Solved

Access database suddenly slow

Posted on 2012-04-06
2
679 Views
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.
0
Comment
Question by:baabaa_nl
2 Comments
 
LVL 83

Assisted Solution

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

http://office.microsoft.com/en-us/access-help/compact-and-repair-an-access-file-HP005187449.aspx
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 450 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.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

776 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