Whats the maximum number that the autonumber data type in microsoft access can count up to? Once it reaches its maximum number can it automaticaly re-use previous numbers which have been deleted?

The numbers available for autonumber (identity) are long integers. The range is -2,147,486,648 through 2,147,843,647. By default autonumber starts with 1 (incrementing by one) so generally only the positive integers are used.

Once a number has been used, deleted or skipped it cannot be reused without resorting to special code to handle the auto-numbering.



Rand

Another thing to consider here, you are talking about 2.1 BILLION Identity records if you are storing that many records in a single Table you might want to consider upgrading to SQL Server as I can only imagine the preformance of Access when trying to query that many records.

================================================== =========
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.
================================================== =========
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile
================================================== =========

dparsons has a very good point.

Access will hit-the-wall (the maximum size of an Access database is 2.1 billion bytes for all tables, code, etc.) long before most applications will use up 2.1 billion records. A one field table of autonumber (long int) type will max out the database at around 536 million records. Access is only intended for small databases.

Rand

I dont intend the database to hold that many records at any one given time however given the number of times the records will be deleted and inserted (aprox 10000 records will be inserted, deleted daily) this autonumber datatype will be used up very quickly, if you can see my point!

since autonumber increments by one can the ones that are deleted be reused. is there a way round this so that each record continues to have a unique identifier generated automatically regardles of the number of times the records are inserted and deleted?

In SQL you can reset the Identity column by calling: TRUNCATE TABLE [name]

WARNING - this is identical to calling DELETE on your table except that it also resets your identity column so be careful when calling it because all of the data in the table will be gone as well.

It seems, based on your explination, that the table you are talking about is pretty much a work table (eg rows are being inserted into it, some things are being done to the data, and then the data gets sent somewhere else) and if that is the case, you could write a process that would call TRUNCATE on x table during the off hours of your agency.

hth.



================================================== =========
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.
================================================== =========
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile
================================================== =========

If you empty the table, the autonumber field can be reset to 1 (or 0). There are examples available on how to do this - google "access autonumber reset" to see a bunch of them.

Rand

With all due respect, do the math.

An integer can hold roughly 2 * 10^9 values. If you use up 10^4 values per day, it will take 2 * 10^5 days before the values are used up. This is about 548 years.......

As another poster stated, you'll run out of Access long before you'll run out of values.

Jeff Mason
Custom Apps, Inc.
[email protected]

Jeff does make a very good point. Even at the number your talking (100K) that is only 36 Million rows of data annually. (Every 10 days you will process one million records * 3 * 12) So ya...

================================================== =========
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.
================================================== =========
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile
================================================== =========

With all due respect to the mathmaticians! Is there a solution to automatically allocate unique identifiers to records without the possibility like autonumber to run out of numbers to allocate? Even if it would take 500 or so years before these numbers would diminish i would still be identifying records with enormous integers. The other solution about reseting autonumber would not be possible becuase all the data in the database isnt deleted all at once. whilst 10000 or more records would be deleted and inserted the databse table would still contain another million records or so. Data is being inserted/deleted and appended to this table therefore autonumber reset would not be feasable.

Has any one any ideas!