ACC2000: Error Message: Microsoft Access Can't Append All the Records in the Append Query

View products that this article applies to.

Symptoms

When you run an append query, you may receive the following error message:

Microsoft Access can't append all the records in the append query.

Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn't add 0 record(s) to the table due to key violations, 0 record(s) due to lock violations, and (X) record(s) due to validation rule violations.
Do you want to run the action query anyway?
To ignore the error(s) and run the query, click Yes.
For an explanation of the causes of the violations, click Help.

↑ Back to the top

Cause

This message occurs when you try to insert empty strings into a field that has its AllowZeroLength property set to No.

↑ Back to the top

Resolution

Open your destination table in Design view.
Set the AllowZeroLength property of all text fields to Yes.
Set the Required property of all these text fields to No.
Save the changes to the table, and then close the table.
Run your append query.

↑ Back to the top

Status

This behavior is by design.

↑ Back to the top

More information

Steps to Reproduce the Behavior

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Open the sample database Northwind.mdb.
Click Tables under Objects.
Click the Employees table.
On the Edit menu, click Copy.
On the Edit menu, click Paste.
In the Paste Table As dialog box, name the new table Test, click Structure Only under Paste Options, and then click OK.
Open the Test table in Design view, and then set the following properties for the FirstName field:
Required = No
Allow Zero Length = Yes
Open the new Test table, enter some new records, but leave the FirstName field blank in some records, and then close the table.
Click Queries under Objects.
Click New.
In the New Query dialog box, click Design View, and then click OK.
In the Show Table dialog box, click the Test table, click Add, and then click Close.
Add the FirstName and LastName fields to the query design grid.
On the Query menu, click Append Query.
In the Append dialog box, click the arrow in the Table Name box, click the Employees table in the list, and then click OK.
On the Query menu, click Run.
Click Yes to the message that you are about to append data to your table.

Note that you receive the error message that is mentioned in the "Symptoms" section of this article.

↑ Back to the top

References

For more information about the AllowZeroLength property, click Microsoft Help on the Help menu, type allowserolength in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

↑ Back to the top