Relinking tables

(Updated 2010-09-27)

First I attempt to open a linked table.

If not successful then I attempt to read my standard INI file located in the users Front End MDB/MDE/ACCDB/ACCDE file location to determine the BE location.   If that location and file name in the INI file works I relink the tables and continue on with the startup routine.

In all these attempts to validate that the user has located a valid backend I open a specific table in the backend chosen and read a specific field.  If that is successful then I assume the rest of the MDB is valid and appropriate for my application.

If either the INI file doesn't exist or the location and name of the INI file doesn't work then I prompt the user to locate the backend.   I use the API: Call the standard Windows File Open/Save dialog box. 

In my Granite Fleet Manager I went one step further and created a form so the user could switch from the demo BE MDB included with the application to the data BE MDB.

Note the number of units and services orders that are present as I find these are an excellent means for the user to figure out which was what. Also the values Data, Demo and Test come from a specific field in a table in the BE MDB that I update.

I then save the location in an INI file on the users FE folder location although I could easily use the registry.    I then relink the tables.  Sample code for the relinking can be found at Tables: Relink Access tables from code

Back End Update Routine

Note that before I relink the tables or accessing the BE MDB if relinking isn't required I check the version number of the backend, using a field in one record table.  If required, I update the tables, fields, relationships and indexes in the BE using VBA code.   I use the Compare'Em utility for this.  While it has it's quirks it does a reasonably good job.  If I need to move data around then I use Append, Update or Delete queries as appropriate.

[ Access Tips | Access | Main ]

   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Granite Fleet Manager - the best designed fleet maintenance tracking and management system available