Autonumber Data type

[ozzii]

What is the max number autonumber will count to? Can it automatically re-use previous numbers which have been deleted once it has reached the max number?

[ADezii]

What is the max number autonumber will count to? Can it automatically re-use previous numbers which have been deleted once it has reached the max number?

Since an AutoNumber Field is essentially a Long Integer Data Type, and if the old neurons are still working, I think the maximum value for a Long Integer is 2,147,483,647. To the best of my knowledge, numbers which have been deleted can never be reclaimed.

[ozzii]

Since an AutoNumber Field is essentially a Long Integer Data Type, and if the old neurons are still working, I think the maximum value for a Long Integer is 2,147,483,647. To the best of my knowledge, numbers which have been deleted can never be reclaimed.

I have a database in which I am using autonumber to assign unique identifiers to records. However the database requires the insert and delete of approx 10000 records daily. If i am inserting and deleting this many records daily, one can appreciate I will run out of this autonumber identifier very quickly. Is there a way round this so that I can automatically generate i unique identifier for each record using autonumber without the possibility of running out of autonumbers to use?

[MMcCarthy]

I have a database in which I am using autonumber to assign unique identifiers to records. However the database requires the insert and delete of approx 10000 records daily. If i am inserting and deleting this many records daily, one can appreciate I will run out of this autonumber identifier very quickly. Is there a way round this so that I can automatically generate i unique identifier for each record using autonumber without the possibility of running out of autonumbers to use?

If you compact and repair the database each time after deleting the records the numbers will be reset. However, this operation will close and then reopen the database.

[NeoPa]

I have a database in which I am using autonumber to assign unique identifiers to records. However the database requires the insert and delete of approx 10000 records daily. If i am inserting and deleting this many records daily, one can appreciate I will run out of this autonumber identifier very quickly. Is there a way round this so that I can automatically generate i unique identifier for each record using autonumber without the possibility of running out of autonumbers to use?

At that rate, just using very rough arithmetic, you have over 200,000 days or nearly 600 years. You could try resetting it every 500 years for safety ;)

[cyberdwarf]

ozzii

Alternatively, you could use a UniqueIdentifier data type...

HTH

Steve

[ozzii]

If you compact and repair the database each time after deleting the records the numbers will be reset. However, this operation will close and then reopen the database.

Is there a way to compact and repair the database using asp code? Sample code would be appreciated.

[MMcCarthy]

Is there a way to compact and repair the database using asp code? Sample code would be appreciated.

I'm afraid you'll probably have to check in with the ASP forum on that one ozzii.

[Killer42]

At that rate, just using very rough arithmetic, you have over 200,000 days or nearly 600 years. You could try resetting it every 500 years for safety ;)

Personally, I would consider such a limit much too restrictive. Not because of the 600 years, but because it doesn't allow enough slack for increases in the rate of allocation.

If some business change meant that you now have to load a thousand times as many records per day, suddenly you've only got enough unique numbers for 7 months. What if the increase was ten thousand times? Then you've only got 20 days or so to solve the problem.

Now, I realise this scenario may be unlikely, and would certainly involve other considerations (storage space, processing time, etc) but I always recommend leaving lots of room for expansion, so that at least it's one less thing to worry about. (Or to put it another way, "no more Y2Ks please":))

If we are talking about Access, then perhaps one could use a "Replication ID" Autonumber rather than the usual Long Integer type. I don't know much about it, but it is 16 bytes long, so may be worth investigating.

[NeoPa]

As someone who never contributed to the Y2K problem (and I was writing code before the Eighties started and was instructed to ignore the problem on numerous occasions), I can happily disagree with you on at least the emphasis of your point. If the project changes that drastically then whoever implements the change is in a position to redesign the solution where necessary. I agree that too precise planning without allowance for any naturally occurring changes to the parameters is not good design, but I think in this case that this is OTT. I can happily accept that there may be instances where the size of the Long Integer AutoNumber field is too restricting, but not that this is one of those instances.

[Killer42]

As someone who never contributed to the Y2K problem (and I was writing code before the Eighties started and was instructed to ignore the problem on numerous occasions), I can happily disagree with you on at least the emphasis of your point. If the project changes that drastically then whoever implements the change is in a position to redesign the solution where necessary. I agree that too precise planning without allowance for any naturally occurring changes to the parameters is not good design, but I think in this case that this is OTT. I can happily accept that there may be instances where the size of the Long Integer AutoNumber field is too restricting, but not that this is one of those instances.

Well, I suppose everyone's entitled to their opinion... ;)

At work a while back, I deliberately set up a file with an "autonumber equivalent" field that allowed us something like 150,000 years worth of allocations. :) (However, that's because we knew that the rate of record creation was going to accelerate rapidly.)