Chapter 16: Working with External Data
597
Text files are often used as intermediate data-transfer vehicles between dissimilar applications. For
example, there might be an obsolete data-management system in your environment that is incom-
patible with any of the link or import data types in Access. If you’re lucky, the obsolete system is
able to output either fixed-width or CSV files. Linking to or importing the fixed-width or CSV files
might be the best option for sharing data with the obsolete system. At the very least, much less
time is required linking or importing the data than would be involved in rekeying all the informa-
tion from the obsolete system into Access.Follow these steps to link to the Contacts text file:- Open the Chapter16.accdb database and select the External Data ribbon.
- Click on the Text File button.
The Get External Data dialog box appears.
- Be sure the Link to the Data Source by Creating a Link Table option is selected, and then
click Browse.
The File Open dialog box appears.
- Locate the text file (either Contacts_FixedWidth.txt or Contacts_CSV.txt)
and click Open. - Dismiss the other dialog boxes that appear.
You’ll be taken to the Link Text Wizard dialog box.
Generally speaking, Access makes a pretty good guess at how the data in the file is delim-
ited. Linking to text data involves nothing more than clicking Next and verifying that
Access has correctly identified the data in the file.
On the CD-ROM
Rather than show or describe each of the dialog boxes in the Link Text Wizard, link to Contacts_CSV.txt
and Contacts_FixedWidth.txt, both included on this book’s CD.
As you’ll see when you link to these files, about the only input required from you is to provide a name for each
of the fields Access finds in the text files. If you’re lucky, the text file includes field names as the first row in the
text file. Otherwise, linking to text files will likely require that you specify names for each field.
Working with Linked Tables
After you link to an external table from another database, you use it just as you would any another
Access table. You use linked tables with forms, reports, and queries just as you would native
Access tables. When working with external tables, you can modify many of their features (for
example, setting view properties and relationships, setting links between tables in queries, and
renaming the tables).One note on renaming linked tables: Providing a different name for the table inside of Access does not
change the name of the file that is linked to the application. The name that Access refers to a link table is
maintained within the Access application and doesn’t influence the physical table that’s linked.