What is the largest "auto number" that can exist in an Access database? How to reset autonumber to start at 1?

[John Baker]

Hi:

I know this is a strange question, but I have inherited a system where files are copied
and records re auto numbered (as an index field) )frequently, and I am wondering how high
the number can go without the system crashing.

An ancillary question is how one resets an auto number so that the sequence starts again
at 1.

In the case of this file, the auto number field serves no useful purpose except as an
index, and identifier of a unique record, so no problem with renumbering the records
provided one does not try and create two identical numbers (in which case the system
throws up all over you!).

Regards

John Baker

[Jeff Smith]

Maximum size for an autonumber is 2,147,483,647 and the smallest it can be
is -2,147,483,648.
One way to reset it back to zero is to delete all records in the table and
then compact the database.

Jeff
"John Baker" <Ba******@Verizon.net> wrote in message
news:i6********************************@4ax.com...

Hi:

I know this is a strange question, but I have inherited a system where files are copied and records re auto numbered (as an index field) )frequently, and I am wondering how high the number can go without the system crashing.

An ancillary question is how one resets an auto number so that the sequence starts again at 1.

In the case of this file, the auto number field serves no useful purpose except as an index, and identifier of a unique record, so no problem with renumbering the records provided one does not try and create two identical numbers (in which case the system throws up all over you!).

Regards

John Baker

[John Baker]

Thanks.. its good to know I can do that.

Is there any programmatic way to reset it? I would almost like to reset it every time I
use it!

Best

John
"Jeff Smith" <No***@Not.This.Address> wrote:

Maximum size for an autonumber is 2,147,483,647 and the smallest it can be
is -2,147,483,648.
One way to reset it back to zero is to delete all records in the table and
then compact the database.

Jeff
"John Baker" <Ba******@Verizon.net> wrote in message
news:i6********************************@4ax.com.. .

Hi:

I know this is a strange question, but I have inherited a system where

files are copied

and records re auto numbered (as an index field) )frequently, and I am

wondering how high

the number can go without the system crashing.

An ancillary question is how one resets an auto number so that the

sequence starts again

at 1.

In the case of this file, the auto number field serves no useful purpose

except as an

index, and identifier of a unique record, so no problem with renumbering

the records

provided one does not try and create two identical numbers (in which case

the system

throws up all over you!).

Regards

John Baker

[MacDermott]

If you're even thinking you might want to reset it,
don't use an autonumber.
Autonumbers are not meant to be meaningful, just unique.

"John Baker" <Ba******@Verizon.net> wrote in message
news:lt********************************@4ax.com...

Thanks.. its good to know I can do that.

Is there any programmatic way to reset it? I would almost like to reset it every time I use it!

Best

John
"Jeff Smith" <No***@Not.This.Address> wrote:

Maximum size for an autonumber is 2,147,483,647 and the smallest it can beis -2,147,483,648.
One way to reset it back to zero is to delete all records in the table andthen compact the database.

Jeff
"John Baker" <Ba******@Verizon.net> wrote in message
news:i6********************************@4ax.com.. .

Hi:

I know this is a strange question, but I have inherited a system where

files are copied

and records re auto numbered (as an index field) )frequently, and I am

wondering how high

the number can go without the system crashing.

An ancillary question is how one resets an auto number so that the

sequence starts again

at 1.

In the case of this file, the auto number field serves no useful

purposeexcept as an

index, and identifier of a unique record, so no problem with

renumberingthe records

provided one does not try and create two identical numbers (in which

casethe system

throws up all over you!).

Regards

John Baker

[Trevor Best]

Jeff Smith wrote:

Maximum size for an autonumber is 2,147,483,647

Out of interest, when using increment, what happens to the next record
after that number? Does it roll over into the negative?

Back in the old DOS days, I wrote a program to calculate prime numbers,
as I remember my 12MHz 286 was rather slow so for speed I wrote 4
separate bits to do it, going from integer, long, single then double so
that when say the integer got over 32767 it would error and the error
trap would resume to the long bit and carry on. This worked in the IDE
(MS Basic PDS 7.1) but when compiled to a stand alone .exe file it would
not error but would wrap into negative numbers, no matter if I used x87
emulation[1] or alternate math libs.

[1] Well I didn't have a 287 co-pro on there.

--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

[Terry Kreft]

Trevor,
If you create a new table (or compact an empty one), then append a record
with the autonumber field set to 2,147,483,647 then start entering records
manually the first manually entered record has an autonumber field value of
1 and then they increment as normal (2, 3, 4, 5 etc).

If, however, you create a new table (or compact an empty one), then append
a record with the autonumber field set to 2,147,483,646 then start entering
records manually the first manually entered record has an autonumber field
value of 2,147,483,647 but from then on they change to negative values,
-2147483648, -2147483647, -2147483646 and so on; note they are still
incrementing

(Tested in XP)
--
Terry Kreft
MVP Microsoft Access
"Trevor Best" <nospam@localhost> wrote in message
news:41***********************@auth.uk.news.easyne t.net...

