3

Is it possible delete all entries in a table and reset the autonumber count?
I have tried:

Private Sub Command12_Click()
DELETE FROM a_test
End Sub

But it I get an error "Expected end of statement". I am using Access 2010.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
BigApeWhat
  • 321
  • 3
  • 15
  • http://stackoverflow.com/questions/1951780/clear-all-the-rows-in-a-table-resetting-the-identity-specification-back-to-zero – karthikr May 30 '13 at 16:30
  • See **[here](http://allenbrowne.com/func-ADOX.html#ResetSeed)**. The key is: `strSql = "ALTER TABLE [" & strTable & "] ALTER COLUMN [" & strAutoNum & "] COUNTER(" & lngNext & ", 1);"` Alternatively, just drop and recreate the table. – Prahalad Gaggar May 30 '13 at 16:32
  • Anyway, I think you are trying to execute a macro (Private Sub). You need to create a statement and execute it inside your Sub block. – eternay May 30 '13 at 16:34

2 Answers2

1

My advice is not to worry so much about reseeding the autonumber. Let Access maintain it's uniqueness. If you need to reset it, look at the comments others have posted.

In regards to fixing your actual error, you're not actually running anything above. If you want to run a SQL statement, you can use CurrentDb.Execute or something like this:

Private Sub Command12_Click()
    CurrentDb.Execute "DELETE FROM a_test"
End Sub
sgeddes
  • 62,311
  • 6
  • 61
  • 83
0

Instead of deleting all rows, you can drop the table and create it again. It's not very effective nor clean, but it'll reset the autonumber count.

eternay
  • 3,754
  • 2
  • 29
  • 27