Auto updating linked tables in Microsoft Access

Just thought I’d write a quick post on something useful I discovered through a recent project.  The project involved using Access as a frontend to a MySQL database using linked tables.  My development system used a different database to the client so it required re-linking the database tables when I sent them a copy of the Access frontend.

The issue was the client had difficulty in making those changes to the linked tables for each new version.  So I had a brilliant idea to update the linked tables automatically when the database is opened.  In my case I created an autoexec macro in Access to run the following code when the application is opened.

Some comments about the code; GetConnectionString() is a function which returns the correct connection string for the MySQL instance.  In my case it gives a different string depending on the environment it is in.  So far I haven’t been able to generate an error in the re-linking process so some more testing is required to ensure that there aren’t any bugs.

Public Function UpdateLinkedTables() As Boolean

   Dim TblDef As TableDef

   Dim ConnString As String

   ConnString = "ODBC;" & GetConnectionString

   For Each TblDef In CurrentDb.TableDefs

       ' Check if the table is an attached ODBC table.  This is the case for all MySQL

       ' tables linked to this database and not for any system tables.

       If (TblDef.Attributes And dbAttachedODBC) Then

           If TblDef.Connect <> ConnString Then

               TblDef.Connect = ConnString

               TblDef.RefreshLink

           End If

       End If

   Next TblDef

   ' Note I have not been able to generate errors using the above commands so I'm not

   ' sure how to error check here.

   MsgBox "The linked tables have been updated"

   UpdateLinkedTables = True

End Function