Sharing a Microsoft Access Database

Microsoft Access does a pretty good job of handling multi-user access as long as you don't push it too far. You can put a Microsoft Access Database on a shared folder and successfully access it from multiple PCs simultaneously. We have many clients doing just that with our Amphis Customer CRM Software with no problems at all.

We'd recommend running a Compact and Repair on a regular basis, especially on a shared Microsoft Access database, to free up resources and tidy up the internal links. We have a free app for doing that available from our Products Page.

We'd also suggest using a connection string that forces row locks instead of page locks to reduce the amount of data locked at a time for a shared Microsoft Access database. This can reduce performance very slightly but increase scalability so more users can use the database at the same time. Also, specify that the database is to be shared. Here's an example :-

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\server1\customer.mdb;Persist Security Info=False;Mode=Share Deny None;Jet OLEDB:Database Locking Mode=1;

We have had clients use 30 client connections for years to a shared Microsoft Access Database before they decided to upgrade to the Free version of Microsoft SQL Server instead.

Also check the following :-

• Use a UNC path rather than a mapped drive for the database location.
• Check that the shared folder where the database is located has unlimited connections allowed rather than restricting the number of connections.
• Check that all users have full access to the shared folder. Test on each client PC that you can create, save and then edit a text file in that folder using notepad.
• If you have problems from a particular client PC, try connecting to the database from that client PC with no other users connected and see if the problems go away.
• Check that the connection strings on all client PCs are the same. The database uses the connection string that the first client connects with to set the Access Permissions.
• Run a Compact and Repair on a regular basis. See Downloads page.
• Perform regular backups, at least daily at the very minimum.

If the performance or scalability of the shared Microsoft Access Database becomes a problem, it can easily be upgraded to the Free version of Microsoft SQL Server called Microsoft SQL Server Express. Our Amphis Customer CRM software supports both Microsoft Access and Microsoft SQL Server including the Free Microsoft SQL Server Express.

One key thing to never forget, whatever database you are using, is to perform very regular backups or else a virus or a failed disk could lose all your data forever. We'd recommend backing up your database and other files at least once per day at the very minimum and preferably every hour. One day a virus or hardware failure could trash all your files. How much data are you prepared to lose ?