ACC: Import Errors Table Not Created During Failed Import

• Microsoft Access versions 7.0, 97

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

Microsoft Access 7.0 and 97 do not create an Import Errors table after an import of a text file or spreadsheet fails.

CAUSE

The circumstances in which Microsoft Access 7.0 and 97 create an Import Errors table have changed from earlier versions of Microsoft Access. Microsoft Access 7.0 and 97 create an Import Errors table only in the following 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 Microsoft Access encounters when trying to import a text file or spreadsheet. The table includes field names and row numbers that indicate which data has caused errors.

Versions of Microsoft Access earlier than Microsoft Access 7.0 create an Import 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 violates referential integrity into the Order Details table in the sample database 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 Index for "Import Errors table".

Keywords          : IsmExl4 IsmExl5 IsmProb IsmTxtd IsmTxtfx kbusage
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbprb
Solution Type     : Info_Provided