How can I reset the AutoNumber field ?

[danial]

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

 

[Andrew Fergus]

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]

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).

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]

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.