Database development and training for Birmingham and the midlands
Specialists in Microsoft Access and Visual Foxpro database training and development
I am happy to hear from existing clients but I am taking no new calls.
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.
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.
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.
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.
If Err.Number = LT_LINKEDALREADY Then
'-- Do nothing - the table's linked in already
'-- Put some code here to handle this error
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.