Very Slow Multi-User Access

[iamchemist]

I am running a fairly elaboraete (many large tables; much VBA Code) Access Database Application at at Non-Profit Food Pantry. It runs under Access 2007 and is split into front end and back end databases.
I find that the database is quite fast, when only one user is logged on. As soon as a second user logs on, the speed drops by about half, with some forms taking up to 8 seconds to come up. During most of this 8 second wait time there is a message in the top left of the Access screen that says "Access Not Responding". I take this to mean that the other user is using a needed table on the back end database, and Access has temporarily locked out the User who is waiting.

Is there any way to improve this situation? Should another applicable Database (other than Access) be considered?

Thank you,

Ron Cochran

 

[LarryE]

Make sure each user has their own copy of the front-end file on their own computer linked to the back-end file. Make sure no one shares a common front-end file.

 

[CJ_London]

Performance issues are usually down to the developer or the underlying network

As mentioned in post #2, each user should have their own copy of the front end locally, not on the network

You should also maintain a persistent connection so access does not have to repeatedly go through the steps to create one.

You may also want to review this thread about performance issues

addressing performance issues

As your db expands and there are more active users, it is quite possible you will experience performance issues and may be considering upsizing to SQL Server or MySQL. Before considering such a transition, you should review your current design: Overview From a performance perspective, Access...

www.access-programmers.co.uk

 

[The_Doc_Man]

Also, verify that all queries, forms, and any other recordset-related activities have chosen either Optimistic Locking or No Locks for their locking models. You can use No Locks for anything that is effectively read-only and Optimistic Locks for things that can potentially do updates, either as an Update query or as a Select query through a form that could do updates.

 

[Pat Hartman]

Also, charities don't necessarily have the best hardware. Network slowness can be attributed to one random bad NIC card. If people are connecting via wi-fi, that will always be slower than a hard wired connection but not as slow as you describe. It is also dangerous to connect via wi-fi since networks have intermittent failures and if Access is doing something important, like saving data, during one of these blips, corruption can result. But, hard-wiring is expensive and probably not going to happen. The office might need repeaters if people are too far from the modem.

 

[gemma-the-husky]

This definitely sounds like a persistent connection issue to me, as mentioned by @CJ_London

Did you write the database? Have you added code to maintain a persistent connection? If not, that's the first place to look.

 

[iamchemist]

This definitely sounds like a persistent connection issue to me, as mentioned by @CJ_London

Did you write the database? Have you added code to maintain a persistent connection? If not, that's the first place to look.

Thanks for your reply! I have gone and looked at the suggested code to maintain a Persistent Connection and I will put that code in to see what happens. I am not currently sure where that code goes - i.e., as specific module, front end, back end, etc? I will try to figure that out.

Do you think that even with two fe users being multiplexed (via an Ethernet switch) to connect one at a time to the be database, the suggested code should still maintain a Persistent Connection?

 

[Gasman]

Front end, little point in putting it in the BE?
I used to have a login form in the past, so mine would be in there?

 

[Steve R.]

Many years ago I developed an MS Access database that served multiple users. As you have experienced, with multiple users performance degraded. That was essentially solved by moving the database (back-end) to SQL Server. Each user still had their own MS Access front-end.

Is there any way to improve this situation? Should another applicable Database (other than Access) be considered?

Based on your existing set-up, it would appear that your are committed to using an MS Access front-end. You may ignore my following suggestion unless you are also interested in another configuration (that would require extensive work to implement). A more contemporary approach would be to use your browser, Apache (server), and an opensource database. Essentially mimicking a web-based interface. Under this approach there is no need for each user to have a front-end. But if you are committed to MS Access, stick with it.

 

[gemma-the-husky]

Thanks for your reply! I have gone and looked at the suggested code to maintain a Persistent Connection and I will put that code in to see what happens. I am not currently sure where that code goes - i.e., as specific module, front end, back end, etc? I will try to figure that out.

Do you think that even with two fe users being multiplexed (via an Ethernet switch) to connect one at a time to the be database, the suggested code should still maintain a Persistent Connection?

