Slow Backend Access 2007 Database

Question

Hi All

I have a split 2007 Access DB with the backend on a LAN file server (NAS drive) and 3 different frontends loaded onto the 3 PC's hard drives. Each of the frontend modules are different but relate back to the common backend. The frontend files are about 9mb and the backend is about 250mb and growing. It has 35 tables. They are connected via a wired LAN using a 10/100 switch. This is the only application or services running on the LAN.

I did an update to one of the frontends which added another 8 tables into the backend, 4 forms and 12 sub forms to the frontend. Since adding the update the system has started taking about 10-15 seconds to load. Before the update it would load in about 4-5 seconds.

It appears that the backend is the problem as the other frontends have also slowed.

I have changed the 10/100 switch to a gigabit switch which has made no difference.

I have rolled back the update, however the DB has still takes 10-15 seconds to load. I have also Compacted & Repaired and refreshed the table links of the backend and frontends a number of times which has made no difference.

What am I missing? Is there anything I can do to bring the system back to its normal operation speed.

Cheers

Glen

Answer 1

The persistent connection simply means that on application start up that a connection to some (any) back end table is forced to be active at all times.

If the main application form that starts up is say bound to a back end table and this form remains open then you have a persistent connection.

You can test if a persistent connection will fix your issue. On a computer that is linked to the back end, launch Access, open any linked table. Now launch your main application form and try the application. If the delay goes away then a persistent connection will fix this issue.

Two other common issues:

You have forms (or reports) that have a non-default printer (pointing to a network printer that is not available).

Or any linked table to a stray backend database that you don’t have rights or a correct path to.

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 2

I am pretty sure that the NAS isn't causing the problem as I have moved the BE to the hard drive of a desktop machine with no improvement.

I would tend to agree with that. If moving the BE didn't help then the NAS is most likely not the trouble. As a second test, move the BE to one of the client machines running the FE, and reconnect that FE to the new BE location. Does the performance improve? If it does, then you've got network issues. If it does not, you've got troubles with one of the files.

Perhaps try moving the BE to a new, blank database, and then reconnect the FEs.

Answer 3

Hi Scott

Thank you for your reply.

I am using a small Synology NAS which is connected via cat 5 to the switch. I am not sure what model it is as I am not on site but it is something like DS216se with mirrored 500gb drives. I will have a look when I get on site tomorrow and let you know. I am pretty sure that the NAS isn't causing the problem as I have moved the BE to the hard drive of a desktop machine with no improvement.

All 3 FEs are slow to load. After some more looking around, one FE loads a small login form with very little processing which loads very quickly but after a successful login the main form is slow. This problem only started after I implemented an update and has remained after the update was rolled back. Could I have corrupted something in the BE setup? I have also rolled back the BE from backups to before the update and it is still slow.

I work on the BE by copying it onto a desktop, making the changes, Compacting it and then copying it back onto the NAS. I do this as the compact operation times out over the LAN. Could this be a problem?

Hi Daniel

Thank you also.

Please see the discussion above regarding the NAS. 

I do not understand what a "persistent connection" is. I point to the back end by using the UNC path.

I have the record locking set to No Locks as I believe that this is quicker on a multi user app.

Glen

Answer 4

I have had very mixed results with NAS drives.  The first thing I'd try, to eliminate the NAS as the source of your problems, would be to move the back-end onto a standard PC or server and relink the FEs and see if the problem persists or not.

Also, do you create a persistent connection between the FE and BE at the startup of your database?

What type of record locking are you using?

Answer 5

Slowdowns can occur for a variety of reasons.

Have you compacted the backend database lately? Access database will "bloat", and the only way to manage that is to disconnect everyone from the backend, open the backend in Access and Compact it. Before doing this, of course, make a backup.

What sort of NAS device are you using? Is it a wired LAN device, or is it connected via USB or something of that sort. I've always advised clients to use a standard PC to host their backends, and use the NAS for backus and storage. Often a NAS device is slow, and as they age they can get much slower.

Does the slowdown occur for the FIRST FE to connect? Or is that one fast, and the others slow?