Solved

Access Performance and SQL server

Posted on 2014-01-25
11
603 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +4
11 Comments
 
LVL 27

Accepted Solution

by:
MacroShadow earned 100 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.
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
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.
0
 
LVL 5

Assisted Solution

by:Lawrence Barnes
Lawrence Barnes earned 80 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.
0
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
LVL 57

Assisted Solution

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

Jim.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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 "
0
 
LVL 1

Author Comment

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

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
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:

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)
ID: 39810754
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
ID: 39810943
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
ID: 39810972
Thanks for the clarification ACPerkins.
0
 
LVL 1

Author Closing Comment

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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 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