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
mainrotorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
" 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 Architect / Systems AnalystCommented:
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
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

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 Architect / Systems AnalystCommented:
"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 Architect / Systems AnalystCommented:
"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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 Architect / Systems AnalystCommented:
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 Architect / Systems AnalystCommented:
"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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.