1

I am looking into building an access database that will replace a very frightening combination of excel spreadsheets that are linked together. The files for the current spreadsheet system are located on our Windows Storage Server, which has a mapped drive to the directory they use. Multiple users access this system as it host inventory, ingredients used in each product and pricing for everything. I believe using a database is a MUCH better Idea. As I am visioning it, If i split the database it would give me a front and back end. allowing multiple users to access and make changes at the same time, With out Corruption! woo-hoo!!!

Is this correct? and has anyone implemented this? did the end user find using the database easy enough? any problems with this that i did not for-see. I have read that it is also possible to have the front end locally on the users computers. True/good?

Feel free to share any experiences!

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
Grishanko
  • 131
  • 2

2 Answers2

1

Access works very well for small offices, if it is set up properly. The database must be split, with each user having a copy of the front-end. This does not mean that each user needs a full copy of Access, the runtime version is sufficient. 2007 runtime is free (http://www.microsoft.com/downloads/details.aspx?familyid=d9ae78d9-9dc6-4b38-9fa6-2c745a175aed&displaylang=en)

You should read this thread that deals with many misconceptions: Is MS Access (JET) suitable for multiuser access?

Community
  • 1
  • 1
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
0

This should go over to StackOverflow.com, and I've voted to move it over.

That said, Access and Multiple Users = disaster waiting to happen as the number of multiple users goes up. You should seriously consider switching over to SQL Server Express for the backend. You can use Access forms / reports for the front end, linking the tables to the SQL Express Server.

I have done this type of split, but I only do it where there are VERY few people that would hit it at the same time. There's a copy of the database that houses all the UI things (forms, reports, etc) and the data lives in a different copy, all the tables are linked together. It works fairly well, but again I would NOT TRUST ACCESS with more than a few people hitting it at the same time.

I'll leave it to the rest of the SO community (assuming it gets migrated) to go from there.

squillman
  • 13,363
  • 3
  • 41
  • 60
  • @squillman: the fact that you are unable to make a reliable Access/Jet app does not mean that it can't be done. It is a poor workman who blames his tools. – David-W-Fenton Jul 26 '09 at 21:35
  • Call it what you will. I've had these things go south so many times, split, not split, backend to a web app... – squillman Jul 27 '09 at 14:35
  • Microsoft doesn't recommend the Access backend database for projects of any complexity. I have built stable products that operated well outside their recommended limits, but it wasn't pretty. Their upgrade path is SQL server, but in reality any real RDBMS that you can address via ODBC will do. Once the database is split, it is straightforward to migrate the data and link the front end to the tables in the ODBC datasource. I'm upvoting this answer. – pojo-guy Mar 02 '15 at 14:28