Nov 09 2021 03:50 AM
I am using Access 2016 and we have multiple users, each with their own front end. Microsoft Access takes a crazy amount of time to load any form or report or query unless its within the first ten seconds of opening the program. I have tried compacting and repairing and really have no idea what to do to fix this. Any suggestions?
Nov 09 2021 06:41 AM
@Lateralusx777 There are multiple possible reasons and it's hard to diagnose them from afar.
I recently had a similar problem on my two-computer peer-to-peer network. After days of trying different things, I finally discovered the problem was my backup software which was running continuously and consuming all available system resources most of the time. Not that your problem is likely to be that, but by way of explaining there can be different reasons in different environments.
I would also investigate anti-virus software running on your computers. That can be a source of the problem in some situations.
Do you have persistent connections set up in all of your users' front ends? That is a potential problem when it's not done.
Where is the back end located? What is it, an ACE back end? SQL Server on premises? Hosted SQL Server or SQL Azure?
Nov 09 2021 07:33 AM
@George Hepworth What do you mean by persistent connections? It is Access back end on a file server on premises. I am currently in the process of converting it into SQL but that is going to take me a while so I was looking for a quicker fix. We have probably 10 users connected at all times.
Nov 09 2021 08:27 AM
Here's a link to a good overview discussion of performance, including the use of persistent connections.
Nov 09 2021 08:48 AM
Nov 09 2021 09:00 AM
@Lateralusx777 The existence of the locking file isn't the same thing.
It only records who has opened the accdb/mdb and when they close it. When a user attempts to use a linked table, as in opening a form, Access has to reestablish a connection to the data. If one is currently open and available, that isn't necessary. Hence, the importance of a persistent connection.
Nov 09 2021 09:08 AM
Nov 09 2021 09:16 AM
Nov 09 2021 11:03 AM
Nov 09 2021 02:00 PM
@Lateralusx777 As I said in my first post, it's really hard to diagnose performance problems when the most common factors have been tested and found not to be relevant. It could be bad hardware in your network somewhere, a switch or router that is bad or going bad. I've seen situations like running a CAT 5 cable over the top of a fluorescent light and having the cable compromise by proximity to the ballast in that light. Failing NICs in one or more computers can be a factor. The fact of the matter is that if you've checked and validated the kinds of things Access can be subject to, then the most likely source of a problem is in the environment. Access has to have a clean, reliable network connection because it passes so much data back and forth between front end accdbs and the back end accdb. Other applications are much less susceptible.
Get your IT support involved and don't let them dismiss the problem without thorough follow up.
Nov 09 2021 02:33 PM
Let's go back to this first statement, and maybe expand on it a bit.
"Microsoft Access takes a crazy amount of time to load any form or report or query unless its within the first ten seconds of opening the program. "
So, if a user starts an existing relational database application and immediately opens any form or any report, there is no delay in opening it. However, if they then close that form or report and attempt to use a different form or report, things slow down dramatically? Does that pattern repeat itself consistently? Does it happen regardless of which form is first opened? Does it happen if the user closes and reopens the same form?
Does this happen with any accdb? Or with one in particular?
Do you have an audit function that is copying records to an audit log, or some similar updating process running in the background?
How about timer(s) in an open form?
Can you replicate this problem on every computer on your network?
Nov 10 2021 03:02 AM
Nov 10 2021 06:39 AM
That sounds so much like either code running on a timer event or an audit log making a back up that I'm stuck trying to think otherwise. Also, in my personal environment, I had back up software running that would kick in from time to time with similar patterns.
The only ways I can think of to pursue it further is to provide a copy of the accdb to examine or for you to create a separate accdb with only a form and a couple of tables to test with no code in it.
Nov 10 2021 06:43 AM
Nov 10 2021 07:03 AM
Nov 10 2021 07:15 AM
Nov 10 2021 07:29 AM
Nov 10 2021 07:37 AM
Nov 10 2021 12:03 PM
Nov 10 2021 12:33 PM