Improving Performance of Linked Table Routine

[GummowN]

I am responsible for the development and maintenance of a multi-site multi-user access/access database environment. Typically we have at site:
Back End Database (Site Specific) (MULTIPLE INSTANCES)
Front End Database (Generic Application)

Typically I will update the application and then roll-out to the sites (Via a PC-Anywhere transfer). Time consuming as we have 20 sites and the application is 8Mb in size.

We normally have a copy of the application for each back end database which we manually have to link (via either the standard Microsoft Popup box or from a datasource text file that is stored locally).

Each user will logon daily and via their login script receive a fresh copy of the application (helps manage bloat!).

Some sites may have 5 or 6 back end databases which require linking, and so finally to the question.

Does anyone have any idea how I can improve the performance of either:
Linking tables - I already have code for this which connects and then links the table. Performance of this can range from slow to painful if other users are connected to the back-end database.
Or
Storing paths/filenames in the application so when transfered to the server it will create copies of the application and relink automatically to the correct sources?

Thanks for any ideas

 

[Rolliee]

WROX publishers say that using unbound tables can improve performance manyfold. One of the tech-tip users says to try one dummy table with a hidden form in the hiddden mode to increase the link times.

 

[Rolliee]

WROX publishers say that using unbound forms can improve performance manyfold. One of the tech-tip users says to try one dummy table with a hidden form in the hiddden mode to increase the link times.

 

[scriverb]

I am guessing that you are talking about the slow-down process initially after the relinking of the front-end to the back-ends? This happens as a result of ACCESS Jet recognizing that there are new tables because the links have changed or been added and the first time each query in the front end runs the Jet engine optimizes the query.

This is because you are providing a new copy of the front-end at each daily logon by a user. Whenever you send out a new front-end it is necessary to have executed each query that is new or has been changed so that this optimization is complete before distribution.

It is suggested that you create a stand build for your PC's where each and every one of them is hard linked always to a letter mapping.(i.e. G:\BackendFolder\ ) Then your developers link to the necessary tables on their PC's using this same letter mapping. When they optimize their queries in development and then distribute the front-end to the users there will no need to re-link anything at the User PC/Server site. All back-end databases and tables are stored at the same location on the servers and the front-ends are all linked properly and the queries in the database are optimized already.

Our office experienced just this problem due to having multiple servers in each office prior to migrating to a single server and Windows 2000. Our back-ends were on the primary domain server but the PC's each were hard letter mapped to one of three local servers. We had to perform the relinking process to the IP address of the Primary Domain server with each front-end distribution. It was a nightmare because the Jet engine then began optimizing each query as the users tried to use the application. This was a very slow process but only happened once with each distribution.

You on the other hand to "manage bloat" are distributing the same front-end with each days logon. Optimizing the queries daily must be killing worker production terribly.

Think about the different options and get back with me and maybe I can help some more. You can manage bloat of the front end by performing Compact and Repair through another database that executes prior to opening up the front-end. You shortcut can point to a database that has some code in a module that executes through the AutoExec and performs the "Compact and Repair" of your front-end application database, then through code opens the front-end and closes itself.

I can help you with that process. Bob Scriver