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:

1. As usual, on the File menu, choose Get External Data, and select Import or Link
2. In the File Name box, enter "*.*" (without the quotes, of course) -- that will show all files and you can select any one you wish
3. Follow the prompts to select the objects you want.

Thank you very much, Eric!