Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Access database suddenly slow

Posted on 2012-04-06
2
Medium Priority
?
949 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 200 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 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.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
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 …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

580 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