Click here to Skip to main content
11,795,568 members (78,346 online)
Rate this: bad
Please Sign up or sign in to vote.
See more: VB VB.NET MS-Access
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[^]

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

  Print Answers RSS
0 Maciej Los 340
1 Sergey Alexandrovich Kryukov 316
2 OriginalGriff 285
3 Abhinav S 223
4 KrunalRohit 214
0 OriginalGriff 1,797
1 Maciej Los 1,415
2 KrunalRohit 1,240
3 CPallini 840
4 ppolymorphe 808

Advertise | Privacy | Mobile
Web01 | 2.8.151002.1 | Last Updated 28 Feb 2013
Copyright © CodeProject, 1999-2015
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