How do I fix this?

[enak]

I really need some help with this. I have a query that runs just fine except that it won't copy three records. I have checked the data and it seem to be in the same format as the data that does get copied.

Here is the error message that I get:

"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 3 record(s) to the table due to key violation, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.

Do you want to runt the actions query anyway?
To ignore the error(s) and run the query, click Yes.
For an explanation of the causes of the violations, click Help."


I have inherited this database and do not know how to check the items listed in the error message.

Can someone please help?

Thanks,
enak

 

[KenReay]

Hi

Check the table definition of the table you are adding to, looking for columns with unique indexes on them (including of course the primary key).

Problem is you have duplicate values in the data you are trying to append

you must either remove the offending duplicates, or remove the unique indexes

one or the other it is your choice

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[enak]

Thanks for your response. I checked the table definition and only found one field that was indexed and did not allow duplicates. There were some others that were indexed but allowed duplicates.

I queried the target table to see if the records existed based on the indexed (No Duplicates) field and came up empty.

Do you have any other suggestions?

 

[KenReay]

Hi

Use the query design wizard to make a query checking for duplicates on the relevant field, if Access/jet says there are duplictes then there are duplicates.



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[enak]

I used the unmatched query and it showed me the 3 records that I am having trouble copying to the target table. Because of this I can only conclude that these records are duplicated in the target table.

The key field (CaseID) is an autonumber and I am inserting the same value from the source table. However, it works fine on the other records.