Go Premium for a chance to win a PS4. Enter to Win


Access Performance and SQL server

Posted on 2014-01-25
Medium Priority
Last Modified: 2014-01-28
I developed an app in Access 2003 for a client several years ago.

Over the years the company has grown and the number of concurrent users increased to above 20 and sometimes more.

The backend database is an MDB.  The size of the MDB is only about 250MB so nowhere near the Access limit.  I have many clients with backend MDB's larger that this.

System response is becoming an issue, especially with certain forms.  The client has very state of the art hardware and networking.

 The client is asking me to make it better from the application side.  My first thought is to move to a SQL Server.  I have heard and read many times that the MDB is not designed to support concurrent usage by this many users.  I have also read that  SQL Server is a much better solution as the number of users with concurrent access grow.

I'm sure the client is open to this solution but before proposing it I want to make sure my recommendation is valid.

The last thing I want, is to propose the solution, have them go thru the time and expense to implement it and end up with no improvement.

So I'm just looking for experiences of any EEer's that have gone thru a similar transition and hearing what kind of performance improvements were realized, or not.

We also plan to transition the application to Access 2010 at some point.  However, I don't see that providing any performance benefit.  Am I correct about that.
Question by:mlcktmguy
  • 3
  • 2
  • 2
  • +4
LVL 28

Accepted Solution

MacroShadow earned 400 total points
ID: 39809729
The maximum number of concurrent users for Microsoft Access is 255. The
only limit I've heard of so far about "no more than ten users" is not an
Access limit, but a Windows XP networking limit. If a multiuser Access
database application is placed on a Windows XP peer-to-peer network, then no
more than 10 users will be able to open the file at the same time. Of
course that limit applies to any file on that network, not just the Access
database. The solution to this, of course, is to use a client/server
network with a dedicated server.

If a multiuser database is designed correctly with a split front end and
back end, all users are given Windows "Full control" security permissions on
the network shared directory, and all users are only reading the records,
instead of inserting, updating and deleting records, then the limit of 255
concurrent users _could_ be reached. However, real life doesn't give us
such an ideal scenario.

The number of concurrent users will be limited to the amount of network
traffic and the amount of record lock contention. The record lock
contention can be controlled to some degree by the efficiency of the
database design and database application design. This is where experienced
Access database developers are worth their weight in gold, because they can
often create a fairly robust database system with Access, instead of
creating a much more expensive database system with SQL Server, Oracle, or
another RDBMS.

So, the limit on users depends upon the needs of the database application,
the amount of record lock contention, the network and how it handles
traffic, and the expertise of the database developer. This limit could be
from one user to 30 or more concurrent users in a reliable Access database
system, depending upon all of these factors.
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 320 total points
ID: 39809930
In general, you may see an improvement in your applications speed, by simply moving the backend to SQL Server.  But that is very dependent on how your application accesses data.  See this MSDN description regarding Optimizing Access - SQL Server applications

You can make the move to SQL Server without too much pain if your tables all have primary keys.  If not, the tables will not be updateable once moved to SQL Server.  Every table that you want to be able to update will require a primary key and I would also recommend a TimeStamp field (used by SQL Server for version-stamping table rows, avoid write conflicts).  You will also need to add the dbSeeChanges option to all of your OpenRecordset code.

What I have found is that the performance tuning I've done after moving my data to SQL Server is what provided the most improvement.

The application will attempt to perform all queries on the SQL Server, and only return the appropriate records based on your query, these queries will be quicker than what you are currently experiencing.  But if the query contains VBA functions, SQL Server will be unable to process those queries and will be forced to push the entire recordset accross your network for Access (JET) to process (same way it works with Access as the BE).

In many instances, the real performance enhancements will come from using SQL Server Views, pass-through queries, and SQL Server stored procedures.

Assisted Solution

by:Lawrence Barnes
Lawrence Barnes earned 320 total points
ID: 39810032
I will usually setup rapid prototypes using access front and back ends.  I have done this for up to 50 users with a lot of data manipulation and succeeded when I used hidden tables/forms to maintain the .ldb locking files when multiple users are opening and closing their front ends (as sunning you have split the database.)

I have switched the back end to point to SQL server when I needed:
1. The data backed up frequently
2. More speed for queries with multiple levels (these queries were rewritten as stored procedures that Access would call.)
3. Storage of more than 2gb of data.
4. Elimination of Access bloat management.
5. Security
6. The data made available to other company resources.

You can suggest a test...  There is a pro version of SQL that will hold up to 4gb which is free.  The developer version only costs $50.00 USD.  Either of these could be installed on your workstation and linked to your database and you could push your data into a database.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 320 total points
ID: 39810278
Ditto what fyed said.

In addition, there is an edition of SQL server that is free to use, and if you use the SQL Server Migration Assistant, it's easy enough to move the data, so I suggest you give it a try.

LVL 49

Expert Comment

by:Dale Fye
ID: 39810309
Actually, instead of this:

"But if the query contains VBA functions"

I should have said:

"But if the query contains VBA or user defined functions "

Author Comment

ID: 39810569
Thanks you.  Based on some of your responses I am very interested in any fundamental changes I can make in the DB to improve performance.  I realize this is a separate issue/questions so I created another question:


Please look at it and provide any suggestions, SOP's, corrections or techniques for making an Access application more efficient.

One thing I asked in the new question was how to ensure a constant DB connection, as was suggested above.

Assisted Solution

by:Armen Stein - Microsoft Access MVP since 2006
Armen Stein - Microsoft Access MVP since 2006 earned 320 total points
ID: 39810674
I've written a PowerPoint presentation on techniques for using Access as a client-server front-end to SQL Server databases.  It's called "Best of Both Worlds" at our free J Street Downloads Page:


It includes some thoughts on when to use SQL Server, performance and security considerations, concurrency approaches, and techniques to help everything run smoothly.

Armen Stein
LVL 49

Expert Comment

by:Dale Fye
ID: 39810754

Didn't realize you hang out here on EE.

Enjoyed the piece you posted on Linked In.  Hope you are making good use of that keyboard!

LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 320 total points
ID: 39810943
There is a pro version of SQL that will hold up to 4gb which is free.
It is 10GB.

Expert Comment

by:Lawrence Barnes
ID: 39810972
Thanks for the clarification ACPerkins.

Author Closing Comment

ID: 39814927
Thanks for all the great advice and information.  I split the points among responders

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question