Jeff Smith wrote:

Maximum size for an autonumber is 2,147,483,647
Out of interest, when using increment, what happens to the next record
after that number? Does it roll over into the negative?

<SNIP> [1] Well I didn't have a 287 co-pro on there.

--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

[David W. Fenton]

John Baker <Ba******@Verizon.net> wrote in
news:i6********************************@4ax.com:

I know this is a strange question, but I have inherited a system
where files are copied and records re auto numbered (as an index
field) )frequently, and I am wondering how high the number can go
without the system crashing.

An ancillary question is how one resets an auto number so that the
sequence starts again at 1.

In the case of this file, the auto number field serves no useful
purpose except as an index, and identifier of a unique record, so
no problem with renumbering the records provided one does not try
and create two identical numbers (in which case the system throws
up all over you!).

Others have already pointed out that if you actually care about the
values of the autonumber, then you're mis-using it.

Let me also point out that there's nothing magical about an
autonumber: it is just a special kind of default value. If you want
to re-use numbers, simply append your record with the autonumber set
to a value that is presently unused.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[david epsom dot com dot au]

Normal autonumbers are long numbers. See the specification for Long.

If the table is empty, compacting the database will reset the auto
number to 1.

(david)

"John Baker" <Ba******@Verizon.net> wrote in message
news:i6********************************@4ax.com...

Hi:

I know this is a strange question, but I have inherited a system where files are copied and records re auto numbered (as an index field) )frequently, and I am wondering how high the number can go without the system crashing.

An ancillary question is how one resets an auto number so that the sequence starts again at 1.

In the case of this file, the auto number field serves no useful purpose except as an index, and identifier of a unique record, so no problem with renumbering the records provided one does not try and create two identical numbers (in which case the system throws up all over you!).

Regards

John Baker

[John Baker]

Terry:

If I understand what your saying correctly, overflowing an auto number will cause no
problems at all. In other words rather than the maximum number of UNIQUE auto numbers
being 2,147,483,647, it is in fact twice that, since you have the negative value AND the
positive one.

Have I understood that correctly?

Best

John

"Terry Kreft" <te*********@mps.co.uk> wrote:

Trevor,
If you create a new table (or compact an empty one), then append a record
with the autonumber field set to 2,147,483,647 then start entering records
manually the first manually entered record has an autonumber field value of
1 and then they increment as normal (2, 3, 4, 5 etc).

If, however, you create a new table (or compact an empty one), then append
a record with the autonumber field set to 2,147,483,646 then start entering
records manually the first manually entered record has an autonumber field
value of 2,147,483,647 but from then on they change to negative values,
-2147483648, -2147483647, -2147483646 and so on; note they are still
incrementing

(Tested in XP)

[Terry Kreft]

Yes, you have the full range of an unsigned long to play with.

--
Terry Kreft
MVP Microsoft Access
"John Baker" <Ba******@Verizon.net> wrote in message
news:rd********************************@4ax.com...

Terry:

If I understand what your saying correctly, overflowing an auto number will cause no problems at all. In other words rather than the maximum number of UNIQUE auto numbers being 2,147,483,647, it is in fact twice that, since you have the negative value AND the positive one.

Have I understood that correctly?

Best

John

"Terry Kreft" <te*********@mps.co.uk> wrote:

Trevor,
If you create a new table (or compact an empty one), then append a recordwith the autonumber field set to 2,147,483,647 then start entering recordsmanually the first manually entered record has an autonumber field value of1 and then they increment as normal (2, 3, 4, 5 etc).

If, however, you create a new table (or compact an empty one), then appenda record with the autonumber field set to 2,147,483,646 then start enteringrecords manually the first manually entered record has an autonumber fieldvalue of 2,147,483,647 but from then on they change to negative values,
-2147483648, -2147483647, -2147483646 and so on; note they are still
incrementing

(Tested in XP)

[Trevor Best]

Terry Kreft wrote:

Yes, you have the full range of an unsigned long to play with.

Yes, just mentally add 4 binary billion to the negative numbers to get
the number you want :-)

ISTR when there were a few more BASIC compilers around, one of them
(can't remember which) had an integer type called a cardinal, which was
just an unsigned int like you can have in C.

--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

[John Baker]

Thanks guys. Since I don't expect the system to exist without major changes for more than
3-4 years, and the table I am concerned about changes about once a month, and only
contains 300 items, I don't think I am going to have a problem.

Appreciate your input on this.

Incidentally, if I did want to reset the number back to zero, I gather I can do it by
emptying the table and compressing. I tried that specifically by copying the contents of
the table to another table (same format different name) emptying the original table,
compressing, and then copying the old data back to the new table without the auto add
number (which is an index key). For some reason the OLD number keeps turning up.

Any suggestions?

Best

John Baker

Trevor Best <nospam@localhost> wrote:

Terry Kreft wrote:

Yes, you have the full range of an unsigned long to play with.

Yes, just mentally add 4 binary billion to the negative numbers to get
the number you want :-)

