How can I get AutoNumber not to Reset?

[ryanhi11]

I have searched through several posts on this forum and have continually found information on resetting an AutoNumber value. My question is the opposite of this.

I have recently created an archive utility that extracts data from the main table (from the beginning of the data through a user-defined date) and adds the old data to an archived main table. The problem I am having is that after I extract the old data the AutoNumber gets screwed up and starts over (even though there will always be data left in the main table that holds where the AutoNumber previously left off). This will cause a problem, because when the data is archived again there will be duplicate IDs. Is there some way to control the AutoNumber's value, or should I just give up on AutoNumber and create the ID manually?

Ryan

 

[KenReay]

Hi

Cannot understand why Autonumber should reset unless the table is empty AND you do a compact,

but since it apparently is doing so,

have you considered changing you autonumber field to be a replicationid instead of a long.

Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[ryanhi11]

Thanks for your reply. The table isn't empty, but I do a compact and repair prior to adding data. It is odd because it is not really starting over. The table consists of approximatley 70,000 records and I tested it by removing the first 40,000 - 50,000 records. The autonumber seemed to start at random numbers between 15,000 - 30,000 and then increments consistantly by 1. I'll try changing it to a replicationID and see if that does it.

Thanks,
Ryan

 

[ian0001]

Hi Ryan

I stay away from the AutoNumber because of it's undocumented feature of incrementing with out reason every blue moon or so. Finding out that there is another unusuality (new word?) is not surprising.

 

[JeremyNYC]

Ryan,

Where have you seen the "undocumented feature" of Autonumbers. I've been using them since 1995 and I've never seen anything not explained in Microsoft documentation.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing and building Access databases since 1995.

 

[ian0001]

One client as a DB on a Win98 (everything standard ). Uses ID number to match hardcopy booking numbers. Now for some reason every so often (3 times last year) the autonumber would increment by two & throw the number system out of sync. Which meant I had to go and re-load the tables etc - not fun.
I just do it all manually (instead of autonumber) now - better control & no pain in the rear call outs.
I admit the same code never misses a beat elsewhere but why take the risk?

 

[JeremyNYC]

Ian,

Hmm. That's actually a fairly well documented thing. MS always says that autonumbers are never to be relied on to be sequential. Any time you start to add a record and then bail you'll increment the autonumber. Autonumber are not to be relied on to convey any information other than recortd identification.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.