Access database suddenly slow

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.
baabaa_nlAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CodeCruiserCommented:
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
Jacques Bourgeois (James Burger)PresidentCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.