AutoNumber reset

[tflett]

Does anyone know how to reset the autonumber back to 0 or 1??

 

[Allen Browne]

Delete all records.
Then compact the database (Tools | Database Utilities.)

If you need to reset it programmatically:
http://allenbrowne.com/func-ADOX.html#ResetSeed

 

[tflett]

Thank you

Delete all records.
Then compact the database (Tools | Database Utilities.)

If you need to reset it programmatically:
http://allenbrowne.com/func-ADOX.html#ResetSeed

 

[Jeff Boyce]

Allen provided pointers to "how to".

Now, ?why? I'm not asking out of simple curiosity. Frequently folks try to
use Access Autonumbers to mean something (e.g., "a sequence number", an
"invoice number", ...).

Access Autonumbers are intended to be used as unique row identifiers, and
are generally unfit for human consumption. Why do you care what value the
autonumber is?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[tflett]

After testing my DB, I would like to reset the autonumbers back to 1 when
entering new data. My sutonumber is being used to keep track of clients in
the DB.

 

[steveatdb]

I would try using a different method to increment in this case to keep
everything in same order the problem with using autonumber for tracking
clients is that it can turn out to be random. You can not be certain that you
will be given numbers in order as in 1,2,3,4... it may jump from
4,5,6,8692003,7,8.

 

[Jeff Boyce]

By "keep track of clients", do you mean count them? Remember, Autonumbers
will (eventually) not be sequential, and can be (set to) random.

Do you mean "related a client in one table to that client's 'child' records
in another table"? If so, does it matter if the ID is 1 or 191?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[tflett]

I would like to count all the clients being entered in the db for percentages
purposes. My autonumber is my PK and is being used to link other tables
together. I guess it doesn't matter what the autonumber is. So I take it
all other autonumbers in the db won't matter as long as the relationships are
correct?

 

[John W. Vinson]

After testing my DB, I would like to reset the autonumbers back to 1 when
entering new data. My sutonumber is being used to keep track of clients in
the DB.

What Jeff is saying is... let the autonumber keep track of clients, *behind
the scenes and under the hood*. Neither you nor your users should care whether
a client is number 3, or 318, or -225104225. The ONLY purpose of the
autonumber is to provide a unique key for linking tables within your
application; they're not suitable for human consumption.

John W. Vinson [MVP]

 

[Jeff Boyce]

If you have the relationships set (e.g., one-to-many, using a foreign key
field in the many side table that points back to the {?autonumber} primary
key in the one side table), it won't matter.

If you want a count of rows in ANY table, use a Totals query (or the
DCount() function).

That way, it won't matter how many gaps are in the Autonumber (primary key)
sequence (and there will be!), you can still count how many.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I would like to count all the clients being entered in the db for percentages
purposes.

Then count them, using a Totals query.

The Autonumber is *NOT* a count, and should not be used as if it were!

John W. Vinson [MVP]

 

[Jeff Boyce]

Thanks, John. I was beginning to wonder if I was being too subtle...<g>.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[tflett]

Thanks for the Info guys you were both extremely helpful.