[AccessD] Resetting an AutoNumber

You can do this via VBA code or even SQL


ALTER TABLE <tablename> ALTER COLUMN <fieldname> Counter(<seed>,<increment>)

The seed in a long integer value that specifies what number to restart at,
the increment is the number by which to increase each new entry. So, if you
have a table named "Customers" and you want to reset field "CustomerID" to
start at 10 and increment by 5 each time a new record is added, use the
command:

ALTER TABLE Customers ALTER COLUMN CustomerID Counter(10,5)

Pay attention to relationships however

There is no need for closing & compaction of db

Tejpal, A.D.  has an example at
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='AutoNumbers_Reclaim.mdb'

rusty.hammond at cpiqpc.com wrote:

>Arthur,
>
>The process you describe will work.  I don't know if it's a recommended
>strategy but deleting and recreating the ANPK field works too.  I like your
>way better just because the compact process cleans up the file.
>
>HTH,
>
>Rusty
>
>-----Original Message-----
>From: Arthur Fuller [mailto:fuller.artful at gmail.com]
>Sent: Sunday, June 24, 2007 8:19 AM
>To: Access Developers discussion and problem solving
>Subject: [AccessD] Resetting an AutoNumber
>
>
>Hi all,
>
>It's been a while since I worked in an MDB, and in the immortal words of The
>Simpsons, there are no stupid questions, only stupid people -- so I'm
>stupid. I can't remember. Is it enough to nuke all the rows in a table, and
>then compact+repair? Will that reset the ANPK to 1?
>
>Struggling through this senior moment,
>Arthur
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada