Several years ago, I designed a Microsoft Access application called JobSearch Plus for managing job leads during an employment search.  It was a split application, meaning that the tables were stored in one Access file, the back-end, and everything else was in the front-end file with table links to the other one.

JobSearch Plus was also meant for distribution and I had no idea where any of the users were going to store the files on their computers.  Access uses absolute file paths to specify the source database for a linked table. This meant I needed some way to determine if the back-end existed and what it’s file path was.

On it’s own, relinking Access tables in VBA is just a few lines of code.

 Dim dbCurr As Database
 Dim tdfTableLink As TableDef
 
 For Each tdfTableLink In dbCurr.TableDefs
   tdfTableLink.Connect = ";DATABASE=" & (Insert new file path)
   tdfTableLink.RefreshLink
 Next

This is fine if you know exactly where the new file is but my program could be anywhere on the user’s computer so I had to do some more engineering.  Also, since this was going out over the Internet to who knows how many strangers with my name on it, it had to work right … always.  This is where the fun of actual software development comes in.

Engineering Around Murphy’s Law

“If it can go wrong, it will go wrong.” is not negativity or paranoia; it’s a good thing to keep in mind if you don’t want unpleasant surprises from your software.  When releasing code into the wild, it’s a safe bet.

I let the code require the back-end file to be in the same directory as the front-end file or in a .\Data subdirectory created by the installation.  If it wasn’t in the same folder as the front-end, then the program should re-copy the file from the Data subdirectory, enabling the user to start with a new database.  If it wasn’t present in either location, then the program simply notified the user that it needed to be re-installed.

In order for this to happen successfully, every time, no matter what directory they’re stored in, the code has to be a little intelligent … or at least very careful.  It needed a plan – in programming terms, an algorithm.

The program’s splash screen would start the process to ensure that the check was run every time the program started.  This would guard against a missing back-end file or just allow the user to quickly reset the program by deleting it and letting the program copy a new one. Among other tasks, the form’s On Open event called a RelinkTables procedure to start the process.

This led to the first decision on the program’s part – The splash screen is also the program’s Credit’s screen. What if the user pulls it up while the program is running?

'If the Leads list is already open, this form has been called by the user.  
'Don't do anything.
 If CurrentProject.AllForms("frmLeadsList").IsLoaded Then
   Exit Sub
 End If

In this case, it’s simple enough to determine if an Access form is already loaded and take the appropriate action as shown above.

Relinking the Tables

Most of the time, the program’s going to be searching for the data file but I wrote the procedure to allow for a data file location to be specified.

Public Sub RelinkTables(Optional DataFile As Variant)

You can find the entire program and its code on Github.  I mostly want to go over the steps involved and highlight some of the things I thought about as I was working out the process.

First we need to know where the data file is supposed to be and that means looking at the connection path for the tables.  This code assumes that there is only one back-end file to deal with although Access does allow for many data sources to be called by a single database file.

The MSysObjects system table stores the vital information of all objects within the database, including linked tables.  For linked tables, the Database field within this table shows the path and filename that the table is linking to.  Knowing this, it’s simple to write a subroutine that will find the database for a specific linked table.

Public Function TableConnectPath(Optional MainTable As String) As String

...

Dim strConnect as String

strConnect = Nz(DLookup("[MSysObjects]![Database]", "MSysObjects", 
  "[MSysObjects]![Type] = 6 AND [MSysObjects]![Name] = '" & MainTable & "'"), "")

I try to make code reusable whenever possible and I did need to call it a couple times during the process so I put this in its own function.  The function also allows for a specific table to be used during the search. If one is not supplied, it will just look for the first linked table it finds in MSysObjects.  In this program, I  have it look at the main job leads table.

The DLookup function above looks for the Database field value for the specified table name. Just to be safe, it specifies a Type value of 6 which indicates a linked table.  For a second level of safety, it uses the NZ function to return a zero-length string if the DLookup doesn’t find any matching records.  If it returns a zero-length string, the TableConnectPath function shown above returns the path of the current file (CurrentProject.FullName) to indicate the table is local.

If the table is local and no data file was specified, the rest of the RelinkTables function is skipped and nothing happens.  If a data file was specified in MSysObjects or passed to the procedure, the procedure carries on with that.

