Why is Access so slow?

[Lateralusx777]

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?

[#2]

@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? 

[#3]

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

[#4]

@Lateralusx777 

Here's a link to a good overview discussion of performance, including the use of persistent connections. 

[#5]

I will look into that. Our files always have the lock when open though so idk if this is already happening.

[#6]

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

[#7]

Ok I will try to set that up when I have some time to. Thank you for the input and assistance. It is very much appreciated.

[#8]

Good idea. It's probably a good idea to run a test anyway before deploying a new version.

[#9]

That didn't yield any results for me. I cant really find anything about this anywhere either.

[#10]

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

[#11]

@Lateralusx777 

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? 

[#12]

Yes if we immediately open a form or report there is no delay, however, after roughly ten seconds or so opening the same one again or a different one would yield a significant wait time.
This is with any accdb.

I do not have an audit function running.

No timers.

It is replicated on all computers.

[#13]

@Lateralusx777 

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.

[#14]

I missed the statement that this happens with any accdb in your environment. That pretty much puts it outside of Access and points to environmental problems. I.e. Anti-virus or backup software or other network processes or perhaps hardware problems like bad switches, routers or cables.

Getting your IT involved seems like the only way to track down the source of the problem.

[#15]

Even though it always works fine for the first ten seconds or so? We only have one accdb for the backend that contains all of our linked tables and then a different accdb for each person's front end.

I have a few reports converted to run off of my linked sql tables on a cloned front end. These load instantly always.

[#16]

Also sometimes it works for the same duration to compact and repair my front end.

[#17]

The fact that it starts out fine and then slows down implies that something is happening in the environment. You've ruled out anything in the accdbs themselves, such as the lack of a persistent connection, backups and so on I can only suggest things based on the information available here in this discussion.

Do the individual accdbs reside on the computer of each user, or do they also reside in the network folder with the back end accdb? Move them to users' computers if they are being run from a shared location.
I can't imagine that you are on a WIFI network, but let's verify that as well.

[#18]

I have tested the accdb on server and on pc with no difference. We are using ethernet connections all around. I can ping the server all day with no degradation or lost packets.

[#19]

I actually just made a huge breakthrough and converted everything successfully into SQL and now everything loads instantly. I appreciate your assistance.

[#20]

I'm glad you solved the problem. However, it's disappointing not to be able to address the Access side. Out of curiosity, where is the SQL Server instance located, internally or hosted?