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

MsgBox "Tables have been truncated", vbInformation, "TABLES TRUNCATED"  

    Set TDF = Nothing
    Set DB = Nothing
    Exit Sub

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

  • Share this post on
comments powered by Disqus