ACC: Import Errors Table Not Created During Failed Import

This article was previously published under Q171852
This article has been archived. It is offered "as is" and will no longer be updated.
Novice: Requires knowledge of the user interface on single-user computers.

Symptoms
Microsoft Access 7.0 and 97 do not create an Import Errors table after animport of a text file or spreadsheet fails.
Cause
The circumstances under which Microsoft Access 7.0 and 97 create an ImportErrors table have changed from earlier versions of Microsoft Access.Microsoft Access 7.0 and 97 create an Import Errors table only in thefollowing situations:
  • The data in a field is inappropriate for the data type of the destination field. For example, the data type of the destination field is Numeric, but the data contains a Text value.
  • The data in a Numeric field is too large for the field size of the destination field. For example, the destination field has a FieldSize property set to Byte, but the data contains a value greater than 255.
  • The data you're importing contains a Null value that you attempt to append to an AutoNumber field (Microsoft Access 97 only).
  • The table to which you are appending the data contains replication fields.
Status
This behavior is by design.
More information
The Import Errors table contains descriptions of the errors MicrosoftAccess encounters when trying to import a text file or spreadsheet. Thetable includes field names and row numbers that indicate which data hascaused errors.

Versions of Microsoft Access earlier than Microsoft Access 7.0 create anImport Errors table in the following situations:
  • The data in a field you're importing is inappropriate for the data type of the destination field. For example, the destination field is a numeric data type, but the data contains a text value.
  • The data in a numeric field is too large for the field size of the destination field. For example, the destination field has a FieldSize property set to Byte, but the data contains a value greater than 255.
  • Records you are importing contain duplicate values that would be stored in the primary key of the destination table, or in any field in the table that has the Indexed property set to Yes (No Duplicates).
  • The data you're importing violates a field or table validation rule.
  • Records you are importing contain Null values for a field whose Required property is set to Yes.
  • The data contains records that cannot be parsed by Microsoft Access. This can occur when you import a text value that contains the text delimiter character (usually quotation marks) within the data. For example, the Import Errors table would contain an error entry for the following text data because it contains a quotation mark:
          "10 - 3 1/2" disks/box"						

Steps to Reproduce Behavior

The following example attempts to import a record that violatesreferential integrity into the Order Details table in the sampledatabase Northwind.mdb.
  1. Open any text editor, such as Notepad, and create the following new text file:
           99999,54,7.45,20,0.000						
  2. Save the text file as C:\My Documents\OrdDetails.txt.
  3. Start Microsoft Access, and open the sample database Northwind.mdb.
  4. On the File menu, point to Get External Data, and then click Import.
  5. In the Import dialog box, select Text Files in the Files Of Type box.
  6. Locate the folder C:\My Documents, select OrdDetails.txt, and click Import.
  7. On the first screen of the Import Text Wizard, select the Delimited option, and then click Next.
  8. On the second screen of the Import Text Wizard, click Next.
  9. On the third screen of the Import Text Wizard, select the "In an Existing Table" option, and select the Order Details table from the list.
  10. Click Finish. Note that you receive the following error message:
    Microsoft Access was unable to append all the data to the table.

    The contents of fields in 0 record(s) were deleted, and 1 record(s) were lost due to key violations.

    *If data was deleted, the data you pasted or imported doesn't match the field data types or the FieldSize property in the destination table.

    *If records were lost, either the records you pasted contain primary key values that already exist in the destination table, or they violate referential integrity rules for a relationship defined between tables. Do you want to proceed anyway?
  11. Click Yes. Note that Microsoft Access does not create an Import Errors table.
References
For more information about the Import Errors table, search the Help Indexfor "Import Errors table."
Properties

Article ID: 171852 - Last Review: 11/02/2013 00:01:00 - Revision: 4.0

  • Microsoft Access 97 Standard Edition
  • kbnosurvey kbarchive kbprb kbusage KB171852
Feedback