Solved

Shared Access Database Frequently "Running Query"

Posted on 2011-03-08
6
443 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format Date fields 11 63
SQL Server for XML PATH giving wrong results. 6 59
Database (Access Table) Security Access 8 57
replacate wordpress websites 3 22
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 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