470,815 Members | 1,324 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,815 developers. It's quick & easy.

Change ID numbering sequence


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.
Oct 12 '09 #1
4 5651
3,532 Expert 2GB
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)>
Oct 12 '09 #2
32,311 Expert Mod 16PB
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.
Oct 12 '09 #3
675 512MB
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.
Oct 16 '09 #4
32,311 Expert Mod 16PB
Absolutely. I concur fully.
Oct 16 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Sjaak van Esdonk | last post: by
5 posts views Thread by Charles McCaffery | last post: by
1 post views Thread by Wayne Aprato | last post: by
3 posts views Thread by Chris | last post: by
1 post views Thread by Marek Lewczuk | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.