KV error's on Updating serial numbers (1 Viewer)

Hi,
I have a table that uses SerialNo as primary key. From time to time (its a long story...) I need to go in and update the SerialNo by adding x to it. Here's the problem: lets say the table has SerialNo's 1,2,3,4,5, and I try to add 1. I get 4 KV error's because of course, 2,3,4,5 already exist in the table. (So I would end up with 1,2,3,4,6 instead of 2,3,4,5,6.)

I really thought that Access should be smart enough to figure out that there really won't be any key violations after the updates are done, but I guess not.

So, what's the easiest way to deal with this?

Thanks for any suggestions.

Don't get it!

Add 1 more serial number or 1 to the existing numbers?

1,2,3,4,5 + 1 =? 2,3,4,5,6 or 1,2,3,4,5 + 1 =? 1,2,3,4,5,6?

add 1 to the existing numbers. That is, change 1,2,3,4,5 to 2,3,4,5,6.

I guess I can temporarily turn off my primary key, run the query, and then turn the primary key back on.

This is a really scary process and I don't even want to know why in the world you would ever do such a thing to a serial number since a serial number is normally used to UNIQUELY identify something and therefore should have some permanence .

I have had to do this to allow a user to renumber items that needed to be kept in a specific order (additions to a chemical process). The key is to do the renumbering backward rather than forward. Use a DCount() to find the number of rows in the set and thereby determine the highest sequence value. Then change the last item to the highest value and loop backwards though the list.

This method is not without dangers. It will also fail if there is an existing value that is higher than the newly determined highest value. You can sort of get around this by doing a DMax() also and using the results of the two domain functions to determine the highest value. This will leave gaps at the beginning. If you don't want this, you can take a second pass, this time in forward order since you know exactly where the gap is and you will always be lowering the key value.

Thanks Pat, that sounds do-able.

I would certainly like my serialno's to be permanent and unchanging, but am having to deal with and repair errors in data sent to me.

I ended up solving this problem by
1. using data definition query to temporarily remove the primary key
2. Run the update query
3. Use data definition query to re-add the primary key.

Seems to work fine. I'm sure that Pat's method would work fine too; I have never used data definition queries before so wanted to try this as a learning experience.

Why not use a different PK? If you used an autonumber you wouldn't be doing something quite as scary. I guess you'll still want to have the serial number indexed, no dupes, but manipulating that property isn't as bad as the PK.

Can't see where that would be any easier. Using data definition query to turn "primary" off and on requires the same effort as turning "unique" off and on... am I missing something?

I suppose, if you don't have any other tables, it won't make much difference. If you had other tables related on the PK, it would make life a lot easier because you would only be updating one table.