Shared Access Database Frequently "Running Query"

Posted on 2011-03-08
Medium Priority
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:Chris

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?

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 77

Accepted Solution

peter57r earned 1800 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 200 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Microsoft Jet database engine errors can crop up out of nowhere to disrupt the working of the Exchange server. Decoding why a particular error occurs goes a long way in determining the right solution for it.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

621 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