I don't really understand your last statement. However I always create a persistent connection at the earliest opportunity. If your speed degrades by adding a second user, then on the face of it, the absence of a persistent connection is the cause.

Databases are multi user by design. Even if you don't envisage having a multi user environment it's worth building the database using a standard plan.

So in general always split the data between front end and backend, and always create a persistent connection. I actually use an autoexec macro to startup the database, rather than nominating a startup form. That macro checks that all the expected tables are available and if not reconnects them (eg in case your front end is reconnecting from a development environment to a live environment).

In a multi user database, Windows negotiates the connection to any object in the connected database by a user unless that user already has an open connection. So unless you maintain a live connection every process takes a second or two per object to get permission to use the object, and your performance takes an almighty hit.

My first step therefore in my startup code is to create a persistent connection to the target database. I open a recordset to a table in the backend that then remains open until the database closes. Some of us open and hide a form. Having this open conduit speeds up every subsequent connection, because Windows does not need to check and grant you permission for each new connection

I have a timer on the process to reconnect my files. With a persistent connection, reconnecting a 20 table database might take a few tenths of a second if I am the only user, maybe 1.5 seconds if the DBS is already in use, (ie unavoidable overhead to create the first connection) but without it, it can take nearly 30 seconds, at over 1 second per table.

So my startup code function first creates a persistent connection, then connects all the tables if necessary, and only then opens the startup form, reading the startup forn name from a database table, or hard coding it in the function.

The autoexec macro is a special macro that runs on database startup. I use it to call a public function that I call startup(), and that function manages the entire startup process. I don't think there is another way to do this, but maybe some users have different ways to achieve the same thing.

 

[Cotswold]

All I ever did was have a table in the BE with just one field and one record. That was the datasource to the opening menu, which of course remains open throughout. I never bothered to re-link on opening because Access will fall over immediately if there is a bad network, or fails to link to data. If there was a network problem, then the user could run a utility to check the BE data location and fix it if necessary. But any issues were usually a flaky network, which became less of a problem from Windows XP onward and after the often dopy hardware and network support improved.

I used that system from the first time I read about the permanent connection. Everything was always brisk, regardless of if it was on a local network, or using Terminal Server linking multiple remote sites. Access often gets some stick regarding remote working but even using MDB, or ACCDE, I never had problems with it when using Terminal Server.

 

[The_Doc_Man]

Do you think that even with two fe users being multiplexed (via an Ethernet switch) to connect one at a time to the be database, the suggested code should still maintain a Persistent Connection?

If both users can do so (with respect to network connectivity), it should be possible for both to be in the same database at the same time if you split the database to produce a Back-End a Front-end. (BE and FE) Then put the BE on a machine to act as a file server with the "normal" Windows File and Printer Sharing network setup. Give a COPY of the FE file to each of your users. Diddle with the File >> Options to assure that the Back-End is set up for non-exclusive use.

If your FE file has a persistent form that never goes away until you exit from the App, you can create the persistent connection in that opening form and let it sit silently.

The reason a persistent connection works has to do with Windows File Locking and something called "Lock Arbitration." In order to access a DB file, you must ask Windows to allow you to take out a Shared Write lock on it. If you open and close that connection, then every time you want to open it, you have to not only get permission on the file itself, but also on every folder involved. If you have a persistent connect, you do that file locking arbitration step once and after that, everything is linked up and there is no need to open another connection. Access will "ride" that connection as long as it exists.

 

[iamchemist]

I am running a fairly elaboraete (many large tables; much VBA Code) Access Database Application at at Non-Profit Food Pantry. It runs under Access 2007 and is split into front end and back end databases.
I find that the database is quite fast, when only one user is logged on. As soon as a second user logs on, the speed drops by about half, with some forms taking up to 8 seconds to come up. During most of this 8 second wait time there is a message in the top left of the Access screen that says "Access Not Responding". I take this to mean that the other user is using a needed table on the back end database, and Access has temporarily locked out the User who is waiting.

Is there any way to improve this situation? Should another applicable Database (other than Access) be considered?

