Solved

Shared Access Database Frequently "Running Query"

Posted on 2011-03-08
6
430 Views
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.
0
Comment
Question by:Orcadian
6 Comments
 
LVL 6

Expert Comment

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

This should reduce the size
0
 
LVL 77

Expert Comment

by:peter57r
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



0
 

Author Comment

by:Orcadian
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?

Thanks.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 77

Accepted Solution

by:
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.

0
 
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.

ET
0
 

Author Closing Comment

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now