Excel VBA to delete records in Access table and reset the Autonumber

[Laurence Lombard]

Being a newbie to Access I would like clarification on the following
Excel VBA code. The intention is to delete all records in Table1 in
MyDatabase.mdb and reset the autonumber.


Sub TableDeleteRecordsAndResetAutonumber()
Set db = OpenDatabase("C:\Users\MyDocuments\MyDatabase.mdb")
db.Execute "DELETE * FROM Table1" 'Note 1: This works
'DoCmd.runSQL "DELETE * FROM Table1" 'Note 2: gives error "Object
required"
'db.CompactDatabase 'NOTE3 : does not work
' Application.SetOption ("Auto Compact"), 0 'NOTE4 : Does not work
db.Close
Set db = Nothing 'NOTE5
End Sub

NOTE1,2: After much trial and error (1) works. Many posts on the
internet forums use code like (2), but this gives an error "Object
required". What must be done to get (2) to work. I don't understand the
significance of "DoCmd.runSQL"

NOTE 3,4: From what I gather the Compact Database method will reset the
autonumber, so I tried these variations, but they do not work. What must
the code look like. Is there another way to reset the autonumber using
Excel VBA?

NOTE 5: What does this statement do. Why is it necessary/recommended

Many Thanks

 

[Ben McClave]

Laurence,

This macro should work. It uses the "TransferDatabase" function to copy just the table structure with no data into a new table with "1" appended to the name. Then, the old table is deleted and the new table is renamed to remove the "1".

Note that this code will run against whatever Access considers to be the "Current" database. Thus, it would be a good idea to close all Access databases you may have open other than the one you wish to run this macro on prior to running it.

Hope this helps,

Ben

Sub CopyTable()
Dim strTable As String
Dim myDB As Database

'Plug in your table name here

strTable = "Table1"

'Now we check if there is a database open
On Error Resume Next
Set myDB = CurrentDb
If myDB Is Nothing Then 'Database not open, so exit
MsgBox "Please open the database prior to running macro"
Exit Sub
End If
On Error GoTo 0

'Since everything looks good, we will copy the table structure first
DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, _
acTable, strTable, strTable & "1", True
'Now we'll delete the old table
DoCmd.DeleteObject acTable, strTable
'Finally, we will now rename the new table to match the old one.
DoCmd.Rename strTable, acTable, strTable & "1"

End Sub