Secrets of the Office Masters: Creating Links to External Databases

In some cases, you might want to incorporate the contents of an external database file (or a collection of related files) into an Access database. If the external file is available on your system or network, and if the data is stored in a format that Access can use, you can incorporate the data into your Access application in either of two ways: You can import a copy of the data, creating a new table in your Access database. You can also create a link to the external table, without actually importing it into your current database. When you create a link, Access allows you to work with the data just as if it were stored within the Access file—you can establish relationships between the external data and existing table objects in your database, and you can create new queries to extract combinations of data from related tables.

Links are always the preferred option when you have multiple database programs maintaining identical data stores. For example, suppose that your office uses an accounting program that stores data about customers and suppliers in dBASE IV file format. Your accounting manager doesn't plan to switch from the dBASE application, because it suits the department's needs. You want to build a contact-tracking database in Access, and the information about suppliers and customers is a key part of it. If you import the information into Access, you will have no way of knowing when a record has been updated in the accounting program, and as time goes on, the two sets of records will develop more and more inconsis- tencies.

Instead, create a link to the Suppliers table; you can use the data the table contains in queries involving other tables stored directly in your Access database. To create the link, choose File, Get External Data, Link Tables. In the Files of Type list at the bottom of the Link dialog box, choose dBASE IV. Browse to the location where the external database file is stored, select its name, and click Link. If the dBASE file includes an index file, choose its name when prompted; if no index file exists, click Cancel to continue. Click OK when Access notifies you that the import was successful, and then close the Import dialog box.

The linked file appears in the database window, alongside other table objects, with an arrow to the left that lets you know it's a linked file and is not stored in the database itself. The icon is also different, depending on the original format of the data file. In general, you can view, edit, add, and delete data in a linked file, just as if it were stored locally.