Access performance issue with multiple users

Hi atrkmnn,

Thank you for reaching out about the performance issues.

Have you utilized the Performance Analyzer to optimize your database's performance?

Here's a helpful Microsoft Office 365 blog with tips to make your Access database faster:

5 Tips to Make Your Access Database Super-Fast

FYI, these forums do not provide direct support to end users. They are staffed, for the most part, by non-Microsoft employees who happen to be interested in helping fellow Access users and developers by sharing their experience when they can. If you need direct support, you might want to contact Microsoft via their support page.

There are a few possible reasons for this problem, and it sounds like you have already implemented the most important with regard to shared access, i.e. the persistent connection method.

Please tell us which version of Access you have installed. Please tell us whether you have a wired or wireless network. Also, please confirm that no users are connecting remotely via a VPN or Remote Desktop.

Personally, I'd start with your network and ensure that it is performing well.

Do you apply link table ?

Ok, so far it looks like you have the correct setup.

The "big" issue would of course be VPN users, since a VPN tends to be 10x or even more times slower.

The reason?

Well, because the VPN is working over the internet, then a 10 times slower connection means your performance will be 10x slower (kind of simple).

However, if user's on the LAN (that is the local company network, no VPN's), then often it can be the company network virus software - check with IT that the accDE and the Access .exe are excluded from the virus software.

Also, make sure that the network share is not some kind of cloud storage, but is a valid working shared folder on the network. And make sure that ALL users have create + delete rights on that folder. (reason being is that when the first user opens the file, then Access creates a so called "locking" file which allows multi-user operations. So, often the time to create this locking file is "huge", and hence the suggesting to ensure a persistent connection is setup (this eliminates the repeated deleting and creating of the locking file - which of course can slow things down to a crawl, since Access is then waiting for the "OS" to create that file. So, do double/triple check that your persistent connection is setup.

As noted, performance over a VPN in near all cases is far too slow, and will not work well at all.

Remember, while your company server might have a good network, any remote VPN user is now limited to their home internet connection, and worse yet most home connections have VERY limited up-load speeds, thus resulting in even worse performance.

In general you can't run Access over a VPN - it's simply too slow and too unreliable. In such cases, I recommend remote desktop for such users. You can also of course consider using SQL server as the back end, but this can take significant efforts are your part. In other words, SQL server "can" work over a VPN well, but ONLY if you make additional investments to the existing Access application.

Even without SQL server as the back end, doing things like launching a form bound to a WHOLE table is going to run slow, and all such forms can remain bound to the linked table, but a "where" clause to limit the records pulled into that form. In other words NEVER JUST open a form to a bound table, but open the form with a where clause. Even with a Access back end, it is smart, and will ONLY pull the one record down the network pipe. So, optimizing and avoiding simple things like opening forms without a "where" clause to limit records is a bad design choice.

The above is a summary and outline of the issues you face, and really is a re-hash of the points I make in this article of mine, and the performance issues you encounter with a VPN and Access:

http://www.kallal.ca/Wan/Wans.html