Update query - key violations?

[wvSusie]

I've been running an update query for months without issue. Today I started getting key violation errors and it will not perform the update.

UPDATE tblExHist RIGHT JOIN tmpEXCPTDWN ON tblExHist.ExHistID = tmpEXCPTDWN.ExHistID SET tblExHist.ExHistID = tmpEXCPTDWN!ExHistID, tblExHist.ExDate = tmpEXCPTDWN!ExDate, tblExHist.Type = tmpEXCPTDWN!Type, tblExHist.ChrgGtwy = tmpEXCPTDWN!ChrgGtwy, tblExHist.Code = tmpEXCPTDWN!Code, tblExHist.Discrepancy = tmpEXCPTDWN!Discrepancy, tblExHist.ActionTaken = tmpEXCPTDWN!ActionTaken
WHERE (((tmpEXCPTDWN.ExDate)>=[Forms]![frmUpdEx_Comp]![txtUpdBeginDate] And (tmpEXCPTDWN.ExDate)<=[Forms]![frmUpdEx_Comp]![txtUpdEndDate]));


tmpEXCPTDWN is a temp table which has the most current data for the records. The query is supposed to add new and update existing records in tblExHist. ExHistID is PK in both tables (it is a text field); there are no duplicate records. Currently it's adding new records, but it is not updating the existing records due to key violation. What could be the reason?

 

[lameid]

I would begin by looking at the indexes on tblExHist.

You might also consider deleting one column from your query at a time to see which column is the problem.

Another thought, the temp table could have corrupt data in it... you might compact / repaire but that might not fix it if this happens to be the case.

 

[JonFer]

Why are you using a Right join instead of an Inner Join for the update? Since you're joining on ExHistID, why are you including it in your fields to update?

And as lameid wrote, check for other unique indexes.

 

[PHV]

Jonfer, the right join is used to update existing records and add new records.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wvSusie]

I've determined the issue has to do with a one-to-many relationship between tblExHist and tblCompData. If I select Enforce Referetial Integrity to Cascade Delete Related Records, I get a key violation for each record in tblExHist where there's a related record in tblCompData.

Any ideas?

 

[lameid]

Sounds like the related key value in tblExHist is being modified.

What is the direction of the the one to many relationship and what is fields are involved in the relationship?

 

[wvSusie]

Hi lameid!

tblExHist is the one side; tblCompData is the many.

ExHistID is PK in tblExHist and is the field in tblCompData that the relationship is set on.

A few other fields have indexes but they all allow for duplicates, and there are no multi-field indexes.

I turned on Referential Integrity so records in tblCompData would be deleted if the related records are deleted from tblExHist. I don't understand why this would prevent adding new records or updating existing records in tblExHist.

If I de-select Enforce Ref Int, everything works fine. Obviously, I am missing something.

 

[lameid]

Technically you are assigning the related field SET tblExHist.ExHistID in your query...

[red]SET tblExHist.ExHistID = tmpEXCPTDWN!ExHistID[/red]

My gut says this should work but there is obviously something Access doesn't like...

Two thoughts come to mind. Are both sides of that expression above the same datatype? I SUSPECT it could balk at an implicit datatype conversion.

Secondly, the preferred syntax in queries is tablename.field not tablename!field (. vs !). I've seen the exclamation point often enough to know it generally works but I never use it in queries.

If neither of those solve it, you could always run an update with an inner join and an Append / Insert second for the missing records (Sequence is important or you end up updating records you just inserted).