AutoNumber Field Limits - NOT "How do I reset?"
in [General]
|
Prev: Rounding difference in Access 2003 text exports
Next: Attaching a 2003 Access data base to an email for use with Office
From: Sideshowmom on 30 Mar 2010 12:58 My questions about this are: What is the upper limit of an AutoNumber field? and are the jumps in numbering going to reduce the capacity of my table/database (already have 1000 records, anticipate several million over the course of the database's life), ultimately? (Access 2000) The numbering in one of my tables jumped from 374 to 37949458 one day in the course of normal useage and is now in negative numbers. I do plan to implement the "compact the database, make a new table and run an append query" method. I did want to know about the limits, still. Thank you.
From: Jerry Whittle on 30 Mar 2010 13:41 Unless there is a corruption problem, it will not reduce the number of records. As you have seen, the generated number can jump around. You really don't need to do anything more than a compact and repair every great once in a while. The only exception is if you have some strange code or importing-delete-importing going on that is burning through the autonumbers very quickly. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Sideshowmom" wrote: > My questions about this are: What is the upper limit of an AutoNumber field? > and are the jumps in numbering going to reduce the capacity of my > table/database (already have 1000 records, anticipate several million over > the course of the database's life), ultimately? (Access 2000) > > The numbering in one of my tables jumped from 374 to 37949458 one day in the > course of normal useage and is now in negative numbers. I do plan to > implement the "compact the database, make a new table and run an append > query" method. I did want to know about the limits, still. > > Thank you.
From: John W. Vinson on 30 Mar 2010 14:04 On Tue, 30 Mar 2010 09:58:04 -0700, Sideshowmom <Sideshowmom(a)discussions.microsoft.com> wrote: >My questions about this are: What is the upper limit of an AutoNumber field? >and are the jumps in numbering going to reduce the capacity of my >table/database (already have 1000 records, anticipate several million over >the course of the database's life), ultimately? (Access 2000) > >The numbering in one of my tables jumped from 374 to 37949458 one day in the >course of normal useage and is now in negative numbers. I do plan to >implement the "compact the database, make a new table and run an append >query" method. I did want to know about the limits, still. > >Thank you. An autonumber is a Long Integer with a range from 0 through 2147483647; it will then jump to -2147483648 and start counting up toward 0. You'll get gaps in the numbering if you delete records or cancel an addition; huge gaps like you're getting probably come from running Append queries - Access seems to reserve enough autonumber "slots" for the worst case scenario in terms of the tables that are being appended from, and leaves gaps when there are fewer records actually appended. I've not heard of a case this drastic though! Are you in fact running append queries? If not, how are you getting data into the table? Are there other tables related to this one on the autonumber ID? A2000 was sort of notorious for bugs and flaky behavior; I wonder if an upgrade might be in order. I suggest going to 2007 with some trepidation because it's a *dramatically* different user interface, but perhaps you could get a copy of 2003 on the retail market. At the very least do connect to Microsoft and make sure you have all the A2000 Service Packs installed. -- John W. Vinson [MVP]
From: e.sada on 30 Mar 2010 14:09 "Sideshowmom" <Sideshowmom(a)discussions.microsoft.com> schreef in bericht news:1C87158C-217F-4407-9EC6-C3270FEC0952(a)microsoft.com... > My questions about this are: What is the upper limit of an AutoNumber > field? > and are the jumps in numbering going to reduce the capacity of my > table/database (already have 1000 records, anticipate several million over > the course of the database's life), ultimately? (Access 2000) > > The numbering in one of my tables jumped from 374 to 37949458 one day in > the > course of normal useage and is now in negative numbers. I do plan to > implement the "compact the database, make a new table and run an append > query" method. I did want to know about the limits, still. > > Thank you.
From: Sideshowmom on 30 Mar 2010 18:54
Thank you for your answers, they satisfied my curiosity just fine. No, I haven't been running append queries, just typing information into the forms and tables. I did try to import from an Excel copy of a table to see if I could get it to reset, once, but after the numbering jump. It seemed to happen when I switched from just using the tables to the newest set of forms I made, about 2 months ago. I've upgraded every chance there was and I did see A2003 on Amazon a bit ago. It's in my price-range, much more than 2007 is! "John W. Vinson" wrote: > On Tue, 30 Mar 2010 09:58:04 -0700, Sideshowmom > <Sideshowmom(a)discussions.microsoft.com> wrote: > > >My questions about this are: What is the upper limit of an AutoNumber field? > >and are the jumps in numbering going to reduce the capacity of my > >table/database (already have 1000 records, anticipate several million over > >the course of the database's life), ultimately? (Access 2000) > > > >The numbering in one of my tables jumped from 374 to 37949458 one day in the > >course of normal useage and is now in negative numbers. I do plan to > >implement the "compact the database, make a new table and run an append > >query" method. I did want to know about the limits, still. > > > >Thank you. > > An autonumber is a Long Integer with a range from 0 through 2147483647; it > will then jump to -2147483648 and start counting up toward 0. You'll get gaps > in the numbering if you delete records or cancel an addition; huge gaps like > you're getting probably come from running Append queries - Access seems to > reserve enough autonumber "slots" for the worst case scenario in terms of the > tables that are being appended from, and leaves gaps when there are fewer > records actually appended. I've not heard of a case this drastic though! > > Are you in fact running append queries? If not, how are you getting data into > the table? Are there other tables related to this one on the autonumber ID? > > A2000 was sort of notorious for bugs and flaky behavior; I wonder if an > upgrade might be in order. I suggest going to 2007 with some trepidation > because it's a *dramatically* different user interface, but perhaps you could > get a copy of 2003 on the retail market. At the very least do connect to > Microsoft and make sure you have all the A2000 Service Packs installed. > -- > > John W. Vinson [MVP] > . > |