Thank you,

Ron Cochran

You guys have been giving me advice that my slow database situation probably results from NOT having a "persistent connection" to Access, because there are two Laptops talking to a Desktop Server via Ethernet Cables THROUGH an Ethernet switch. That switch means that each of my two users is disconnected for Access for a few milliseconds every few minutes, while the other User/Laptop talks to Access.

Adding a second Ethernet Port to our Desktop Server is not a cheap or easy experiment. My Computer Hardware guy is really skeptical that such a short disconnect could have much effect. Are all of you guys fairly certain that the Ethernet Switch is really causing a non-persistent connection problem, even if the disconnect is only a few milliseconds? Has anyone actually dealt with this situation?

 

[GPGeorge]

You guys have been giving me advice that my slow database situation probably results from NOT having a "persistent connection" to Access, because there are two Laptops talking to a Desktop Server via Ethernet Cables THROUGH an Ethernet switch. That switch means that each of my two users is disconnected for Access for a few milliseconds every few minutes, while the other User/Laptop talks to Access.

Adding a second Ethernet Port to our Desktop Server is not a cheap or easy experiment. My Computer Hardware guy is really skeptical that such a short disconnect could have much effect. Are all of you guys fairly certain that the Ethernet Switch is really causing a non-persistent connection problem, even if the disconnect is only a few milliseconds? Has anyone actually dealt with this situation?

The concept of a persistent connection is not related to the hardware.

A persistent connection, in this context, refers to keeping a table in the back end open continually from a form in the front end.

The problem is, as the_Doc_Man outlined, that each time the front end needs to use a table in the back end, it has to negotiate a connection with other front ends which also want to use the back end. By keeping a form which is bound to a small table in the back end open constantly in the front end, that negotiation for a shared connection -- or reconnection -- is avoided, which should speed things up.

Many developers do this by binding a form to a small table and opening that form when the accdb first opens. The form is often hidden. It's only function is to maintain that persistent connection. See also the comments in Post #11.

Again, not a factor of routers, ethernet ports, etc. It has to do the the interactions between the multiple front end databases.

 

[raziel3]

Check this link @iamchemist . It help me with some of the issues you are describing.

https://www.devhut.net/ms-access-persistent-connection-in-a-split-database/

 

[The_Doc_Man]

That switch means that each of my two users is disconnected for Access for a few milliseconds every few minutes, while the other User/Laptop talks to Access.

This statement gave me instant chills. Most ethernet switches don't alternate one output between two inputs. In fact, I've never heard of any Ethernet switch that does that. Please double-check the description of what that switch does because - I'll be a bit blunt here - that is a suicidal network configuration if there is actually a make-and-break connection going on. If that description IS correct, then the rest of my comments might seem a bit dire and a bit harsh.

I'm somewhat surprised the database doesn't crash out from under you every time that switch switches over. My chill comes from the theory of TCP connections, which are session oriented, and the SMB protocol (or SMB2 or SMB3) definitely is a session protocol. Every time you break a connection, unless you have "TCP Session Reconnect" enabled somewhere in one of the registry network settings or in the network \etc\ folder's config files, you risk losing that connection and can never make it again. It's a network security thing and I'm stunned that any IT security person would ever even allow "Reconnect" to be enabled. Among other reasons, it's a hacker highway into your machine.

The only OTHER way that this switch could be effective is to have a long device timeout during which your network session sockets will be pinging away trying to reconnect with their partner nodes before the driver-level connection times out. When that switchover occurs (which I presume only occurs when two people are pinging away at the same time), what is happening is that immediately after the break, the network is undergoing a time-out countdown trying desperately to reconnect before the time-out expires. If I am right, the only thing that saves you is that neither side loses the session because the timeout is long enough. Until the day occurs when it isn't long enough, at which point you will have a super-high risk of database corruption.

Access is designed for consistent network connections. Even a wi-fi momentary break has been known to corrupt Access databases for the reasons described above. What happens more specifically: The session layer of the ISO network model needs continuity because the presentation layer is using a numbering scheme that, if the network session breaks, cannot be re-established. Essentially, the session sequence number gets lost and any partial transaction is now incomplete forever. Which is pretty much the definition of a corrupted database.

