Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Shared Access Database Frequently "Running Query"

Posted on 2011-03-08
6
Medium Priority
?
461 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 77

Accepted Solution

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

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

ET
0
 

Author Closing Comment

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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

604 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