Slow Access Database with multiple users

Question

Hello fellow Access developers. I posted about this issue six years ago. Here is the original post:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/5c5aecbc-aa7b-4523-b80a-dc8cd029b753/slow-database-issue-please-help?forum=accessdev

I am now at another company, developing a different database in Access 365. Backend is less than 5MB and will have 10 users (about 5 simultaneously). But this same issue still haunts me. Six years ago, I found the culprit was DLookups. I got rid of those (replacing with Elookups) and it solved the problem. The database I have now only uses ELookups.

All users, including myself, work remotely, so we use VPN. Database is lightning fast on my machine (3 seconds to open main form from main menu). Database is still pretty fast over network with just one user (5 seconds). But as soon as another user opens the database even if they just sit idle on the main menu (which has the permanent connection table as the record source), it jumps to 15-20 seconds, or sometimes longer. Some users seem much slower than others.

I have done all the appropriate performance improvements in the design, as described in the post from six years ago, plus some other things like using local temporary tables when feasible and removing Conditional Formatting. The temp tables and conditional formatting only sped it up by a couple seconds.

The lock file (.laccdb) behaves as expected, adding the appropriate usernames when users open the database, and deleting the lock file once everyone is out. All permissions to the network folder are correct.

Tables are mapped directly to the server name, NOT the drive letter. I tried mapping to the IP address of the server. That actually seemed to make it even slower.

I did a trace route on the server (to the IP address). 22ms on average. That is pretty fast, correct?

So I’m not sure what to do at this point to improve latency. Note: I’m a contractor for a government agency, so it’s on a large network. Don’t know if that is part of the problem, along with VPN use.

In the post from six years ago, and other research on this topic, people mention Oplocks (Opportunistic Lock). Some recommend disabling that feature on the share where the back-end resides. But others say that may cause database corruption. Here is one post about it:
https://www.askwoody.com/forums/topic/access-office-365-backend-in-an-inconsistent-state-issue/

Any thoughts on this? What are the pros and cons of enabling vs. disabling this feature? It might be hard to get server maintenance people to change this setting, but I can try if it will make a difference.

My only other thoughts are: Migrating the backend to either SQL Server or Dataverse.
I’m not sure I want to deal with SQL Server, and from what I understand, that may not necessarily fix the latency issue. I would have to go to another group for that, so not sure how much control I would have.

I have read and seen videos on Dataverse. Seems easy to setup and maintain. The customer has expressed interest in sharing Access data on SharePoint, so that might be a good fit (if we can get approval for the license). But would Dataverse solve the latency issue?? Anyone have experience with using Dataverse as a backend?

Any thoughts on migrating the backend to solve the latency issue?

Any other suggestions in general??

Answer 1

All users, including myself, work remotely, so we use VPN.

So, a VPN - that going to be a good 10 or more times slower then your local office network. So, a 5 second delay will now become 50 seconds. And of course, if a person is at home, then you have no clue what kind of connection they have, or how poor their internet system is. So, keep in mind that VPN's are slow - often 10x or MORE slower then your local office network.

I got rid of those (replacing with Elookups)

Hum, Elookup? What on earth is that? never heard of that term in regards to ms-access. A dlookup() is no slower then writing VBA code to fill a record set to pull a value from a table. It not dlookup() that is slow, but then again, if you drop in a dlookup() to a query, then you executingn 100's and 100's of dlookups (one for each row). The dlookup() is thus not slow, it is using it 100's of times that is slow!!!

In the VAST majority of cases, a dlookup can be replaced with a simple left join in the query - and they work fast. But, "elookup" is a new term, and somthing i not heard of.

>ut as soon as another user opens the database even if they just sit idle on the main menu (which has the permanent connection table as the record source), it jumps to 15-20 seconds, or sometimes longer. Some users seem much slower than others.

Ok, some things to check:

Each user has a local copy of the front end on their computer - NO EXCEPTIONS to this rule. So, they are consuming and using linked tables to the back end shared folder on the company network - over that super slow VPN which as I noted is AT LEAST 10x slower.

A few things to check:
You ensured that the applcation part (front end) is on each work station. (no exceptions, right ???).

Next up:
Make sure the applcation does not have ANY specific printer set for reports - even ones you are NOT using.
Make sure the user has a NON NETWORK default printer set. when you launch a form or report, access will often interrogate and attempt to get printer information - it can be worse then slow . Check this issue.

Check for any stray linked table (that means a linked table that points to no where).

Now, you suggest, state, hint, and seem to be very sure you have a persistent connection. If you main form is bound to a back end table - and that main form REMAINS open at all times, then you look to be ok. However, based on your example, this THEN should not result in a slow down when the 2nd user enters. (assuming the persistent connection).

No wi-fi users allowed.

I should point out, that VPN's are not a reliable setup for a split database. You will see increased chances of corruption.

The most easy solution would be to migrate the back end to SQL server. The front end you have can 99% remain as is.

SQL server express is free, and is ideal for this. The great part about this, is that you get to keep + use the front end, and 99% of the code and everything should work as before. Very few code changes are required for this setup to work.

However, the HUGE bonus then?
You can then optimize some parts of the applcation. (you use a mash like hospital approach). You might find say 2 or 5 forms or reports that run slow. So, you take the access query, move it to sql server (a view). Link to the view (give it same name as your query). At that point, that query speed and performance will be blistering - and all without ANY code or even form or report design changes required.

