Chapter 16: Working with External Data
587
Figure 16.3 illustrates an important concept regarding using linked data in Access. Users will not
know, nor will they care, where the data resides. All they want is to see the data in a format they
expect. Only you, the developer, understand the issues involved in bringing this data to the user
interface. Other than the limitations of linked data (explained in the next section), users won’t be
able to tell the difference between native and linked data.Note
After you link an external table to an Access database, you should not move the source table to another drive
or directory. Access doesn’t bring the external data file into the .accdb file; it maintains the link via the file-
name and the file’s path. If you move the external table, you have to update the link using the Linked Table
Manager, explained in the “Viewing or changing information for linked tables” section, later in this chapter.
Limitations of linked data
Although this chapter describes using linked data as if it existed as native Access tables, certain
operations can’t be performed on linked data. Plus, the prohibited operations depend, to a certain
extent, on the type of data linked to Access.These limitations are relatively easy to understand. Linked data is never “owned” by Access.
External files that are linked to Access are managed by their respective applications. For example,
an Excel worksheet is managed by Microsoft Excel. It would be presumptive — and dangerous —
for Access to freely modify data in an Excel worksheet. For example, because many Excel opera-
tions depend on the relative positions of rows and columns in a worksheet, inserting a row into a
worksheet might break calculations and other operations performed by Excel on the data. Deleting
a row might distort a named range in the Excel worksheet, causing similar problems. Because there
is no practical way for Access to understand all the operations performed on an external data file
by its respective owner, Microsoft has chosen to take a very conservative route and not allow
Access to modify data that might cause problems for the data’s owner.The following list describes the limitations of linked data:l Excel data: Existing data in an Excel worksheet can’t be changed, nor can rows be deleted
or new rows be added to a worksheet. Excel data is essentially treated in a read-only fash-
ion by Access.l (^) Text files: For all practical purposes, data linked to text files is treated as read-only in
Access. Although the data can be used in forms and reports, you can’t simply and easily
update rows in a link text file, nor can you delete existing rows in a text file. Oddly
enough, you can add new rows to a text file; presumably, this is because new rows won’t
typically break existing operations the way that deleting or changing the contents of an
existing row might.
l (^) HTML: HTML data is treated exactly as Excel data. You can’t modify, delete, or add rows
to an HTML table.
l (^) dBASE: Because these are database files, you can pretty much perform the same data
operations on dBASE tables as you can on native Access tables. This general statement
applies only if a primary key is provided for each dBASE table.