Solved

Access Performance and SQL server

Posted on 2014-01-25
11
589 Views
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.
0
Comment
Question by:mlcktmguy
  • 3
  • 2
  • 2
  • +4
11 Comments
 
LVL 26

Accepted Solution

by:
MacroShadow earned 100 total points
Comment Utility
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.
http://bytes.com/topic/access/answers/201084-limit-users-ms-access#post769164
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 80 total points
Comment Utility
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.
0
 
LVL 5

Assisted Solution

by:Lawrence Barnes
Lawrence Barnes earned 80 total points
Comment Utility
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.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 80 total points
Comment Utility
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.

Jim.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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 "
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:mlcktmguy
Comment Utility
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:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28348583.html

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.
0
 
LVL 9

Assisted Solution

by:Armen Stein - Microsoft Access MVP since 2006
Armen Stein - Microsoft Access MVP since 2006 earned 80 total points
Comment Utility
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:

http://www.JStreetTech.com/downloads

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

Cheers,
Armen Stein
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Armen,

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!

Dale
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 80 total points
Comment Utility
There is a pro version of SQL that will hold up to 4gb which is free.
It is 10GB.
0
 
LVL 5

Expert Comment

by:Lawrence Barnes
Comment Utility
Thanks for the clarification ACPerkins.
0
 
LVL 1

Author Closing Comment

by:mlcktmguy
Comment Utility
Thanks for all the great advice and information.  I split the points among responders
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now