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.