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

I need help with an Access 2003 performance Issue

Hi Experts,
i have written an application in Access 2003, but the performance is terrible when more than 10 users use the application at the same time.  If I use a SQL Server 2005 database with my Access 2003 application, will I still have the same performance issues?

Thank you,
mrotor
0
mainrotor
Asked:
mainrotor
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
" If I use a SQL Server 2005 database with my Access 2003 application, will I still have the same performance issues?"

All things being equal, probably not ... unless this is a network performance issue.

mx
0
 
epichero22Commented:
SQL is more geared towards performance among multiple users, so I would say no and you're bound to get better performance.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
To be fair to Access, I develop / manage approx 20 db's at work in use across 150 people.  One of the db's has upwards of 55-60 users during a good part of the day.

All of the db's we have work over a 1GBbit WAN, with a 60 mile round trip with 5 routers in between our physical location and the server.  However, this specific db has basically ... a 'disconnected' paradigm ... no linked tables for all practical purposes, except on small table with no records that the only purpose is to keep a persistent connection to the backend.  

When a user clicks the 'Submit' button, the OpenDatabase Method is used to open the main table in the BE, find one record out of 700K (currently), update a few fields, close the connection, then update a local frontend copy that contains a subset of those records.  This all happens in less that 0.5 seconds (usually 0.2 sec). And I am not kidding.   There can be several Submits per minute across the 50-60 users. This scenario works VERY well, with virtually no issues.  To say we have a stellar network is an understatement.

So ... my point is ... It can be done.  Some of my colleagues here on EE doubt this.  I have invited them to drop by anytime so to see this in (daily) action :-)

mx
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Anthony PerkinsCommented:
>>i have written an application in Access 2003, but the performance is terrible when more than 10 users use the application at the same time. <<
This probably has more to do with a poorly written app than the actual database used.

>>If I use a SQL Server 2005 database with my Access 2003 application, will I still have the same performance issues?<<
No one can answer that question for you.  It really depends on what you are prepared to do and your technical abilities.  If you are prepared to re-design and re-write your app to take advantage of SQL Server's functionality, you could potentially see a performance improvement.  But then you could also get an improvement by re-designing and re-writing for MS Access.

Truth be told, you should focus less on the database and more on your own technical experience that you can use or hire.  That is what counts, the tool for the most part is irrelevant and only as good as the one using it.

Personally I would not touch MS Access with a barge-pole and I am convinced all your problems would go away if you re-wrote your app with MS SQL Server.  But I am as biased as the next die-hard MS Access developer and quite honestly I do not have a clue, I have not used MS Access in well over 12 years.

Good luck.
0
 
MINDSUPERBCommented:
@mx

Honestly, I am interested with your post ID: 35224506. If you don't mind, do you have written articles/documentation on this that I can look into? I have no doubt that you were able to do it and your expertise on Access speaks for it. If you can share the details on how to achieve it, I really appreciate it.

Thanks.

Sincerely,
Ed
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"do you have written articles/documentation on this that I can look into? "
Sorry Ed, I really do not at this time.  But I did describe the basic process above.

mx
0
 