My theory of what happens in your observed slowdown no longer has anything to do with persistent connections - because if that switch DOES create make-and-break connections based on multiple users, you are describing a case of non-persistent sessions.

Adding a proper network port isn't as expensive as you think, though. I am not allowed to advertise any product here, but do an internet search for "multiport ethernet adapters" and you will see a 1-to-3 adapter as cheap as $12.00 USA dollars or 1-to-5 adapters starting at $20.00 USA. If your situation is so dire that you can't afford to use one of those devices, your business is already bankrupt. It is the nature of the Ethernet connection that if two users want to send at the same time, they have a method of resolving the connection conflict in a way that doesn't kill the other sessions.

NOTE: IF your quoted description isn't actually correct, then we have to keep looking and I apologize if the above spun you up.

 

[Gasman]

That switch means that each of my two users is disconnected for Access for a few milliseconds every few minutes, while the other User/Laptop talks to Access.

I think that statement is a load of rubbish.
I made a persistent connection to our DB in work and had 20 odd users using it at the same time. All our connections had to go through at least one switch. No issues. In fact I was here asking about our initial slowness at that time (2012) and was advised to make the persistent connection, which I did. No issues after that.

 

[The_Doc_Man]

@Gasman - I've never heard of an Ethernet setup that does that, either, which is why I requested a re-examination. It is one thing if @iamchemist merely misunderstood what was happening; it is totally another situation - and not a good one - if he got the technical description right.

What you are describing is in fact typical of Ethernet networks. You can have 20 users pinging away, apparently simultaneously. As long as you are below about 35% saturation, you will barely notice anything. Above 35%, the probability of multiple sequential collisions becomes significant. On my U.S. Navy network (1 Gbit), our engineers and system admins were on an Access database I built that used a native Access backend, had up to 35 users on at once, and because it had a dedicated file server, so rarely suffered from collisions that, more often than not, the only way I could tell that it HAD glitched was to look at the log files for the error traps for the "record locked" error.

In a way, I really hope that description WAS incorrect, because if not, that site is on a razor's edge of instability. If that description WAS wrong, the reported slowdown would more probably be due to non-persistent connections forcing repeated access (little-a) arbitration.

 

[Gasman]

I have mentioned in the past, that at one place of work, that although the server sat next to my desk, mine and my team members network was a little slow.
Turns out our traffic was being routed from Birmingham to Coventry and then back to Birmingham.
Once that was fixed, it behaved as expected. We are also talking about that being over 25 years ago.

 

[GPGeorge]

@Gasman - I've never heard of an Ethernet setup that does that, either, which is why I requested a re-examination. It is one thing if @iamchemist merely misunderstood what was happening; it is totally another situation - and not a good one - if he got the technical description right.

What you are describing is in fact typical of Ethernet networks. You can have 20 users pinging away, apparently simultaneously. As long as you are below about 35% saturation, you will barely notice anything. Above 35%, the probability of multiple sequential collisions becomes significant. On my U.S. Navy network (1 Gbit), our engineers and system admins were on an Access database I built that used a native Access backend, had up to 35 users on at once, and because it had a dedicated file server, so rarely suffered from collisions that, more often than not, the only way I could tell that it HAD glitched was to look at the log files for the error traps for the "record locked" error.

In a way, I really hope that description WAS incorrect, because if not, that site is on a razor's edge of instability. If that description WAS wrong, the reported slowdown would more probably be due to non-persistent connections forcing repeated access (little-a) arbitration.

I think the description probably was a misinterpretation of the suggested need for "a persistent connection" because it is so unlikely to be the case.

Unfortunately, those Access developers with many years of experience tend to overlook the fact that newbies and less involved Access developers do not have what might be called insider knowledge about such terms. "Persistent connection", like "split database" and many other terms we all take for granted can be, and often are, misunderstood in that situation. I hope the OP merely defaulted to the most likely interpretation of the term from their perspective. I fervently hope that's the case.