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 End If
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.