Reset AutoNumber in Table

[sxschech]

I read some threads that indicated that there are 2 ways to reset a counter id field. First way is to delete contents of data and then do a compact. The other is to delete data, remove field and then add back the field. I had been doing the compact way and it works, however I would like to automate this and thought about doing it the second way as I need to perform several steps after the counter has been reset since compacting won't allow me to continue from where I left off. I created several DDL queries to alter the table to remove and add the counter id field. Unfortunately, this did not reset the counter as appending data continued from whereever the last number left off. (Even when I manually deleted the data, removed the field and added it back the counter didn't reset) Any thoughts on automating the reset would be greatly appreciated.

 

[JeffAlton]

If all you need is an empty table where autonumber starts at 1 try this. Delete Data. Compact the database. Export table to seperate .mdb file. Now in your automation sequence all you need to do is delete the table and import the empty one from the other database.

 

[TonyJollans]

Hi sxschech,

Another way might be to copy the (empty) table. When you do this, the history of AutoNumber usage is not copied with the Table so the AutoNumber gets reset in the copy.

With DoCmd

    .SetWarnings False
    .RunSQL "Delete * From Table1"
    .SetWarnings True

    .Rename "Table1Old", acTable, "Table1"
    .CopyObject , "Table1", acTable, "Table1Old"
    .DeleteObject acTable, "Table1Old"

End With

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[sxschech]

Hey Guys,

Thanks for your suggestions. I think Tony's suggestion is a bit easier to work with. Since I was interested to learn about DDL queries, I decided on creating two queries one to do a drop table and the second to create table. This also resets the ID counter field. In the future I probably would use Tony's method so I don't have to type so much to create all the fields, data types and col widths for the table.