If you need to empty/truncate all user data from an MS Access database, the following VB script should do the job:
Public Sub TruncateTables()
On Error GoTo Error_TruncateTables
Dim DB As DAO.Database
Dim TDF As DAO.TableDef
Dim strSQL_DELETE As String
Set DB = CurrentDb()
For Each TDF In DB.TableDefs
If Left(TDF.Name, 4) <> "MSys" Then
If Left(TDF.Name, 1) <> "~" Then
strSQL_DELETE = "DELETE FROM " & TDF.Name & ";"
DB.Execute strSQL_DELETE
End If
End If
Next
MsgBox "Tables have been truncated", vbInformation, "TABLES TRUNCATED"
DB.Close
Exit_Error_TruncateTables:
Set TDF = Nothing
Set DB = Nothing
Exit Sub
Error_TruncateTables:
Select Case Err.Number
Case 3376
Resume Next 'Ignore error if table not found
Case 3270 'Property Not Found
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Error_TruncateTables
End Select
End Sub
Happy coding...