Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 303414 - Last Review: January 31, 2007 - Revision: 3.1
ACC97: Error Message "Microsoft Access Can't Append All the Records in the Append Query"
This article was previously published under Q303414
Novice: Requires knowledge of the user interface on single-user computers.
On This Page
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 records to the table due to key violations, 0 records due to lock violations, and <x> records 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.
CAUSE
The message occurs when you try to insert empty strings into a field that has its
AllowZeroLength property set to
No.
RESOLUTION
- Open your destination table in Design view.
- Set the AllowZeroLength property of all text fields to Yes.
- Save the changes to the table, and then close the table.
- Run your append query.
STATUS
This behavior is by design.
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 Northwind sample database.
- Click the Tables tab.
- Click the Employees table, and then on the Edit menu, click Copy.
- On the Edit menu, click Paste.
- 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
- Add a few new records to the Test table, but leave the FirstName field blank in some records, and then close the table.
- Click the Queries tab.
- Click New, 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 grid.
- On the Query menu, click Append Query.
- In the Append To drop-down list, click the Employees table, and then click OK.
- On the Query menu, click Run. Click Yes to the alert that you are about to append records to your table.
- Note that you receive the error message that is mentioned in the "Symptoms" section of this article.
REFERENCES
For more information about the
AllowZeroLength Property, click
Contents and Index on the
Help menu, click the
Index tab in Help, type the following text
allowzerolength
and then double-click the selected text to go to the
"allowzerolength" topic. If you are unable to find the information you need, ask the Office Assistant.
APPLIES TO
- Microsoft Access 97 Standard Edition
Retired KB Content DisclaimerThis article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please