Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 17th, 2007, 03:44 PM
Registered User
 
Join Date: Jan 2007
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Auto number in microsoft access

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?

 
Old January 17th, 2007, 03:58 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old January 17th, 2007, 04:06 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
 
Old January 17th, 2007, 04:18 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old January 17th, 2007, 04:40 PM
Registered User
 
Join Date: Jan 2007
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

 
Old January 17th, 2007, 04:47 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
 
Old January 17th, 2007, 04:54 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old January 17th, 2007, 10:35 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by ozzii
 ... aprox 10000 records will be inserted, deleted daily [so] this autonumber datatype will be used up very quickly, if you can see my point!
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.
je.mason@comcast.net
 
Old January 17th, 2007, 10:50 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
 
Old January 18th, 2007, 06:46 AM
Registered User
 
Join Date: Jan 2007
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!






Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto Number query again Brendan Bartley Access 4 August 26th, 2006 06:09 AM
problem with Auto Number Brendan Bartley Access 6 August 14th, 2006 10:13 AM
[b]Auto Number[/b] vanjamier Classic ASP Databases 1 November 18th, 2004 03:44 AM
Auto number yami56 Access 1 August 20th, 2004 11:24 AM
Auto number Sheri Dreamweaver (all versions) 0 December 22nd, 2003 03:36 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.