Access database suddenly slow

Posted on 2012-04-06
Medium Priority
Last Modified: 2012-04-12
Hi Experts,

I am developing an application on vb.net using access as my database.

I have been developing on the access database file for more than 5 months now and it was working very fast  until for the past two days, even the simplest sum filter and query takes up to 5 to 8 seconds delay.

I am sure that I did not make any changes to the database or to the connectivity on the vb.net code. i am using the same kind of connectivity code as i was using for the past 5 months.

Also i was using the database file on my local machine and also on my shared computer across the network, and never got a problem.

After the delay started, i even put the database file on the local machine and tried working with it, but still it has got the same delay.

Could you please help me to make it run faster. The size of the database file is around 800kb.

Thanks in advance.
Question by:baabaa_nl
LVL 83

Assisted Solution

CodeCruiser earned 200 total points
ID: 37816607
Have you tried doing a "Repair and Compact Database" in Access?

LVL 40

Accepted Solution

Jacques Bourgeois (James Burger) earned 1800 total points
ID: 37820159
CodeCruiser suggestion is the first thing to try, but you might want to go further, because even on a properly compacted Access database, you eventually end up having performance problems.

Access as the bad habit of letting you down very fast when the database becomes too big. It won't slow down gradually so that you can prepare. What you describe is quite typical. The size at which it suddenly drops varies depending on the structure of the database and the type of data it contains, but I start being afraid when going near to 1 Mb, which is you case.

Having the proper indexes can help a lot. This it the type of performance enhancement that many do not do are about you do not see the difference between an indexed table and a non-indexed one when the tables are small. But the bigger they become, the bigger the

Look at the queries that are slow. If they filter or sort on some fields, if you join on some fields, indexing those might help. There is a Performance wizard under the Tools->Analyze menu that can help you spot fields that might be causing slow downs if not indexed. Run it on the queries that are too slow.

I do a lot of code review, and theres is a point where I often see programmers stumble with Access. They send the SQL commands from their code instead of calling queries already prepared in Access. Access does not use its indexes when you do so. The indexes are used only when calling already saved Queries.

And finally, the best answer: with the free SQL Server Express, using an Access database as a backend for Visual Basic .NET applications is a very bad idea, unless for some reason your users also use Access itself to work with the data and create custom forms, reports and queries.

I have been teaching Access for close to 20 years, and ADO.NET for 10 years. The first thing I do in both courses is to ask the students if Access is really the right choice for them. When it is so, Access is a wonderful easy tool to use. But I have seen too many databases created in Access that needed to be converted to SQL Server because they became too big (and this means a long time before you get to the therotical limit of 2 Gb) or the number of users increased too much. The conversion is usually a pain if you had a lot of code written against the database and if you pushed Access queries to their limit.

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

624 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