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
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, 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.
|
