Append Query causes key violation error 1

[Guest_imported]

Hello, I am trying to use an append query to append records to a table called "visit". However, Access gave me a key violation error. I went ahead and made a copy of the "visit" table and run the append query on the copy. It works fine. Both tables have the same format and data. The only differnce between the two is that the copy doesn't have any relatioship w/ other tables.

What do you guys think the cause is? Could the problem be possibly solve by setting cascade relationship ( I have no idea how to do that)? OR I have to configure the referential integrity settings in the SQL server..Please help me out, I am puzzled. B-(

 

[Robertd]

Hi 8384,

You may have answered the question when you tested the append query on the copy of the table.

if you are appending to the many or dependent side of a relationship the master side of the relationship must have the values (records) in its table.

cascade update/delete preform a different roll,
cascade update will change the child's tables key if the master tables key is changed but it must already exist.
eg if the master table had a record with the key field of
XY123, also there existed records in a child table that had this forien key XY123. and the master table's record for this entry was changed to XYZ123 then access would automatically update the records in the child table(s)
cascade delete allows access to delete all child table records if the master tables record is deleted.

both cascade update and delete are set when the relationships are defined and are optional.

So back to your problem the records you are adding need to have the forien key value and the table keys value in the append query AND the forien key must exist in the master table.

two options
append any new records to the master table first then append to the child table

if the key values already exist in the master table then you need to add these in the append query.

HTH


Robert Dwyer
rdwyer@orion-online.com.au

 

[Quehay]

Robert has it right. How do you reference the "one" table in the outlying records? If there is no reference at all make a table of your records and add a lookup field that references the correct "one" table. Then go through and add the correct reference. If the reference is there, but with a different numeric key or merely a text entry (when your destination table has a numeric key) then insert the same type of lookup field adjacent to the existing foreign key field. You can then cruise through the table in sheet view making the lookup (which will insert the numeric index via displayed text value in the combo) match the existing foreign key field. You can then delete the original field (or keep it as a reference and add a matching field to the destination table) and dump the records in without having the referential integrity violation that was stumping you previously.