Access VBA: Delete tables from Access database

If you want to delete all or some of the tables from your Access database, you can use this DAO approach. You would need a reference to Microsoft DAO 3.x object library. As shown in the example, you can use an array to store the table names, which you want to keep or delete.

Sub DelteTbls()
Dim sTblNm As String
Dim db As Database, tbldef As DAO.TableDef
Dim i As Integer, Arr As Variant

On Error GoTo DelteTbls_Error
‘You can use an array if you want to delete or not delete specific tables
‘Arr = Array(“Table1″,”Table2″,”Table3″)

Set db = CurrentDb() ‘Set the database object

‘Set the warnings off to suppress messages
DoCmd.SetWarnings False

‘For i = 0 To UBound(Arr)
    For Each tbldef In db.TableDefs
        ‘here you can use equal to or not equal to delete or keep specific tables
        ‘If Left(tbldef.Name, 4) = Arr(i) Then
        ‘Don’t delete System or temporary tables
        If Left(tbldef.Name, 4) “MSys” And Left(tbldef.Name, 1) “~” Then
              Debug.Print tbldef.Name
              sTblNm = tbldef.Name
              ‘Delete table
              DoCmd.DeleteObject acTable, sTblNm
        End If
    Next tbldef
‘Next i

MsgBox “Done!”

On Error GoTo 0

SmoothExit_DelteTbls:
    Set db = Nothing
    DoCmd.SetWarnings True
    Exit Sub

DelteTbls_Error:
    MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in procedure DelteTbls”
    Resume SmoothExit_DelteTbls
End Sub

Trackbacks

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>