Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is MS Access (JET) suitable for multiuser access?

I have a product designed to be a desktop product using MS Access file as a DB.

Now, some users need to install it in a few PCs (let's say 2 or 3) and SHARE the database.

I thought to place the MS Access file in a shared folder and access it from the PC, but... the JET Engine is designed for multiple user access?

Any tips or things to be aware of doing this?

EDIT: The app is a .net one, using the database as storage (not using the database as frontend)

like image 811
Romias Avatar asked Apr 18 '09 18:04

Romias


People also ask

Is MS Access Multi User?

Access, by default, is a multi-user platform. So this functionality is built in. However, to insure data integrity and not cause corruption, a multi-user database should be split between the back end (the tables) and a front end (everything else).

How many users can Access MS Access database simultaneously?

Re: Number of simultaneous users allowed to access database Theoretically, Access can have 255 simultaneous users. The reality is quite different and depends on a multitude of factor, network, server, ...

How many users can MS Access handle?

MS Access is not suitable for team use It experiences severe performance issues when multiple people try to access it through the network simultaneously. Though the technical limit is 255 concurrent users, it becomes terribly slow even with 5-10 users.


3 Answers

There is so much misinformation in the answers in this thread that I don't know where to start. I just spent 4 points in reputation voting down the answers with misleading and wrong information in them.

  1. the Jet database engine (which is all that's involved here, as the OP clarified with an edit) is by default multi-user -- it was built from the ground up to be that way.

  2. sharing a Jet data store is very reliable when the network is not substandard. This means not a WAN and not wireless, because the bandwidth has to be sufficient for Jet to maintain the LDB file (for multi-user locking), which means a ping by your local PC's instance of the Jet database engine once per second (with default settings), and because Jet can't recover from a dropped connection (which is quite common in a wireless environment).

  3. the situation where Access falls down is when a front-end Access application MDB is shared (which is not the case for this poster). The reason it fails is because you're sharing things that can't be reliably shared and have no reason to be shared. Because of the way Access objects are stored in an MDB file (the entire Access project is stored in a single BLOB field in one record in one of the system tables), it's very prone to corruption if multiple users open it. In my estimation, sharing an Access front end (or an unsplit MDB with the tables and forms/reports/etc. all in one MDB) is the source for 99.99% of corruptions of Access/Jet files.

My basic answer to the OP's question is that, yes, Jet would be a great data store for an app of that size. However, if there's any possibility at all for the user population to grow above 25, then it might be better to start off from scratch with a database engine that is more robust at higher user populations.

like image 174
David-W-Fenton Avatar answered Oct 02 '22 15:10

David-W-Fenton


It's perfectly feasible to do this; but you MUST split the database into a front end (with forms, queries, code) and a back end (data only). Every user has to have the front end on their own computer, linking to the shared back end.

It will be slow as Jet generates a ton of network traffic. Microsoft is also gradually deprecating Access as a development tool. Access 2007, for instance, has a far less sophisticated security model than Access 2003.

As a long time Access developer I am gradually moving away from Access.

like image 27
dsteele Avatar answered Oct 02 '22 14:10

dsteele


Don't do it... the Jet database claims to be able to support multiple users, but it is incredibly easy to use the upsizing wizard to convert your Access file to a Sql Express database. That database file could EASILY become locked by a user or admin, and all of your users would be unable to use the database.

... and Sql Express is free. Your upgrade path from there to a full instance of Sql Server or some other commercial database is simple.

like image 21
Jeff Fritz Avatar answered Oct 02 '22 16:10

Jeff Fritz