Shared Access Database Frequently "Running Query"
Posted on 2011-03-08
I have an access database. The back end is on a shared mapped network drive, and the front end is on each client's computer. About 10 clients concurrently connect to the back end. The backend .mdb is about 100 MB.
The database is being updated continuously throughout the day. For some reason, when using the database, every minute or two the front end will stop responding and say "Running Query" in the status bar. This takes about 30 seconds for it to run the query (I'm not exactly sure what query it's running), and then allows you to continue.
My logic is this: Because multiple users are updating the database constantly, if user #2 makes a change to the database, the front end for user #1 must download the entire backend database (all 100 mbs) over the network, to make sure it has the most up-to-date version of the database. If various users are making changes every minute, this would explain the frequency issue. The long query time is probably just the time it takes to pull the 100 mb backend over the network.
My questions are:
1. Is my logic correct? Is this really how Access works? Does making a single update to the backend require the entire backend to be transferred to the other clients?
2. How can I resolve this? (I am looking at upsizing to a SQL Server backend in a few months, but I am looking for a more immediate solution).