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)
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.