autonumber

[roddy17]

I am designing a database which has autonumber. This database imports hundreds of thousands of records every 3 months, and exports the previous 3 month's records at the same time and deletes it from the database. The autonumber for the new data starts where the autonumber of the old data left off, even if i do the delete operation before importing the new data.
My question is, there is ultimately a finite number for the autonumber to increment to. When autonumber gets to that point where it can go no higher, will it recycle?
That is, will it start at 0 again and go up by one again?
thankyou

 

[tlbroadbent]

Compact the database after deleting the records to reset the autonumber. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[jebry]

Hi!

Another consideration, at the rate of 500,000 records every 3 months, an autonumber field will good for over 2000 years!

Jeff Bridgham

P.S. Compacting your database on a regular basis is a very good idea for a variety of reasons. Just wanted you to know that I was not disagreeing with Terry!

 

[roddy17]

True enough - the autonum is gotta be a large number, and compacting the database should very well pe practiced.
I did not know that compacting a database had that effect on the autonum.
I thank both of you for your comments.

 

[MichaelRed]

Just to be SURE. Compacting the db will reset the autonumber IF AND ONLY IF the table is EMPTY at the time of compacting.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Aivars]

Hi!

I rejected autonumbers about three years ago when I had same problems as Roddy (data import/export). In lieu of it I use long integer field. Before insert I check for Max value and put new value of this field equal Max+1:

NewValue=iif(dcount(&quot;FieldId&quot;,&quot;MyTable&quot<>0,dmax(&quot;FieldId&quot;,&quot;MyTable&quot+1,1)

Aivars

 

[tlbroadbent]

Just to be MORE SURE. Compacting will reset the next autonumber to 1 if the table is empty or to the next higher number if the table is not empty.

Example:
If my table contains autonumbers up to 100 and I delete records with autonumbers above 80, compacting will reset auto number such that the next number inserted will be 81. However, if I add a record before compacting, the autonumber inserted will be 101. Compacting after that insert will not recover any auto numbers. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[MichaelRed]

Aviars,

You &quot;soloution&quot; MAY save the timing problem -at the expense of POSSIBLY duplicating the 'NewValue'. It will (or course) only occur in multiuser apps, and probably not often. It can occur, unless you add record level
(not 'available until ver 2K) locking to the app. It occurs more often with memo fields but it does occur unless the usage is trivial.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over