Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
5.00/5 (2 votes)
See more:
Friends
I have created a table in ms-access table.
in that i created the invoiceno field as autonumber.
now i finished the project.
i deleted all the testing data in the table.
while im deleting the autonumber was 1027
after deleting the data completely from the table.
i tried to insert a new record.
the autonumber is coming 1028 i dont want 1028 i want to start from 1(ie. from beginning).

so how can i reset this.


thanks
Posted
Updated 4-Jan-23 12:02pm

After you delete all of the rows in the table, compacting the database should reset the Autonumber value back to 1.

for Compacting database go through this link-

How to compact a Microsoft Access database by using Visual Basic .NET[^]
 
Share this answer
 
AFAIK you can't, without deleting the field and re-creating it.

There are good reasons for this.
1) Autonumber is often used (unfortunately) as an index to the table. This means there could exist other rows which refer to a now deleted row by the previous autonumber. If you could reset the number, then those rows would eventually be associated with a new row instead.

In your case, imagine what trouble it would cause if you used the autonumber as an invoice number, and had a separate tablw of items on the invoice. these would then refer back to the invoice by the autonumber. If you reset the number, suddenly a customer gets invoiced for thirty items he didn't order, and hasn't had. Trouble ensues.

Personally, I hate autonumber and only ever use it when I will never refer to it: as an index for a log table for example. If you need a specific sequence of invoice numbers (and legally you do in most areas) consider writing a stored procedure to allocate a new invoice number and use a Guid for the actual database row reference instead.
 
Share this answer
 
You can execute an Access DDL statement from ADO to reset the autonumber seed value. Here is an example Immediate window session:

strDdl = "ALTER TABLE Dummy ALTER COLUMN ID COUNTER(1, 1);"
CurrentProject.Connection.Execute strDdl

The statement must be executed from ADO. It will fail if you try it with DAO (such as CurrentDb.Execute strDdl), or from the Access query designer. The example succeeded because CurrentProject.Connection is an ADO object.

The two values following COUNTER are seed and increment. So if I wanted the autonumber to start from 1000 and increment by 2, I could use COUNTER(1000, 2)

If the table contains data, the seed value must be greater than the maximum stored value. If the table is empty when you execute the statement, that will not be an issue.


------------
http://stackoverflow.com/questions/20738596/how-to-reset-an-access-tables-autonumber-field-it-didnt-start-from-1[^]

Credit goes to -> HansUp
 
Share this answer
 
v2
ALTER TABLE Table1 ALTER COLUMN ID COUNTER(0,1)
 
Share this answer
 
Comments
CHill60 18-Aug-21 4:06am    
A very poor repeat of Solution 4 from 6 years ago. Please ensure you are bringing something new to the thread if you are going to post solutions to such old questions
Easy way is to..

delete any blank records in table

rename to table0 - if warned about relationships, write them down and delete them

copy the table structure by right-clicking on the table and picking structure only - save that as table

right-click table0, pick move data to another table - pick table to send it to

problem solved
make sure your data made it to new table then it's OK to delete table0
reset your relationships
 
Share this answer
 
compact the database and then try the insert again
 
Share this answer
 
Comments
CHill60 5-Jan-23 4:52am    
5'd.
Despite this being a very old thread, this is actually the easiest and safest way to achieve what the OP asked. No need to delete the records first.
HOWEVER - note the comments made by OriginalGriff in Solution 1 - if used as a foreign key, this may break
Ah - however, just realised that Solution 2 essentially says the same thing and with more detail so removing my 5

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900