Linking a table in Access

It is good practice to split an Access application into a frontend database holding forms, queries, etc, and a backend database that does nothing but hold the data. This arrangement gives you many advantages - especially where the data is being shared across a network:

  • Better performance because the forms can be in a database on the user's local hard drive. They don't have to be downloaded across the network whenever they are needed.
  • Easier backup because you are only backing up the data.
  • Less bloat of the data-holding database because users keep their own ad-hoc queries in their own copy of the form-and-query database on their own PC.
  • Easier and safer maintenance and upgrading of code because you can take a copy of the form-and-query database and run it against dummy data on your own PC. You can be certain that you are completely isolated from live data whilst you are developing and testing.

Practical problems

You can link the tables in by hand whilst you are developing but you will probably have to delete these links and create them again when you deliver the database. And you'll have to do it again every time that the user's network changes. There is a Linked Table Manager in Access that helps a user to recreate links but a better solution is to write code that will link the tables in automatically.

The solution

This code uses DAO to link the named table into the database. If the table is already linked then we'll get an error 3012 - which we'll ignore. Call this subroutine before you try to use a table for the first time. Pass it the name of the table to be linked and the fully-pathed name of the database that holds it.

In this example, gdbs is a public variable of type Database that refers to the current database. It is defined outside this sub because you may make a dozen consecutive calls to the sub to open a dozen tables and the code would run more slowly if it were creating and dropping the database reference twelve times.

Sub LinkTable(strTable As String, strDb As String)

   Const LT_LINKEDALREADY As Integer = 3012

   Dim tdf As TableDef

   On Error GoTo Err_LinkTable

   '-- Create a new TableDef then link the external
   '-- table to it

   Set tdf = gdbs.CreateTableDef(strTable)
   tdf.Connect = ';DATABASE=' & strDb
   tdf.SourceTableName = strTable

   '-- Add this TableDef to the current database.
   gdbs.TableDefs.Append tdf

Exit_LinkTable:
   Exit Sub

Err_LinkTable:
   If Err.Number = LT_LINKEDALREADY Then
     '-- Do nothing - the table's linked in already
     Resume Exit_LinkTable
   Else
     '-- Put some code here to handle this error
   End If
End Sub

For a more flexible solution, store the names of the table and the path to the database in a Config table that is part of the form-and-query frontend database. It then becomes very easy to point the application at a different set of data by changing the entries in the Config table.