Change ID numbering sequence

[buncey]

Hi

Is there a method of changing the auto numbering sequence whilst retaining the exisiting records along with thier original ID numbers?

What I am trying to say is, I have a table with records numbered 100000 to 100999 and I need to change the starting number to 200000 so the next set of records within the same table can be clearly seperated by thier starting number.

So, the last record entered will be numbered 100999 and the next new record would be 200000 whilst retaining the numbering structure of the original records.

[missinglinq]

First question would be how are you accomplishing this "auto-numbering?" With an Access generated AutoNumber or with a custom coded generation routine?

Welcome to Bytes!

Linq ;0)>

[NeoPa]

If you can specify logically what criteria would be used to determine how this should work, then it can be done. It would not be using AutoNumber fields though. It would not be automatically assigned. You'd need to introduce the assignment logic yourself in a form - with VBA code.

[OldBirdman]

I did this in Access 2003 once by mistake. I was entering data directly into a table (creating test data), and somehow entered some data into the autonum field. Access accepted it and continued counting from that point, so I had an autonum field with 1,2,3,4,1062,1063,1064,...
I was never able to recreate this, as Access won't let me enter data into an autonum field. But is is somehow do-able.

You could append 99000 dummy records to your table, add a record, and delete the 99000 dummy records.

If you need to group records, you need another field in your table to do this. The Access autonum should be treated as if it were a random number (which is an option), and has no significance. If you use the random autonumbers, there is no entry order maintained, no holes when records are deleted. It promotes better programming because the programmer makes no assumptions about this field.

The autonum as a Primary Key serves 2 purposes: 1) It makes Access happy, and 2) it gives a field to link related data together. Any other use should be avoided.

[NeoPa]

@OldBirdman
Absolutely. I concur fully.