Access performance issues when more than one users log in simultaneously.

[atrkmn]

Firstly, I would like to extend my warmest wishes to everbody on this forum for a joyous and restful Christmas season, filled with happiness and good health.

We are currently developing a database system using Microsoft Access. While the system works seamlessly for the most part, we encounter significant performance issues when more than two users log in simultaneously. The database's speed decreases to the point where it becomes nearly unusable. Despite trying various solutions suggested online, we have not been able to resolve the problem.

• Database is splitted. Each user has own FE copy on their local computer, BE is located on a network share folder.
• Forms tables etc. optimized as much as possible. Single user usage is pretty fast (either ethernet or vpn).
• We have implemented Persistent Conncetion (both trying with hidden table and VBA methods).
• Name autocorrect has been turned off.
• Both FE and BE compacted.

We were hoping someone here might be able to provide support or guide us towards a solution.

 

[Minty]

Is the network share subject to anti-virus scans?
What is the connection speed like to the Network?
Is the network share a trusted location?

Oh and welcome to AWF!

 

[GPGeorge]

What version of Access do you have installed and what build?

 

[The_Doc_Man]

The default behavior of queries and forms is Optimistic Locking, so this should only be a "verification" step. Check each query and form to assure that nothing is set for Pessimistic Locking.

Also, in the "Similar Threads" list at the bottom of the page, you might find a few articles of interest.

Your symptom also reminds me of something that happened to me when I had a 40+ user DB. Do your forms use queries rather than direct table references in their .RecordSource properties? Ditto for .RowSource for combo and listbox controls? Because for some reason, queries are faster than direct tables, perhaps because stored queries already have an execution plan.

Then, there is the issue of whether you have a timer running in the FE that potentially touches something in the BE. It is not that you have a timer but what that timer would do to some table that exposes potential slowdowns.

Fast performance in single use but slow performance in shared use also begs the question of what kind of startup form you have (if any). But does this slowdown occur immediately and persist? OR does it start as a mild effect that increases in effect the longer you are logged in? Or some other profile of onset and maintenance?

 

[arnelgp]

We have implemented Persistent Conncetion (both trying with hidden table and VBA methods).

persistent connection is Not opening a Table from Be and keeping it Open.
it is Opening the Database (BE) itself.
see: https://www.fmsinc.com/microsoftaccess/performance/linkeddatabase.html