Slow Access Database

[cdahrens]

I have an Access application that has two Access files and is working very slow. One mdb file holds all of the table data and is located on a shared network drive. The other mdb file is the interface file that sits localy on the users machine. The interface file links to the table data file to view the data.

The only problem with this setup is speed. Drop down boxes take 20 seconds to view and there is very slow feedback to any actions such as button clicks.

When the two files reside on the same machine, the speed problems disapear. This is not an option however, the data needs to be shared between users here and even in Asia.

We have discussed the posibility of seting up SQL server. But were not sure if that would even help that much.

 

[Norie]

This sounds to me like a network issue rather than an Access one.

 

[cdahrens]

Norie, I don't disagree with you. But I can't change our network, and I am trying to find out if there is something I can change.

 

[CT Witter]

For the combo boxes, does the data change that often?
You may benefit by moving the source for those to the local db.

Have you tried using the performance analyser to see if there are any suggestions that you could act on?

I would definitely look at moving to SQL server or you could always test using MSDE first.

HTH,

CT

 

[Norie]

But I can't change our network, and I am trying to find out if there is something I can change.

Do you compact the database on a regular basis?

How are you populating things like comboboxes?

How many people are using it at one time?

 

[cdahrens]

CT: There are 3 combo boxes that update when a user changes one of them. Their source changes regularly and can not be placed locally. As far as SQL Server goes, I don't really know much about it, what would be the benifits?

Norie: The DB is compacted every tenth time it is loaded. I populate the combo boxes after one has been updated by reseting their recordsource. At most, 4 people are using it at the same time.

 

[Norie]

The DB is compacted every tenth time it is loaded

Why not compact it more regularly?

Later versions of Access have an option under Tools to compact on close.

 

[cdahrens]

I have read that compacting it everytime will actually slow down the DB. The queries will need to optimize after each compaction.

 

[CT Witter]

Compacting will not slow down. My source: Access 2002 Desktop Developer's Handbook pg 961.

...Compacting the database may also speed up queries because it forces Jet to write all the data in a table to contiguous pages. This makes scanning much faster.

You may also want to look at defragmenting your disk drives.

CT