Solved

Access database suddenly slow

Posted on 2012-04-06
2
616 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

948 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

22 Experts available now in Live!

Get 1:1 Help Now