If a VPN was not involved, then just 10 users is not a problem. However, even if you do gain and tweak better performance? the setup is NOT reliable with VPN. A VPN means a internet connection, and that means small breaks in the connection can occur, and that means corruption and damage.

I am not sure if you seen say star trek. They have a thing called a transporter. If you break the transport, then you get a mess on the other side.

So, Access (unlike excel or Word) does NOT load or pull the whole file data from disk into memory. But, for reasons of performance pulls ONLY parts from disk into memory that it needs.

You have this:

So, say you request record. Unlike excel, or word? You can have 1 million rows, yet the grab of the row is near instant. You get this:

Note how JUST little bits and parts get pulled from disk into memory.

BUT WHAT happens if you put a network between you and the disk drive?

Well, now we have this:

Remember, the server does nothing here - all work is simple a file read process.

But, now with that network connection above?

What happens if the network connection breaks?? (which VPN's do all the time).

You now have this:

The problem is unlike a local database, the ram is YOUR computer with the bits and parts, and the FILE is on the server!!!! If your connection breaks, those bits and parts now don't make it back over the network to the file on disk - and now there is holes and missing parts. The result is a damaged file.

While access can tolerate a network, as you can see, it has to be rock solid. But, a VPN is not rock solid, and not only are they VERY VERY slow compared the office LAN (local area network), they as noted are not near as reliable.

Now, I will say I VERY much find it strange that first user in is ok speed wise, and after that it is slow - a persistent connection should solve that issue.

However, at the end of the day? Due to the VPN, you in a high risk area - even if you do fix the speed issue. I would suggest moving back end data to free sql server, and then continue to use the existing access front end. That setup cannot and will not and does not suffer data file corruptions.

And using free sql server will also mean your performance issues can be fixed - if not out right eliminated.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Answer 2

Access was never designed for concurrent usage so it has minimal support. The fact that you were able to get it working years ago is impressive but also probably highly dependent upon what is going on. For example I suspect that if all 10 users were navigating the same table at the same time then performance would not be great because that is likely triggering a lot of locking issues. Access was just not designed for this.

Yes disabling opportunistic locks would likely speed things up as it would in any real database. However that defeats the purpose of the database in most cases. The lock is what helps keep all the clients in sync and prevents one user from stomping over the changes of another. If you disable this then you might as well have each user copy the DB locally, make their changes and copy it back. That would be the equivalent of what happens without the oplock IMO. However I've never tried that with Access and I'm not sure what the impact would be.

For 10 users I think switching a regular SQL Server database would be the better option. The cost is an issue for most people but ultimately it is designed for this kind of thing. You'd need a server to host the DB though. GIven just 10 users it could be an existing server such as your file server or even a personal machine if you could ensure the personal machine was never rebooted unexpectedly.

Migrating from Access to SQL (or any DB) is easy for the data. In fact you can likely export/import the data. However now would be a good time to expand from the limited relational DB types/features of Access to a more formal relational design. The import process could handle the conversion.

The harder part is any forms/reports you may have. SQL is just the data so none of that would transfer. You could still use Access for reporting or you could migrate to SSRS but that requires additional resources. Any forms you may have could also technically bounce off SQL Server but ultimately you'd want to replace that UI with a Windows or web based UI. Again this requires resources.

A hybrid approach could also be used. Create a web app that contains the UI you need. Have the web app (running on a web server) talk to the Access database. Then all your users would use the web app to access the database. This eliminates the file sharing issues but you still have concurrency problems. However Access is better suited for multiple requests to the same file from the same server then it would for multiple users to be accessing the same file at the same time. This requires you have web experience but would allow you to preserve your existing database structure. At a later point you could migrate to SQL Server without the user's even noticing.

Answer 3

Thank you cooldadtx and Albert for the detailed answers.

Albert:
The ELookup function was developed by Allen Browne. It's more efficient than DLookup.

Yes, all users have local copies of the frontend on their desktops (.accde file). Backend (tables only) on the network server. No stray linked tables. Yes, the persistent connection is how you describe it. The main form, based on the persistent connection table, remains open behind other forms the entire time. I know it works because when I remove the persistent connection table as the record source, and test it again over the network, it gets very very very slooooow. So, that persistent connection definitely makes a difference, just not enough.

I did not know about the default printer setting. Never thought of that. I will have users check their printer settings. And good point about the wi-fi. I am directly connected via Ethernet cable and they should be too. Another item I will have them check.

Albert and cooldadtx:
I will definitely consider migrating the backend to SQL Server or SQL Server Express.

One last question. Neither of you mentioned Dataverse. Do you know much about it? I know it's fairly new but wondering if that is another viable option for the backend. I'm not sure how Dataverse would perform compared to Access or SQL Server backend. It's cloud-based, but from what I read, it's actually built on SQL Azure. More info in these two links:

https://techcommunity.microsoft.com/t5/access-blog/the-access-connector-for-dataverse-and-power-platform-is/ba-p/3354250
https://support.microsoft.com/en-us/office/get-started-migrate-access-data-to-dataverse-013c8bab-7737-46ca-ad2e-892bbf26287d

In the comments at the bottom of the blog site, one person claims their Access queries seem to run slower against Dataverse tables, so ... hmmm. Don't know if that's just them or the norm.