Solved

Access Performance and SQL server

Posted on 2014-01-25
11
592 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 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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

910 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

24 Experts available now in Live!

Get 1:1 Help Now