Automatic Table Relinking

There are probably as many good table linking scripts out there as there are good Access developers, and mine is by no means the best. However, the repeated requests on the newsgroups for suggestions on how to manage table links compels me to share the bit of code that I have been using for some time. The download for this article, when unpacked correctly, demonstrates how your database application can relink to the data tables, provided the back end MDB file is where it is expected to be.

The sample code in the download demonstrates the following:

What this demonstration omits is a method for navigating to a datasource. If there is enough interest, I could use that as the topic for a future article, but first and foremost is the need to understand how to update the table links themselves.

Basic Relinking Code

In Microsoft Access, all tables appear in what is called the TableDef’s collection. To begin with, let’s create a TableDef object and assign it to a specific table in the collection.  Once we have done that, we can access its properties.  In specific, we are interested in the Connect property.  For tables linked to other Access databases, the string looks like that in the sample code below.  (It varies somewhat for SQL Server, ODBC and other linked databases.)

We use the Debug.Print command to read the connection string and print it to the Immediate window.  This can be useful for capturing the syntax of the string, especially when linking to non-Access databases where the connection string gets more complex.

In this example, the target database is changed from PubsDataB.mdb to PubsDataC.mdb.  After assigning a new value to the Connect property, a call to the RefreshDatabase method assures that the new path is saved and the connection is updated.  Without this call, the updated property value is ignored.

Function ShowConnectInfo()

  Dim dbs As DAO.Database

  Dim tdf As DAO.TableDef

  Set dbs = CurrentDb()

  Set tdf = dbs.TableDefs(“tblSales”)

  Debug.Print tdf.Connect

  
‘ Results of Debug.Print …

  ‘   ;DATABASE=C:\DataFast\Data\PubsDataB.mdb


  tdf.Connect = “;DATABASE=C:\DataFast\Data\PubsDataC.mdb”

  tdf.RefreshLink

  Set tdf = Nothing

  Set dbs = Nothing

End Function

Well, that’s about it. You set the Connect property and call RefreshLink. The only trick in the above code is that, for links to Access tables, you need to prefix the physical path to the mdb file with the text “;DATABASE=”< /FONT> in order for the link to succeed.

The connection string for an ODBC connection to SQL Server might look like this:

ODBC;DRIVER={SQL Server};SERVER=Sparky;DATABASE=Pubs;Trusted_Connection=Yes

The connect string is different, but the process is the same. Just set the connect property and refresh the links. Now you need a way to perform this task against ALL your tables. I usually do that one of two ways: With a table driven list, or by using the TableDefs collection mentioned above. Let’s consider that method first.

TableDefs verses Table-Driven

In the above example, we set the TableDef object to tblSales. Below, we simply loop through ALL the objects in the TableDefs collection, not caring what their names are, looking for any that already have a non-empty connection property value. For those that are linked tables, the Connection property is reset to point to PubsDataC.mdb and the RefreshLink method is called.

Function LinkTableDefs()

  Dim dbs As DAO.Database

  Dim tdf As DAO.TableDef

  Set dbs = CurrentDb()

  
‘ Loop through TableDefs collection, only processing

  ‘ the table if it already has a Connection property.

  ‘ (all other tables are local … not linked)


  For Each tdf In dbs.TableDefs

    If tdf.Connect <> “” Then

      tdf.Connect = “;DATABASE=C:\DataFast\Data\PubsDataC.mdb”

      tdf.RefreshLink

    End If

  Next

End Function

It should be noted, however, that the above code assumes that the new database, PubsDataC.mdb contains all the tables in our collection of links. This is no problem if all of your links point to a single database, but if your application points to tables in numerous Access databases, or a mix of Access and ODBC sources, then you will need to find a more elegant way to assign the Connect property.

Dynamic Path Assignment

One way to dynamically assign the connect string is to store the path for each linked table in a local table list of links. This has the singular disadvantage of having to be updated manually, unlike the TableDefs collection whose membership is maintained by Microsoft Access. If you forget to add a new table to your list of linked tables, it will be excluded from your LinkTables routine and may cause you some embarrassment when your new app is rolled out. Can you say, “It worked fine on my machine?”

Public Function RelinkByList() As Integer

  Dim dbs As DAO.Database

  Dim rst As DAO.Recordset

  Dim tdf As DAO.TableDef

  Set dbs = CurrentDb

  Set rst = dbs.OpenRecordset(“tblLinkedTables”, dbOpenSnapshot)
  
‘ Loop through the recordset, processing rows


  Do Until rst.EOF

    Set tdf = dbs.TableDefs(rst!TableName)

    tdf.Connect = rst!DataFilePath

    tdf.RefreshLink

    rst.MoveNext

  Loop

  Set tdf = Nothing

  Set rst = Nothing

  Set dbs = Nothing

End Function

For this code to work there must be a local table named tblLinkedTables that has at least two fields: TableName and DataFilePath and, as mentioned above, this table must include a row for each table you wish to process.

The beauty of this method is that you can assign different targets for different linked tables. In the demo code, I have PubsDataA.mdb, PubsDataB.mdb each containing three of the six linked tables. So, using my table driven design, I am able to dynamically link to the correct file because the correct path has been saved in the table that contains the physical path to the data file. You could, theoretically, have a different path for each linked table. The sky is the limit!

A Good Start, But …

The above should be enough to get you started, but there is so much more that can be done, once you begin managing table links. I recently delivered several replicated databases where the users wanted to have a local copy on their laptops where they could enter data offline. So, the above code was modified to expose a LocalPath and a ServerPath. When available, the startup procedure would link to the server, but when disconnected, it would point to local tables. The databases were synchronized as need be and the solution was complete.

As mentioned above, this solution works for ODBC connections as well, but in addition to managing linked tables, you may also manage the connection strings for linked queries, better known as PassThrough queries. That too, will have to wait for a future article. In the mean time, download the sample code and take it for a drive.

» See All Articles by Columnist Danny J. Lesandrini