How do I avoid key violations in append queries

  • Thread starter Thread starter Pitaofauck
  • Start date Start date
P

Pitaofauck

I am using Access 2007 in Access 2002 - 2003 file format. When I run an
append query from a table with 129 records, to a table with 0 records. I get
the following message:
Microsoft Office Access set 0 field(s) to Null due to a type conversion
failure, and didn't add 100 record(s) to the table due to key violations, 0
record(s) due to lock violations, and 0 record(s) due to validation rule
violations.

If I agreee to run the query anyway it appends 29 records to the new table.
 
I expect you might have duplicates in a field or fields that make up a unique
index or primary key. If this is the case then change your append query to a
totals append query.
 
Sorry not sure how to change to totals append query from append query in
access 2007 to make it run?
thanks
sk
 
Sorry, I'm not sure what your table structures are or what you are attempting
to do.
 
Duane,

I am able to view the data in datasheet but fails when you run the append
query with the error below:Thanks
sk
 
This is generally a fairly accurate error message. There is either a primary
key or other constraint that isn't allowing for the appending of 6000 records.
 
Duane,

I am able to view the data in datasheet but fails when you run the append
query with the error below:
Thanks
sk

Please open the query in SQL view and post the SQL text here. Also indicate
what is the Primary Key of the table into which you are appending; from the
error it suggests that the table already contains these records, and they're
being rejected because they would cause a duplicate value in the primay key
field.
 
Back
Top