Linking an external table in Access
It is good practice to
split
an Access application into a frontend database holding the forms, program code,
queries and reports, and a backend database that does nothing but hold
the data tables. This is not a true client-server setup but the arrangement does gives you many
advantages in security and performance - especially where the data is being shared between
several users across a network:
-
Better performance because the forms and reports can be in a database on
the user's local hard drive. The files don't have to be downloaded
across the network each time that the user requests them.
-
Easier backup because you are only backing up the data files. These can be backed up every day
if necessary but the frontend need only be backed up when some change is made to the user
interface.
-
The data-holding database remains relatively small in size because it's not being bloated by
temporary queries from all the users. They keep their own ad-hoc queries in their own copy of
the frontend database on their own PC. If a local copy gets too large then it's only that one
user that's affected.
-
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 the same PC. You can be certain that
you are completely isolated from live data whilst you are developing and testing.
Practical problems with linking
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.
Automatically link the tables
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 for the moment. Call this subroutine before you try
to use a table for the first time. Pass it two parameters, 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.
The connection is made outside this sub because you may make a dozen consecutive calls to the sub
as you open a dozen tables and the code would run very much more slowly if it were creating and
dropping the connection to the database 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
Set
tdf = gdbs.CreateTableDef(strTable)
tdf.Connect = ';DATABASE=' & strDb
tdf.SourceTableName = strTable
gdbs.TableDefs.Append tdf
Exit_LinkTable:
Exit Sub
Err_LinkTable:
If
Err.Number = LT_LINKEDALREADY
Then
Resume
Exit_LinkTable
Else
End If
End Sub
For a more flexible solution you can 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.
|
