Autonumber

[Jack]

I designed a database and enter some test records. On some of the tables I
decided I would be better to have an autonumber. I deleted the records in
the table and did away with the relationships. Changed the field to an
autonumber. When I entered the first record it started the autonumber at 3.
If I deleted that record it starts at the next number (4). I would like for
it to start at 1 and go up in sequence (1, 2, 3 ect). Can I do this?

 

[John W. Vinson]

I designed a database and enter some test records. On some of the tables I
decided I would be better to have an autonumber. I deleted the records in
the table and did away with the relationships. Changed the field to an
autonumber. When I entered the first record it started the autonumber at 3.
If I deleted that record it starts at the next number (4). I would like for
it to start at 1 and go up in sequence (1, 2, 3 ect). Can I do this?

Well... don't bother.

Autonumbers have only ONE purpose: to provide a meaningless unique key. They
will ALWAYS have gaps; they are not guaranteed to start at 1 or to be
sequential; they can even become random. It's best to keep autonumbers
concealed from user view, because it's very easy to misinterpret them as
"record numbers" - which they emphatically are NOT.

That said... empty the table and Compact the database. Depending on the
version, this will usually reset the autonumbers.

 

[Jack]

Thank you John.
After reading your reply I see no reason to use autonumbers. I will just
use my own numbers as a key field, and with a little programing I can keep
them sequential if I want or need to.
I am using 2007. How does access keep track of record numbers and will they
change if I sort on different fields?

 

[John W. Vinson]

How does access keep track of record numbers and will they
change if I sort on different fields?

It doesn't, and since there ARE no record numbers, there's nothing to
"change". Tables have no (intrinsic) numbers, and no defined order; the very
concept of a "record number" is simply alien to Access (and to other
relational databases).

 

[Jeff Boyce]

I'm with John. Access doesn't need 'no steenken record numbers' ... and
unless YOU tell Access which field to sort on, Access doesn't need to.

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.