Append query fails but copy/paste works

[Guest]

I am running an append query to copy rows from one table to another. My
source table has 230 rows in it. When I run the append query I am told that
171 rows will not be copied because of validation rule violations. However,
if I open the append query in datasheet view to preview the records that
should be appended, I can copy all 230 records and paste them into the
destination table with no problem. This is giving me a real headache as I
need to run the append query from code.

Any ideas why the query is failing to append all the rows?

 

[Guest]

Good old Google - should have checked there first.

Destination table has columns set not to allow zero length strings.

 

[Guest]

Thanks Bob. Maybe you missed that I had already responded myself to say I had
found the problem. The destination table had some fields defined with 'Allow
zero length' set to No. My source table had some rows with zero length
strings in those fields. When you append via a query, Access sees the zero
length strings and complains. However if you copy and paste, for some reason
Access treats the zero length strings as Nulls, so it works.