MS Access back end located on slow network drive

Question

I currently have an Access 2003 app that is used primarialy by three people. It is split into a front end which each person has a copy of on their own machine and a back end on the server. When a user logs into the front end, the front end runs a start up routine that checks table links, version number and a few other things. The problem is that the shared drive is slow, very slow. It can take over a minute for the routine to finish up.

I tested it on another network that I know is faster. It took about 10 seconds to accomplish the same tasks. Unfortunately I don't have any choice on which network to put the app so it can be shared by the people who need to use it. Does anyone have any suggestions as to what I can do on my end to speed things up? It seems to be more of a network issue than a problem with my design but my chances of getting help from our IT department are zero.

Answer 1

The first thing to do is to open a database variable against the backend. This keeps the lock on the backend LDB file open so Access doesn't waste time working with it. You might also want to open a recordset variable.

This is particularly important when relinking tables, for example, when you make new front end available to your users which has been pointing to the development database file and which needs to relink to the production database file. The time spent relinking tables will drop from minutes to seconds.

Also visit my Access Performance FAQ page.