ISTR when there were a few more BASIC compilers around, one of them
(can't remember which) had an integer type called a cardinal, which was
just an unsigned int like you can have in C.

[Terry Kreft]

The other way to do it is
Copy from "source" table to "temp" table
Delete "source" table
Copy from "temp" table to "source" table (structure only)
Use an Append query to add rows to the "source" table from the "temp"
table.

The big disadvantage of this is you'll need to remove and recreate any links
between the "source" table and other tables in your db.

Compacting the database should reset the autonumber so I don't understand
why this is not happening.
--
Terry Kreft
MVP Microsoft Access
"John Baker" <Ba******@Verizon.net> wrote in message
news:e8********************************@4ax.com...

Thanks guys. Since I don't expect the system to exist without major changes for more than 3-4 years, and the table I am concerned about changes about once a month, and only contains 300 items, I don't think I am going to have a problem.

Appreciate your input on this.

Incidentally, if I did want to reset the number back to zero, I gather I can do it by emptying the table and compressing. I tried that specifically by copying the contents of the table to another table (same format different name) emptying the original table, compressing, and then copying the old data back to the new table without the auto add number (which is an index key). For some reason the OLD number keeps turning up.
Any suggestions?

Best

John Baker

Trevor Best <nospam@localhost> wrote:

Terry Kreft wrote:

Yes, you have the full range of an unsigned long to play with.

Yes, just mentally add 4 binary billion to the negative numbers to get
the number you want :-)

ISTR when there were a few more BASIC compilers around, one of them
(can't remember which) had an integer type called a cardinal, which was
just an unsigned int like you can have in C.

[Ron Devonish]

The orginal question has still not been answered. What happens when the
table contains 4294967295 records (or rather the highest Autonumber is
4294967295). What is the next one?

If we regard the Autonumber as an unsigned long the maximum is
4294967295 (or maybe 4294967296!!). So what comes next?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Rick Brandt]

"Ron Devonish" <no*****@nowhere.com> wrote in message
news:41**********************@news.newsgroups.ws.. .

The orginal question has still not been answered. What happens when the
table contains 4294967295 records (or rather the highest Autonumber is
4294967295). What is the next one?

If we regard the Autonumber as an unsigned long the maximum is
4294967295 (or maybe 4294967296!!). So what comes next?

I would suspect an error would be raised, but even with a one field table
you will never have this many AutoNumbers consumed unless you have huge
quantities of records that are created, then deleted, then more added.

In a typical application you will hit the file size limit long before going
through this many numbers.

If you had a non-typical app where the aforementioned was happening then
AutoNumber would be a poor choice for a field in the same way that using a
long integer field to store phone numbers is a poor choice.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Trevor Best]

Ron Devonish wrote:

The orginal question has still not been answered. What happens when the
table contains 4294967295 records (or rather the highest Autonumber is
4294967295). What is the next one?

If we regard the Autonumber as an unsigned long the maximum is
4294967295 (or maybe 4294967296!!). So what comes next?

I'd agree with Rick, as you have a 2GB mdb file size limit you couldn't
fill those numbers anyway. A long integer is 4 bytes, this will give you
half a billion records minus Access' overheads if you had just the
autonumber field in a table.

If you were in a situation where you'd regularly delete records and
append new and use up all the numbers that way then I would reconsider
using an autonumber in there anyway (it's probably meaningless to the
computer let alone a human in that instance), or at least set it to a
random number.

The range of a Long Integer (even just the positive side) should be
adequate for any Access database, if it's not then you should reconsider
either the database engine or the design of the database.

--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Usually, when a positive number reaches it's limit, and its data type
allows negative numbers, it will switch over to negatives. IOW:

4294967295 will "increment" to -4294967294

The next numbers will be -4294967293, -4294967292, etc.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQS+S7IechKqOuFEgEQLcvwCeKzt3D0JMEETZ5bNlCgUgoP rojgUAoIab
u/mqBa/xAej8gX+1mI0VII8W
=ZNY0
-----END PGP SIGNATURE-----
Rick Brandt wrote:

"Ron Devonish" <no*****@nowhere.com> wrote in message
news:41**********************@news.newsgroups.ws.. .

The orginal question has still not been answered. What happens when the
table contains 4294967295 records (or rather the highest Autonumber is
4294967295). What is the next one?

If we regard the Autonumber as an unsigned long the maximum is
4294967295 (or maybe 4294967296!!). So what comes next?

I would suspect an error would be raised, but even with a one field table
you will never have this many AutoNumbers consumed unless you have huge
quantities of records that are created, then deleted, then more added.

In a typical application you will hit the file size limit long before going
through this many numbers.

If you had a non-typical app where the aforementioned was happening then
AutoNumber would be a poor choice for a field in the same way that using a
long integer field to store phone numbers is a poor choice.