CODEWITHSUNDEEP
sql-updatems-access-2010
autumntiger
autumntiger

Reputation: 105

Update Query Will Not Work Due To Key Violations

I am trying to update a specific field in the "Claims" table of my 2010 Access Database. I keep receiving an error message that says there are key violations. Here is the SQL:

UPDATE Claims SET Claims.LS_Name = "JPN" WHERE (((Claims.Responsibility2)=0));

Is there any reason, based on the above code, that it is not working?

Thanks in advance!

Upvotes: 1

Views: 3446

Answers (2)

sweetpeashub
sweetpeashub

Reputation: 1

OK, I ran into this issue as well in Microsoft Access and think I have ways to solve versions of this. The intermediate table may not be necessary. My experience is that autonumber is the issue so conversion to number seems to work, but you have to delete relationships for Access to allow this change.

  1. remove all relationships to the destination table.
  2. change from autonumber to number.
  3. make new empty tablethatincrements starting with the next higher key https://superuser.com/questions/288087/how-do-i-set-the-first-value-of-autonumber-in-access
  4. update/query to the tablethatincrements table instead of destination.
  5. update/merge from tablethatincrementsto to the destination table, including new higher keys.
  6. remake relationships

OR In certain situations. (for mine the new keys matched the old) something like this (may not be exact steps)

  1. remove all relationships to the destination table.
  2. delete ID/primary key in destination
  3. merge/update to destination
  4. create new ID/Primary key in destination (so it can be autonumber to renumber keys)
  5. remake relationships

Anyway my next research item is to see how to never use autoincrement and do the unique keys using other methods so relationships do not have to be removed to change from autoincrement to number.

Upvotes: 0

wumpz
wumpz

Reputation: 9201

My first guess would be that there is a unique key on Claims.LS_Name and your update hits more than one row.

Upvotes: 1

Related Questions