Where’s the Data?

Assuming we have a specific location for the back-end data file, the program now needs to verify that it actually exists and is valid.  Files get deleted, Access files get corrupt, etc..

The RelinkTables procedure then calls a separate function, FindDataFile, which will return the actual location of the data file, if it can find it.

Public Function FindDataFile(Optional CheckLocation As String) As String

If no location is suggested, FindDataFile will go back to the TableConnectPath function to look for the database file associated with the Leads table. If that table is  not local, the function will then verify that the remote file exists.

This is where the code has to be a little flexible. Access has variations on its file extensions and I had to allow for different ones depending on the program edition and whether I was using a development version.

 'Proper location for the datastore is in the same directory as the front-end.
 'If the file has not been found yet ...
 If Len(strLocation) = 0 Then
   'Look for a file in the current path with a recognized name.
   strDataHome = CurrentProject.Path & "\" & BACKEND_NAME
   If Len(Dir(strDataHome)) > 0 Then
     'If there's one that matches, get the exact name, allowing for extension.
     strLocation = CurrentProject.Path & "\" & Dir(strDataHome)
   End If
 End If

BACKEND_NAME is actually a constant containing a wildcard value that allows for the varying Access extensions.  The Dir() function in VBA accepts a file path and verifies it exists by returning the file name it finds there or a blank string if the file doesn’t exist.  In this way, the location string is assembled if the file exists.

This function actually has to do some detective work. If it doesn’t find the data file where it’s supposed to be, it has to go looking for it so it looks in the following places:

• Location reported by MSysObjects
• Matching file in the same folder as the program
• Backup file in the .\Data directory.

If it finds a file in one of those places, it verifies the file as shown in the next section before returning its location to the RelinkTables procedure.  If it doesn’t find a verified file, it returns a blank string.

Is the Data Valid?

Assuming the program has found a data file, it now checks to see if the file is actually valid.

Public Function VerifyRestoreFile(TargetFile As String) As Boolean

The function checks the following:

• File exists
• File extension hasn’t been tampered with
• All required tables are present within the file

A couple of these checks have already been done but this function is also being used by the program’s backup and restore process so it checks for itself.

If the file does not have the required tables, it’s considered invalid. Originally, the calling function (FindDataFile) would move the offending file elsewhere and rename it using the word RECOVER so the user could try to recover the data.  While reviewing the code for this article, I decided to tidy that up and have the RelinkTables routine do it instead.  Functions are supposed to focus on returning the requested values, not performing file operations.

Just tell me we have some data !!

If the FindDataFile function has returned a reference to a data file, the RelinkTables function needs to verify it’s the file that was originally expected.

• If the actual path of the data file is the one already shown in the MSysObjects, even if DataStoreLocation had to restore it from the Data subdirectory, everything is good.  The table links will work.  Do nothing.
• If the actual path differs from the one shown in MSysObjects, this either means the program files have been moved or the user has just installed the program.  Relink all the tables to the new data file.
• If the data file was found in the .\Data directory, copy it to the main directory. If this will result in overwriting a file there, set that file aside for recovery by renaming it “RECOVER.accdb”
• If no data file was found after all those steps, notify the user and offer to run the function restore a backup.

Reviewing the Code

As I said before, I originally wrote this several years ago and haven’t had reason to really look at it since.  Now that I’ve taken the time to write it up here and examine the code, I see some of the things that I could have done better.  VBA will let you get away with some silly things like functions that carry out file operations and do way too many things besides but it’s ultimately the programmer’s responsibility to write good code. It’s also important to be willing review and question your work.

Another useful practice for examining a process is to flowchart it out.  I found an excellent online flowcharting tool called LucidChart a few months ago and I took some time to put together a flow of the process. This alerted me to one or to more weaknesses that I needed to fix.

If you look at the code itself, you’ll see a lot of conditional operations (If .. Then) as the program makes one decision after another.  Once a program has to ask more than two or three questions, a flowchart is almost a necessity so that you can actually see the process graphically and spot any missed paths.  If it starts looking like Bandersnatch, the flowchart might help you simplify things or at least keep you from having to tell a user “Sorry, I forgot to code for that choice.”