MS Access DB on network with multiple users.

Question

Hello All,

I have an MS Access database on a network drive with a userform which has multiple users (All using at the same time)

The form runs an update-query which will add data into a raw data table.

Does anybody know how I can make this work if two(or more) users run the update query at the same time? I assume this has to be done using temporary tables...

Thanks, James

Answer 1

Access does not behave well if the entire database (front end forms plus backend database) is accessed by several users from a network share.

Try splitting into a front end (one on each client machine) and a single backend (tables) on the network share, and use linked tables in the front-end.

How to manually split a Access database in Microsoft Access

Information about query performance in an Access database

Microsoft Access Performance Tips to Speed up Your Access Databases

Tony Toews' Microsoft Access Performance FAQ is well worth reading.

Answer 2

Just to add some pages from my website to this discussion.

You want to split the MDB into a Front End MDB containing the queries, forms, reports, macros and modules with just the tables and relationships in the Back End MDB. The FE is copied to each network users computer. The FE MDB is linked to the tables in the back end MDB which resides on a server. You make updates to the FE MDB and distribute them to the users, likely as an MDE.

See the "Splitting your app into a front end and back end Tips" page for more info. See the free Auto FE Updater utility to make the distribution of new FEs relatively painless.. The utility also supports Terminal Server/Citrix quite nicely. Also visit my Access Performance FAQ page.