Shared Access Database Frequently "Running Query"

Posted on 2011-03-08
Last Modified: 2012-05-11
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).

Thank you.
Question by:Orcadian

Expert Comment

ID: 35067511
Try doing a Compact & Repair on the backend

This should reduce the size
LVL 77

Expert Comment

ID: 35067547
If your application is designed so that shows all the data all the time then that is what you will get.  And it won't matter what your backend is.

Access does not transfer the whole database every few seconds.  But it gets the data you have specified at the start of a process (when you open a form, say) and refreshes it automatically normally every 60 seconds.  Refreshes means gets changes to the current data (no new or deleted recods).  Access does transfer indexes to tables to the local user because that's what it needs to find selected records - assuming you have indexed what you are searching for.  If you ask for records which cannot be found using indexes then it will read whole tables to find them.  This is different to sql server, of course, because that work is done on the server and not locally.

If you do anything which causes a requery rather than a refresh then the data is re-fetched. A requery also includes  new and deleted records.

The basic design principle in client server systems is to ask for individual records when you need them.  The closer you can get to that the better your performance will be, irrespective of the backend


Author Comment

ID: 35067878
TinTombStone - I have tried Compact and Repair, and it does not reduce the size below 100 mb.

Peter57r - Thank you for the information.  From your description, it sounds like problem causing poor performance is that the RecordSource of my main form is tied to a table with ~70,000 records.  If I understand you correctly, when I open the form, Access downloads all the data, and then continually refreshes all 70,000 records to check for updates.

On that form is also a combobox with the RowSource set to the same ~70,000 records.  This combobox is used to locate/navigate to the specific record the user is looking for.  The users really like the combobox, but I see the issue inherent in having it tied to so many records.

Which events will cause a requery?  It seems I need to be cautious of events that cause the data to be re-fetched, because it will take another 30 seconds to transfer the 100 mb backend over the network.

Any suggestions for a better design?

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 77

Accepted Solution

peter57r earned 450 total points
ID: 35069039
Well we're at the edge of my knowledge here so I don't want to say more than I am pretty sure of.

A requery would occur if there is any change in the recordsource sql of the form, or if you issue a requery command to get new records/deletions into the recordset or the rowsource of the combo box. Obviously opening forms is going to cause the recordset to be fetched.
In terms of the 'calculating' message, you need to look at all your form controls to see if any of them are based on queries on the database. Apart from combos and listboxes, do you have any Dlookup() or other domain functions getting data.  All of these can mean that a query needs to be run.

But my view would be that you cannot afford to be going forward with an app that requires 70K+ records to be passed to a form.  I can't tell you how to re-design because it is about your requirements.
But as I said before you have to be looking for tactics that let you transfer as few records as possible to the frontend. This could be getting the user to provide as much as possible of a key value and selecting matches for that or maybe finding some way of cataegorising records so that the user selects a category and you just get those.
Set the recordsource of the form to an expression that initially fetches no records  (Select * from table where 1=0)  and only when you know what the user wants, set the recordsource.

If you can make any 'waits' follow positive actions by the user than they will be more understanding of the situation than if the app appears to doing its own thing.

LVL 19

Assisted Solution

by:Eric Sherman
Eric Sherman earned 50 total points
ID: 35075553
Also, check to make sure there are no Timer Events on your Form.

To echo what peter57r posted ...  I have Access apps connected to various back-ends (Access, MySQL and SQL Server) with well over 10 years of history transactions.  Some tables with 300K + records.  For all "Inquiry Forms" using those tables, the SQL Recordsource is restricted to the current year only when the form opens and each form has a "Show Transactions" Year drop down box that allows the user to navigate to a different year when needed.

Works flawlessly and very efficient.


Author Closing Comment

ID: 35130800
Thank you all for the excellent advice. I will implement your suggestions.

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question