Deleting all data from an MS Access database

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