Provided by Allen Browne, June 2006. Last updated: April 2010.
When you execute an append query, you may see a dialog giving reasons why some records were not inserted:
The dialog addresses four problem areas. This article explains each one, and how to solve them.
Access is having trouble putting the data into the fields because the field type does not match.
For example, if you have a Number or Date field, and the data you are importing contains:
-
Unknown
N/A
these are not valid numbers or dates, so produce a "type conversion" error.
In practice, Access has problems with any data that is is not in pure format. If the numbers have a Dollar sign at the front or contain commas or spaces between the thousands, the import can fail. Similarly, dates that are not in the standard US format are likely to fail.
Sometimes you can work around these issues by importing the data into a table that has all Text type fields, and then typecasting the fields, using Val(), CVDate(), or reconstructing the dates with Left(), Mid(), Right(), and DateSerial(). For more on typecasting, see Calculated fields misinterpreted.
The primary key must have a unique value. If you try to import a record where the primary key value is 9, and you already have a record where the primary key is 9, the import fails due to a violation of the primary key.
You can also violate a foreign key. For example, if you have a field that indicates which category a record belongs to, you will have created a table of categories, and established a relationship so only valid categories are allowed in this field. If the record you are importing has an invalid category, you have a violation of the foreign key.
You may have other unique indexes in your table as well. For example, an enrolment table might have a StudentID field (who is enrolled) and a ClassID field (what class they enrolled in), and you might create a unique index on the combination of StudentID + ClassID so you cannot have the same student enrolled twice in the one class. Now if the data you are importing has an existing combination of Student and Class, the import will fail with a violation of this unique index.
Lock violations occur when the data you are trying to import is already in use.
To solve this issue, make sure no other users have this database open, and close all other tables, queries, forms, and reports.
If the problem persists, Make sure you have set Default Record Locking to "No Locks" under File (Office Button) | Options | Advanced (Access 2007 or later), or in earlier versions: Tools | Options | Advanced.
There are several places to look to solve for this one:
There is something in the Validation Rule of one of the fields, and the data you are trying to add does not meet this rule. The Validation Rule of each field is in the lower pane of table design window.
There is something in the Validation Rule of the table, and the data you are trying to add does not meet this rule. The Validation Rule of the table is in the Properties box.
The field has the Required property set to Yes, but the data has no value for that field.
The field has the Allow Zero Length property set to No (as it should), but the data contains zero-length-strings instead of nulls.
If none of these apply, double-check the key violations above.
If the problem data is not obvious, you might consider clicking Yes in the dialog shown at the beginning of this article. Access will create a table named Paste Errors or Import Errors or similar. Examining the specific records that failed should help to identify what went wrong.
After fixing the problems, you can then import the failed records, or restore a backup of the database and run the complete import again.
| Home | Index of tips | Top |