By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,107 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

Autonumber Data type

P: 37
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?
Jan 17 '07 #1
Share this Question
Share on Google+
10 Replies


ADezii
Expert 5K+
P: 8,597
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.
Jan 18 '07 #2

P: 37
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?
Jan 18 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jan 18 '07 #4

NeoPa
Expert Mod 15k+
P: 31,186
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 ;)
Jan 18 '07 #5

Expert 100+
P: 218
ozzii

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

HTH

Steve
Jan 18 '07 #6

P: 37
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.
Jan 18 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jan 18 '07 #8

Expert 5K+
P: 8,435
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.
Jan 19 '07 #9

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 19 '07 #10

Expert 5K+
P: 8,435
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.)
Jan 19 '07 #11

Post your reply

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