Linking Tables in Access Databases
Access allows one database to use tables in another one, even on a
different computer in the network, through a process known as "linking".
Using linked tables permits update of the user interface part, also
called the front-end (queries, forms, reports, macros, modules, and
local lookup tables), without having to worry about overlaying the
user's data. This is particularly important in a multiuser environment,
where each user can have a copy of the front end, and share data through
a linked back-end database (tables with data, and relationships) on
another computer in the network. It may also be used to circumvent the 1
GB (Access 97 and earlier) or 2 GB (Access 2000 and later) limitation on
the size of a single Access database.
Overview
To link tables in a database, you must have full permissions on the
folder in which that database resides. On the Database Window menu,
choose File, select Get External Data, select Link, and then locate the
database of interest in the dialog that results. (In addition to other
Access databases, you can link tables native to other databases, such as
dBase, Paradox, and others, for use by your Access database application.
If you have ODBC drivers installed for an ODBC-compliant database,
you can link tables via ODBC. Many server databases (such as Microsoft
SQL Server and many other manufacturer's databases), as well as other
"desktop" databases have available ODBC drivers. This is a useful
configuration for Access client applications to server databases with
modest sized user audiences (depending on the server, up to several
hundred users).
Direct links, such as those to Access tables, can be maintained using
the Linked Table Manager provided in Access (Tools | Database Utilities
| Linked Table Manager) if you execute the database under retail Access.
If you are executing under the Access runtime, sample code is available
in the Developer Solutions sample database -- that is included on the
installation disks with Access 97 and earlier and, for Access 2000 and
later, can be downloaded from
the Microsoft Developer Network Library. For ODBC links to server
databases, you may find it necessary to delete the links and recreate
them.
Hints and Suggestions
One common complaint is that the dialog for Get External Data does
not contain an "All" option, so that you cannot easily link tables in an
Access database that has been renamed with a different file extension.
This is often done to secure a database by obscuring the fact that it is
an Access database (CAUTION: it is not strong
security!), and Access table databases are used in many commercial
software products. Eric Fleischman, Microsoft, gave me this
solution, which is much simpler than the one previously posted here:
- As usual, on the File menu, choose Get External Data, and select
Import or Link
- In the File Name box, enter "*.*" (without the quotes, of course)
-- that will show all files and you can select any one you wish
- Follow the prompts to select the objects you want.
Thank you very much,
Eric!