Microsoft Access Tips for Casual Users

Provided by Allen Browne, June 2006.  Last updated: April 2010.

Why can't I append some records?

When you execute an append query, you may see a dialog giving reasons why some records were not inserted:

Error screenshot

The dialog addresses four problem areas. This article explains each one, and how to solve them.

Type conversion failure

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:
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.

Key violations

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

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.

Validation rule violations

There are several places to look to solve for this one:

If none of these apply, double-check the key violations above.

Still stuck?

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.

HomeIndex of tipsTop