Anthony PerkinsCommented:
Basically the way I see it, when considering this situation you have to think about the following three question:
1. What is your (or your team's) expertise.  If it is MS Access and you have no or little knowledge of MS SQL Server are you prepared to contract help to get you up to speed?
2. What is your estimated growth for the future?  Do you expect the same 10 users in the next 5 years or could it increase to 100 or 1,000 or 10,000.  Do you have size limitations?  Are you planning in the future to be able to view the data on the web?  In other words do you need a platform to scale and are you prepared for the considerable work that it will take?
3. Finally and of less importance, how important is the support for the platform you choose?  Ask yourself where do you think MS Access (or MS SQL Server for that matter) will be in 5 years?  While there are many applications still relying on dead databases such as FoxPro is that where you want to be?

So the question that you have to ask yourself when thinking about this:
A.  Is MS Access going to do it for you?  Can you use your current technical abilities to enhance it so that it can support 10 users or say 100 users?  You may well be able to do it.  I certainly cannot (and would) not gurantee it, but I am sure you will find some that will assure you that it does.

B. On the other hand is MS SQL Server something that you can use?  It is certainly scalable (and even the Express Edition handles databases up to 10GB in size), but are you prepared to take advantage of all the functionality that SQL Server offers?  If the answer is no or your no sure, than you had best stay with what you know.  Too often MS Access developers see MS SQL Server as an upgrade, when that could not be further from the truth.  The result is not only frustration but disappointment when they realize that their code does not migrate well or if it does it is a dog and worse than on MS SQL Server.

In the end, I suspect you will find that no one will disagree with the fact that you probably need to re-analyze and re-write you application to make it more scalable, but only you can decide what tool to use.
0
 
Anthony PerkinsCommented:
And this:
The result is not only frustration but disappointment when they realize that their code does not migrate well or if it does it is a dog and worse than on MS SQL Server.

Of course should have read:
The result is not only frustration but disappointment when they realize that their code does not migrate well or if it does it is a dog and worse than on MS Access.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"Of course should have read:"
Again, I fail to see how your negative attitude toward Microsoft Access, wherein based on your profile you have little or no experience with Access ... is helping toward a solution here?

Generally, 10 users with Access is not a problem in most reasonably designed Access applications.  So again, if 10 users is already a problem, then I would suspect network issues, which if that is the case, moving to SQL server may not help that much.

mainrotor:
Start with the basics ...

How to manually split a Access database in Microsoft Access
http://support.microsoft.com/kb/304932

Preparing Your Access 2003 Database for Deployment, Part 1
http://msdn.microsoft.com/en-us/library/aa662933.aspx

http://www.fmsinc.com/MicrosoftAccess/DatabaseSplitter/Index.html

This EE link has a lot of good info also:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23986481.html?cid=748#a23179169

Then ... you need to have a *separate* copy of the front end (FE) on each work station for maximum reliability and performance ... must do.

Performance related:
http://www.granite.ab.ca/access/performancefaq.htm

http://www.fmsinc.com/MicrosoftAccess/Performance.html

I have many more links when you need them.  Get familiar with the basics of multi-user apps first.

mx



0
 
Anthony PerkinsCommented:
>>Again, I fail to see how your negative attitude toward Microsoft Access<<
I am sorry if I was not clear, but I suspect you may have misunderstood what I wrote.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I am mainly referring to the 1st sentence of the last paragraph @ http:#a35227098

mx
0
 
Anthony PerkinsCommented:
This one?
In the end, I suspect you will find that no one will disagree with the fact that you probably need to re-analyze and re-write you application to make it more scalable, but only you can decide what tool to use.

I am confused.  I thought you were saying much the same from a different angle.
0
 
Anthony PerkinsCommented:
Or perhaps you were referring to this (I have corrected the typo):
The result is not only frustration but disappointment when they realize that their code does not migrate well or if it does it is a dog and worse than on MS Access.

Here is my take on this:  Countless times MS Access developers think that MS SQL Server is just the logical upgrade for MS Access and are astonished not to mention frustrated and disappointed when their application actually fares worse using MS SQL Server.  

My use of the word "worse" in this case was merely to denote the fact that the author is already having problems and to alert them than it may not get any better if they migrated to MS SQL Server unless they took advantage of all that SQL Server offers.

Do you not agree with this?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"Personally I would not touch MS Access with a barge-pole"
Which is actually in a different comment.  I copied the wrong ID.

mx
0
 
Anthony PerkinsCommented:
>>"Personally I would not touch MS Access with a barge-pole"<<
But that is exactly in line with what I have been stating all along and I think you make my point very well:
I do not have much experience with MS Access therefore I would never consider using it.   This just makes common sense.  For example, take the old Java vs. .NET debate.  Would I ever contemplate using Java instead of .Net? Knowing my severe knowledge limitations in the former and if I have a choice I would not use it.  This is a personal choice, could I have phrased it better, no doubt, but the outcome is no different.

But again the bottom line is that I do not believe we disagree, as far as the author's question is concerned:
It will require a re-design and re-write, and the choice of database is theirs to make based on some of the points we have discussed.  They now need to do some homework and some soul searching as to how they want to solve their problem.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now