Part III: More-Advanced Access Techniques
582
There are distinct differences among the three methods:l Linking uses the data in its current file format (such as Excel or FoxPro). The link to
data remains in its original file. The file containing the link data should not be moved,
deleted, or renamed. Otherwise, Access won’t be able to locate the data the next time it’s
needed. If moving or renaming the linked data source is unavoidable, Access provides
tools for relinking to the source.l (^) Importing makes a copy of the external data and brings the copy into the Access
database. The imported data is converted to the appropriate Access data type, stored in a
table, and managed by Access from that point on.
l Exporting makes a copy of the data in the Access database and puts the copy into
the file format of another application. The exported data can be used by the other appli-
cation from that point on.
Each method has clear advantages and disadvantages, covered in the following sections.
When to link to external data
Linking in Access enables you to work with the data in another application’s format — thus, shar-
ing the file with the existing application. If you leave data in another database format, Access can
read the data while the original application is still using it. This capability is useful when you want
to work with data in Access that other programs also need to work with. However, there are limita-
tions as to what you can do with linked data. For example, you can’t update data in a linked Excel
spreadsheet or a linked text file. The ability to work with external data is also useful when you use
Access as a front end for a SQL Server database — you can link to a SQL Server table and directly
update the data, without having to batch-upload it to SQL Server.
Access databases are often linked to external data so that people can use Access forms to add and
update the external data, or to use the external data in Access reports.
You can link to the following types of data in Access:
l (^) Other Access tables (.accdb, .accde, .accdr, .mdb, .mda, .mde)
l Excel spreadsheets
l (^) Exchange documents
l Outlook documents
l (^) FoxPro
l dBASE
l (^) Text files
l HTML documents
l (^) SharePoint Team Services
l ODBC databases