Access slow to open and run queries

[BobSullivan]

I have 3 Access users, using Access 2010 on Windows 7 machines. I'll call them Al, Bob, and Chris.

Al's queries are taking longer and longer, and lately Access just "stops responding" and asks if Al wants to restart. This appears only to happen when Bob is in his database. I don’t believe Chris has this problem when both Bob and Chris are in Access, just Al.

Some clarification:
Each user has their own front end database loaded locally on their machine. All the front end databases connect to a back end database, which actually contains links to tables in 4 other databases, and has only one very large table stored in it. When opening the front end databases, they open a menu screen which connects to some of this linked information. I would say that all the links and links to links are causing the problem, except this is only happening on one of the three machines.

I know this is pretty vague, but any thoughts?

[HansV]

Does Al's computer have less memory (RAM) than the others? Does he have more memory-intensive applications running simultaneously? Is there anything else in Al's setup that could cause Access to bog down?

[BobSullivan]

I don't know but thanks for the hints. I will be visiting these folks on Friday, and I will start with these issues. I will report back if I find anything significant for the lounge.

[HansV]

You can also try compacting Al's frontend. A database can become seriously bloated when it's been in use for a long time, and this has a negative impact on performance.

[BobSullivan]

I did that some time back, but I'll look at it again. Last time I compacted the database (mind you it was only a front end database) it went from 78 MB to 7 MB. That is not a typo.

[HansV]

You could set the frontend to Compact on Close. I know that some Access experts warn against it, but I've never experienced problems with it in single-user (individual) frontends.

[Rudi]

Just my ...

Besides the possible hardware/memory issues of Al's computer, (and possible degraded network cabling or network cards), is Al's front-end different to the other two?
IOW: Has Al made some significant changes to his front-end (setting up additional queries, query calculations and reports) that the other two do not have? If the front-ends of these three users should be the same, you could try replacing Al's front-end with either Bob's or Chris's front-end.

Some additional ideas can be found here: http://www.fmsinc.com/MicrosoftAccess/P ... d%20Tables

[BobSullivan]

Found the problem and fixed it.

Even though each user uses the same back end database, the VBA references (Tools, References in the VBA window) appear to be set locally. When Al’s machine was upgraded, the references were correctly set in the front end database. But the references also needed to be set in the back end database VBA window. I opened the back end database from Al’s machine and saw that two were marked as missing. I removed them, and inserted the correct DAO object library. This fixed the speed of the front end database, and eliminated all of the timing issues.

Al's had received a new machine. Evidently, the references need to be reset locally on each machine, even though the all access the same back end database.

[HansV]

Hmmm... - the backend database shouldn't need to run VBA code, so it doesn't need any references apart from the ones that you can't remove (Visual Basic for Applications and Microsoft Access n.0 Object Library). If only those two are set, the missing references problem shouldn't arise.

[BobSullivan]

I agree.

So the actual answer is this:

Two of the three users had older versions of Access, and were upgraded to Office 2010. Because they had the older versions of Access, they had the original set of .dll files referenced in the back end database (which didn't need to be referenced). Al got a new machine with Office 2010 on it, and therefore did not have the old .dll files on his machine, so when a particular query that referenced the particular table in the back end database, he did not have the referenced .dll files and so the query bogged down and usually stopped running completely because the back end database was trying to find the non-existent files on Al's machine. Once I removed the references in the back end database, all three machines run fine because none need to access the .dll file anymore.

Which causes another question that I am going to investigate: When you split a database with references in it, do the references go into both databases? Is that the reason that the references were in the back end database? And the quick answer to that question is no, at least in 2013.