Check Access Table Links on Startup
How to use VBA to re-establish linked tables when the front end of a split Access database is opened by a user.
Last updated on 2018-04-13 by David Wallis
It's my contention that good practice is to have a split database in which the front end automatically checks for the back end and re-link to its tables .
Also, that it a plus for the client if the developer has given control to the client of where the front end and back end files are located.
The progam described here could be applied to the database described on my create contacts database page.
All DMW databases supplied to clients are split between BE (Back End) and FE (Front End). The BE comprises of tables only, as containers for the client's data. In a multi-user database the BE is in a folder on a LAN (Local Area Network) server. I name the BE file DATA
All the forms, queries, reports, VBA code and so on that make up the user's view of the database are contained in the FE. Each user gets a FE on their workstation. I name the BE file USER
The FE uses links to the tables in the BE. Such links can be made manually, using Access's Link Table Manager, for example, or programmatically using VBA procedures, which are the topic of this article.
These are some of reasons I choose to automate the linking of tables as part of a start-up process initiated each time a user opens the FE:
- The presence of the BE file is confirmed as being accessible to the FE. (It can happen that someone moves the BE, not understanding the ramifications of the move!)
- A check is made for the full compliment of tables before the user gets to make use of the records in any of them. Those occasions on which I've suffered the office's “Access expert” fiddling with the table structure in a BE have convinced me that this check is a sensible step.
- If the BE file has become damaged, the user is posted a warning and prevented from using the database until the BE is restored. This is a precaution against file corruption. (I've been told of a number databases already showing signs of corruption that remained in use until they finally ground to a hault. Some of these failed restoration from backups because backups had been made of files that were already corrupted!)
- The client can choose where to place their BE and FE files. They do not need intervention from me if when choose to move files, folders, servers and so on.
In addition to the DATA and USER files, there is a third file, KEY.ini, that completes the database package. The purpose of KEY.ini is to provide the means by which my client can themselves point USER to the folder in which DATA resides.
Tracking Back End File Location — “KEY.ini”
In my experience, clients like to be able to determine for themselves in which folder to place the BE.
Also, clients want to be able to change that folder without needing me to tweak any code. This is a particular requirement when a client's IT want the freedom to change locations for files on a network or to re-map drives.
Over the years, I've tried a number of ways of providing for this, currently settling on the use of the KEY.ini that is a simple text file, the content of which is this:
It is vital that the name of your BE recorded against DataPath includes its file extension.
You must see to it that a copy of KEY.ini is placed alongside each user's USER file. That means in the same folder on their workstation.
Structuring the Startup Program
The program that checks the links to tables resides in the FE. This program procedure, dmwStartUp(), determines the sequence of VBA sub-procedures, each performing a descrete operation in the overall linking porcess.
This is the skeleton of the dmwStartUp() program:
In addition to those four main sub-procedures, dmwStartUp() makes use of these the function dmwBlnFile for checking the existence of a given file, in this case, of DATA.
These are the roles played by each of the main sub-procedures:
dmwGetPathFromKEY This VBA-function sub-routine will look for KEY and get the export path from it. If dmwGetPathFromKEY cannot locate KEY.ini, or is unable the find the information about the export path, then it will return an error message to dmwStartUp().
dmwListLinkedTables This VBA-function sub-routine provide a list of the tables in DATA to where that list is needed in dmwStartUp().
dmwDeleteLinkedTables This VBA-function sub-routine deletes any links that USER has to external tables. This makes sure that USER gets a fresh set of links and demolishes any legacy links that may be hanging around and are no longer relevant.
dmwLinkTables This VBA-function sub-routine creates links to those tables, listed by dmwListLinkedTables, in DATA as identified by dmwCheckPath.
If at any point in the program a sub-procedure throws up an error, dmwStartUp() presents a message to the user and stops any use of the database, pending appropriate attention to the problem.
The VBA code for each of the sub-procedures appears below, followed by the fully-fledged dmwStartUp().
This function determines whether a file, as identified by a full path description, exists at the location supplied to its file$ argument.
dmwBlnFile returns TRUE if it locates the file in the stated folder, FALSE if it doesn't.
The job of the sub-routine dmwGetPathFromKEY is to retrieve the path of the back-end DATA file from KEY:
If it's unable to return the whereabouts of DATA, then dmwGetPathFromKEY warnings from which dmwStartUp composes messages to the user.
dmwListLinkedTables fills an array tbls$. This array has eight rows, in this example corresponding to eight tables in DATA.
tbls$ has three columns. The first holds the names of the tables as they appear in DATA. The second, the names of those tables as you want them to appear in USER when they are linked in.
The third columnn holds the path to DATA, passed to dmwListLinkedTables by way of its BE$ argument.
For this example the third column is overkill. But it's there should you ever need to link to tables in different back ends. Should you, include an argument for each BE.
dmwListLinkedTables returns a zero if it completes the array. If it fails, it returns the number of the error that its error handler identifies.
Before initiating linking of BE tables into the FE, I opt to delete existing links so that linking can start from a fresh sheet — sometimes a table or two in the BE, introduced during the development of a database, are no longer needed by the FE and can therefore be excluded from the linking process.
An Access database has a number of tables in addition to the ones you introduce. One of these is MSysObjects. The SQL$ query in dmwDeleteLinkedTables filters MSysObjects for those tables the names of which begin with tbl or tlu.
Without exception, I use these prefixes in all the databases I create; hence I can rely on them for this procedure to work.
dmwDeleteLinkedTables returns a zero if it completes satisfactorily. If it fails, it returns the number of the error that its error handler identifies.
dmwLinkTables performs the linking of the tables contained in the array of table names generated by dmwListLinkedTables.
dmwLinkTables returns a zero if it completes satisfactorily. If it fails, it returns the number of the error that its error handler identifies.
The Controlling Procedure — dmwLinkTables
In databases I create I place the code described in this article in a module named modStartup. There are four local module-level constants and variables to declare: pINI$, BE$, tbls$() and pFrm$.
We can now complete the dmwStarUp() program with the sequence of sub-routines:
On successful completion dmwStartUp() opens the form, in this example named “frmNavigation”. Should the process fail it closes Access altogether after presenting the user with a message explaining why.