How can I reset the AutoNumber field ?

danial

Board Regular
Joined
Apr 29, 2006
Messages
107
Sawasdee krub,

When I run qry delete all data in a table.
I want to reset the running number in the field autonumber to 1

can I create code for the above action.

Thank in advance
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Sawasdee krub Danail. Phoot Thai mai dai but I know "hello" and "thank you".

I don't think you can create code to reset the autonumber. However, if you compact and repair the database then Access will do it for you. Click menu option Tools > Database Options > Compact and Repair Database.

lah gòrn
Andrew
P.S. I hope I got those phrases right! No offence intended if I didn't...:)
 

mdmilner

Well-known Member
Joined
Apr 30, 2003
Messages
1,352
The closest I've been able to get to doing this via code is to:

Create a new Table using Tabledefs - this is necessary because it's the only way I know to create/define a field as an autonumber in code. You can then generate an append query to copy the old data to the new and let it auto-increment.

This could create additional problems if you have any table relationships involved - in which case you'd have to break/re-establish those also, from code using DAO only (ADO doesn't support it).

Code:
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set fld = tdf.CreateField("ID", dbLong)
fld.Attributes = fld.Attributes Or dbAutoIncrField  ' autonumber options
tdf.Fields.Append fld
dbs.TableDefs.Append tdf

Obviously, these commands could be embedded a bit more - I snipped these out of a function I wrote previously.

Mike
 

danial

Board Regular
Joined
Apr 29, 2006
Messages
107
Thank you Mike.
It's work for me.


and Thank you Andrew too.
K.Andrew krub(=sir)
If you want to say good-bye
You should say sawassdee or bye bye

"Lah gorn" sound like good-bye forever or not see you again.

but I want to see you later.
 

Forum statistics

Threads
1,136,712
Messages
5,677,330
Members
419,688
Latest member
sarahmichelle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top