Microsoft Office Tutorials and References
In Depth Information
Importing or Linking to Data
Getting external data
After you decide whether to import or link to your data, you’re ready to take
the next step. If you can, look at the external data you want to use. Look for
the following factors:
✦ Are fields stored in columns and records in rows? This structure is
relevant to text and spreadsheet files, which sometimes separate data with
commas or other symbols.
✦ Does the data you need begin at the top of the file? For text and
spreadsheets, Access expects to see one row of names and then the
data. If your data isn’t set up this way, you need to edit it.
✦ Is all data within a field of the same type? If not, the field is imported
as a Text field, which can’t be used in mathematical equations.
✦ Is the number of fields in each row the same? This question is of
particular concern in a text file. If necessary, add null values to make your
data line up. Two quotation marks with nothing between them (““), for
example, represent a null text value.
✦ Are the field names in the data you’re importing identical to the field
names in the Access table? When you append data (that is, add data to
an existing Access table), the field names in your source data must be
identical to the field names in the Access table you’re appending to.
Are you importing the data into a new table, or do you want to append the
data to an existing table? Appending can be tricky because the data in the
external source and in the Access table have to match in terms of data type
and relative location. You may want to import the data into a new table in
Access first and then use an append query. (For more on the append query,
see Book III, Chapter 3.)
When your data source is cleaned up, you’re ready to import or link.
Following are general instructions, with some particulars for specific file
1. Open the database that you want to add external data to.
2. Click the button for the kind of data that you’re importing on the
External Data tab of the Ribbon.
The External Data tab contains buttons for Excel, Access, ODBC
databases, text files, and XML files. The More button’s drop-down menu
contains the options SharePoint List, Data Service (for web services), HTML
Document, and Outlook Folder.
When you’ve made your choice, Access displays the Get External Data
dialog box (shown in Figure 4-3), where you specify the name of the file
that contains the data you’re importing or linking to.