Chapter 17: Importing and Exporting Data
633
Troubleshooting import errors
When you import an external file, Access might not be able to import one or more records, in
which case it reports an error when it tries to import them. When Access encounters errors, it cre-
ates an Access table named Import Errors (with the user’s name linked to the table name). The
Import Errors table contains one record for each record that causes an error. You can use this table
to determine which data caused the import issues.Open the Import Errors table and try to determine why Access couldn’t import all the records. If
the problem is with the external data, you might need to edit it. If you’re appending records to an
existing table, the problem might be with the existing table. The table might need modifications
(such as changing the data types and rearranging the field locations). After you solve the problem,
erase the Import Errors file and import the data again.Note
Access tries to import all records that don’t cause an error. If you re-import the data, you might need to clean
up the external table or the Access table before re-importing. If you don’t, you might have duplicate data in
your table.
Tip
If importing a text file seems to take an unexpectedly long time, it might be because of too many errors. You
can cancel importing by pressing Ctrl+Break.
In most cases the Import Errors table contains just a few rows. You might be able to manually
move the data from Import Errors to the destination table. If the Import Errors table contains
numerous rows, you’ll probably have to repair the input file (if possible) and repeat the import
process, or modify the destination table to accommodate the data provided by the import file.In more complex situations you might want to import the external data into a temporary table, and
then use a query or VBA code to move the data to its final destination. Using a temporary table as
an intermediate step is particularly helpful if the imported data is destined for more than one table
in Access.Import errors for new tables
An import error doesn’t imply a problem with the data. It simply means that Access didn’t understand
something about the incoming data and needs you to inspect the questionable data and take corrective
action. Access might not be able to import records into a new table for the following reasons:l (^) A row in a text file or spreadsheet may contain more fields than are present in the
first row.
l (^) Data in the field can’t be stored in the data type Access chose for the field. This could
be text in a numeric field — best case will import as zeros — or numeric trying to store in
a date field.