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

Posted on 2004-11-09
Last Modified: 2012-06-22
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,


Question by:willbdman
    LVL 41

    Accepted Solution

    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.
    LVL 36

    Assisted Solution

    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...
    LVL 6

    Author Comment

    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?
    LVL 41

    Expert Comment

    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.
    LVL 11

    Expert Comment

    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!
    LVL 41

    Expert Comment

    Hey Silly :-)
    LVL 6

    Author Comment

    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
    LVL 41

    Expert Comment

    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!
    LVL 36

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    758 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

    12 Experts available now in Live!

    Get 1:1 Help Now