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.
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
[…] vba code. I trying to Delete a table code. I found an example online at the following site https://nandeshwar.info/2007/07/18/ac…cess-database/. But, this solution requires me to select DAO 3.x. When I try to select that reference, I getg […]
I’m confused about the following parts of your code:
‘If Left(tbldef.Name, 4) = Arr(i) Then
‘Don’t delete System or temporary tables
I need to delete all tables except for system tables and a few tables I created in access, for argument’s sake lets say I need to keep Table1, Table2 and Table3. The database contains also Table4 , Table5, etc.
What is the VBA code to perform this function?