• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 623
  • Last Modified:

Access Performance and SQL server

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.
  • 3
  • 2
  • 2
  • +4
6 Solutions
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.
Dale FyeCommented:
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.
Lawrence BarnesCommented:
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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.

Dale FyeCommented:
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 "
mlcktmguyAuthor Commented:
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.
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
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
Dale FyeCommented:

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!

Anthony PerkinsCommented:
There is a pro version of SQL that will hold up to 4gb which is free.
It is 10GB.
Lawrence BarnesCommented:
Thanks for the clarification ACPerkins.
mlcktmguyAuthor Commented:
Thanks for all the great advice and information.  I split the points among responders

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now