Microsoft access too slow - update to what? Using Access FE/BE

Hi all,

We currently have a front end/back end Microsoft Access application. I have recently become IT amanager and have been looking into upgrading our system.

I am after some advice if possible...

The main back-end database is currently around 50 Meg, and is situated on our server, which runs on a 100Mn/s Ethernet network with switches.

The database has 52 tables, which i believe is too many for an access database?

Accessing the database using the front end, (which before i arrived was being run from the server as well via links from user desktops) is done from users PC's, with links to the tables in the backend.

The database is VERY VERY slow.

The main reason for looking into otherwise of providing this application to the clients.

1.   I am pretty sure that several of the tables, such as 'constants', 'Days' and certain codes are just being used for list boxes - is this bad practice - wouldn't it be better just listing them in the required field?

2.     Surely linking things just to display data is going to make obtaining information from queries that much faster?

3.     I am looking at upgrading - which direction should i go? I was thinking of developing a web-based application, with client side files and access server database   would that be faster than using the access forms to look at/change data?

4.   I was thinking of using ASP.NET and VB.NET as an application base. would this be faster than other options such as PHP?

5.    If we upgrade to a web app, would MS Access be a slow database to use - or should i go for MySQL (we can't afford SQL server) for a speed increase?

look forward to hearing your respnses,


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

52? Our current application uses 140 tables. 50MB is not excessive by any means.

The most important thing to check is if your tables are correctly indexed, whether your queries are WELL written (i.e., using criteria where necessary, avoiding cartesian joins, avoiding VBA functions unless absolutely necessary) etc. etc. The other important factor is how many concurrent users you have.

From what you describe, your database may not be efficiently written or well optimised, but that is a snap judgement. I don't know what it does, but in a like-for-like situation, Access can OUTPERFORM many database systems - so long as it is used in an appropriate manner. Its main limitation comes in a heavy multiuser environment. Once you start exceeding 15-20 SIMULTANEOUS users, you should consider alternatives.

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
I'd agree with Shane...the size of your db is not an issue unless

1) your app is poorly written
2) your have alot of users

you can use the Optimizer wizard in Access...this will help somewhat...(and maybe alot) with performance increase due to indexing and will give some other options

there is a version of SQL server called MSDE and it performs very well in multi user is free...but throttled...that is as users hits to the db go up, performance slows down...That sounds bad but in practice, you can get very nice performance out of it for at least 25 users...especially if you use stroed procedures and proper indexing and such...this is NOT a simple fix as it requires a lot of rewrite but it's what I did about a year ago and have been very happy...

the other benefit of msde is that there is 100% compatibilit with SQL when/if you do need to upgrade  it's very simple...

As for a Web forewarned...if your organization has anything but the most basic reporting needs...a web app is a HUGE pain...
willbdmanAuthor Commented:
You have both made valid points - and what i feared.

We currently have only around 5 or 6 concurrent users.

I will look at just sorting out this mess of a database.

I don't really understand what or how joins work but we have a lot of queries that use them. Also a lot of update queries and link queries?

Do you think if i try and sort the database out it will solve a lot of our problems?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

I would imagine so. Access is an application that is easy to use badly, and design poor systems in. Something that often looks like it works in a small test environment may not scale up properly with large amounts of data. An excellent example of this is the IN SQL clause - works great with a few hundred records but very poorly for a few thousand unless written with a criterion.

That's the difference between getting your app written by a professional Access db designer and someone who just "knows" Access, I'm afraid. Poor understanding of Access application design is probably responsible for most of the misconceptions about Access' performance.
I have nothing to add about planning & designing, nor the number of tables in your apps.

My only comment is does your Access db get compacted often?  This normally shrinks down the file size.  I do that to both my FE & BE on a regular basis.  Since I distribute a new FE pretty frequently to my users, I don't really care if they compact their FE, I only care about MY Speed.  Compact the BE when NO ONE is connected to it.

ps:  Hey Shane!
Hey Silly :-)
willbdmanAuthor Commented:
Thanks a lot for your responses.

Can joins affect performance?

What exactly do joins do? - Just 'join' two tables together that aren't related?

Yes i compact regularly
Joins are (in general) the most efficient way to relate data in two tables together. There are other ways, but the order of evaluation means that joins are more efficient than using WHERE clauses - although their performance can be reduced in certain circumstances, such as using LIKE operators in them. The most important thing is to index any and all fields on which joins are performed - not doing so (and not indexing criteria fields) can lead to a *significant* reduction in speed - I've seen a query drop to a few (5-10) seconds from ten minutes after indexes were applied - admittedly, the original table was creating with a make-table query, had hundreds of thousands of records on it and had NO indexes at all!
if you use the

Tools>Analyze> Performance will get a general idea if you have some big isn't perfect but it's pretty good.
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.

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.