Reset autonumber

P

Peter

I have used autonumber for Employee. However, I have
deleted the 2nd one accidently. Is it possible for me to
compact the database so that I get back the autonumber 2 ?
 
A

Arvin Meyer

Peter said:
I have used autonumber for Employee. However, I have
deleted the 2nd one accidently. Is it possible for me to
compact the database so that I get back the autonumber 2 ?
Click to expand...

No. Autonumbers only exist to guarantee uniqueness, not to provide a
sequential numbering system. Once used, you never get it back. If you need a
sequential number, you can use code to get one. Then you can just re-enter a
lost one. I'd suggest not using it as a key though, because keys usually
have no meaning except to the system.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
P

Peter

Dear Arvin,

Thank you for your advice.

On the other hand, I have entered some testing data in the
database. Before deliver to end user, what is the best
way to remove all testing data ?
 
P

Peter Profiris

As mentioned, Autonumber is to guarantee uniqueness, not sequential.

However, if you do the following steps sequentially without opening any
other queries / forms / reports / etc .....

If you are looking to reset a table after testing, and the table will be
empty when delivering to end user, try copying the original table (eg
tbl_Employee) without data to another name (eg tbl_EmployeeNew), then delete
the old table (tbl_Employee), then rename the copied table back to the
original (tbl_EmployeeNew to tbl_Employee).

When this table is then accessed for the first time, the AutoNumber will be
reset. All the queries, forms, reports, etc will be none the wiser as the
same table with the same structure and same formatting is available.

Cheers
 
T

Tom Wickerath

Peter,

There is no need to copy the structure to a new table, if all existing data
will be deleted from the table. In fact, doing so should require an
additional step that you didn't mention: that would be to delete any existing
relationships before you deleted the original table, and then re-establish
them when you rename the new table. If this is not a requirement, then you
likely have not established relationships between the tables. Relationships
between tables serve a very important purpose, namely referential integrity.

If all records have been deleted, then the autonumber will reset when you
compact the database using Tools > Database Utilities > Compact and repair
database.

Tom
______________________________________

:

As mentioned, Autonumber is to guarantee uniqueness, not sequential.

However, if you do the following steps sequentially without opening any
other queries / forms / reports / etc .....

If you are looking to reset a table after testing, and the table will be
empty when delivering to end user, try copying the original table (eg
tbl_Employee) without data to another name (eg tbl_EmployeeNew), then delete
the old table (tbl_Employee), then rename the copied table back to the
original (tbl_EmployeeNew to tbl_Employee).

When this table is then accessed for the first time, the AutoNumber will be
reset. All the queries, forms, reports, etc will be none the wiser as the
same table with the same structure and same formatting is available.

Cheers
______________________________________

:

Dear Arvin,

Thank you for your advice.

On the other hand, I have entered some testing data in the
database. Before deliver to end user, what is the best
way to remove all testing data ?
 
A

Arvin Meyer

Delete all the data, started with the many side. Then compact your database.
It will reset to 0 for each table where all the data has been deleted. In
Access 97 and earlier, compacting the database starts with the next highest
number after the deleted data, so you could just delete part of the data.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Top