KV error's on Updating serial numbers

[DataMiner]

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.

 

[supercharge]

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?

 

[DataMiner]

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.

 

[DataMiner]

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.

 

[DataMiner]

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.

 

[neileg]

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.

 

[DataMiner]

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?

 

[neileg]

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.