Question

The following question is one I have struggled with myself, and I am sure many of you have as well, if you have put your database on a network and it is shared by more than one user.

I developed a database application in Access 2000 and placed it on a network shared drive.  I was running into data corruption issues (but the speed was fine) so I split the database into a FE/BE solution. The front-end on the user’s hard drive and the back-end still on the network shared drive.  The split solved my corruption issue, however now I have a big performance issue.  The front-end components are fast as expected, but it is brutally slow anytime a table is accessed.  I have tried the following common fixes, which helped a bit, but it is still way too slow:

-established a persistent recordset connection by accessing a table that is always open -Set the sub datasheet Name property set to [None]

- Track name AutoCorrect is off.

It doesn't seem to be a hardware issue because the speed was fine before the split.  Something in splitting the database appears to have drastically affected the performance.

Answer

I often run into the same issue.  I find the following site very useful, just run through all the suggestions:

http://www.granite.ab.ca/access/performancefaq.htm

This site also has a few good tips:

http://www.hal-pc.org/journal/12access.html

I have not found any one solution that is the golden answer.  I do the three you mention and I also do the following:

Put any tables that are not likely to change on the front-end version.  I also make the back-end name as short as possible (be.mdb) and store this as close to the top of the network folders as possible. I also implement many of the ideas in the above sites.

It seems to be a bit of trial and error, sometimes a suggestion works really well in one database but not in another.

Do you like this tip? Subscribe to my free weekly Newsletter to receive tips weekly via email. Click Here to subscribe.