AutoNumber Field Limits - NOT "How do I reset?"

[Sideshowmom]

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.

 

[Jerry Whittle]

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.

 

[John W. Vinson]

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.

 

[Guest]

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.

 

[Sideshowmom]

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]

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!

You may at some point (perhaps after you get 2003) want to create a new, empty
table and run an Append query, appending all the fields EXCEPT the autonumber.
That will give you a clean new sequential autonumber series. This gets
snarkier if you have child tables linked to this one, which would need their
foreign keys updated as well.

 

[Tony Toews [MVP]]

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.

This sounds like the autonumber ID field New Values property was
changed to Random. Or the database was accidentally replicated or
dropped in Windows Briefcase which would also change the above New
Values property.

If you don't have any child records you can delete and recreate this
field to reset the New Values property to Increment. If you do have
child records when it becomes more of a pain.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[david]

On Tue, 30 Mar 2010 09:58:04 -0700, Sideshowmom

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

Access effectively appends all records, then checks the unique indexes for
clashes and DRI.

It has to use the autonumber to base the index on before it can check the
index and do DRI.

This uses up autonumbers, and bloats the database to the extent of any
records which are rejected.

Doesn't sound like that happened here.


(david)

 

[John W. Vinson]

Access effectively appends all records, then checks the unique indexes for
clashes and DRI.

It has to use the autonumber to base the index on before it can check the
index and do DRI.

This uses up autonumbers, and bloats the database to the extent of any
records which are rejected.

Thanks David! I'd wondered how that worked.

Agree that it doesn't seem to be the cause of this particular problem unless
Sideshowmom is running some very strange queries.

 

[Risse]

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.

 

[Tony Toews [MVP]]

This uses up autonumbers, and bloats the database to the extent of any
records which are rejected.

And given how Jet 4.0 appears to work this might be one 4k page per
record that was in the append query before records were rejected.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[david]

And given how Jet 4.0 appears to work this might be one 4k page per

record that was in the append query before records were rejected.

I think I remember that there was a specific fix/update to prevent that
happening.

(david)