Click here to Skip to main content
13,193,048 members (62,855 online)
Rate this:
Please Sign up or sign in to vote.
See more:
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.

Posted 14-Jan-12 3:39am
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

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[^]
Rate this: bad
Please Sign up or sign in to vote.

Solution 4

You can execute an Access DDL statement from ADO to reset the autonumber seed value. Here is an example Immediate window session:
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.


Credit goes to -> HansUp

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web02 | 2.8.171017.2 | Last Updated 27 Oct 2015
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100