Need Help Now!! AutoNumber & reseting the start Number

[NightZEN]

Hi Everyone, thanks for viewing...

I recently split a database using the wizard. One of my Tables has an auto-number field for the key. I had changed it to an Auto-Number from a text field some time ago using an append query. When I split, the Autonumber field was at 24222. Now it gives me 26112 as the next number.

I copied the structure of the table to a new table.
erased the the 26112 record in the source table.
appended all records from source table to new table.
when I add a new record to the new table, I get 26113. I expected 24223.

What am I missing?! please help me (people are getting crabby - first I split, then I take away their numbers, what a pain)

thanks!

 

[KenReay]

Hi

To restart the autonumber from 1, delete all rows in the table and compact/repair the database.

But I suppose the real question is why would you want to do this? Autonumbers are meant to be usnique identifiers, ther are not meant to have sequence, or meaning, indeed most Developers would hide them from users

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[NightZEN]

The numbers are given to Engineering Change Notice (ECN) packages - and I want them to continue at 24223, not restart at 1. my problem is that after splitting the database, the autonumber field sequence has jumped to 26112 from 24222. Why? And is there a way back?

Thanks

 

[KenReay]

I repaet, you cannot rely on autoneumbers to be sequential sequence, theer are several reasons why gaps may appear.

See the FAQ section, there is a FAQ on generating a sequential number to idetify records (eg Order Number, Change Requests etc)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[GingerR]

I agree with Ken. you will run into similar problems your whole life. Autonumber is not meant for us to use like you are.

 

[NightZEN]

Thanks for your warnings. I am developing another solution now.

best regards ...