There are 3 approaches that come to mind for deleting a table in Microsoft Access:
DoCmd DeleteObject Method
DoCmd.DeleteObject acTable, "YourTableName"
SQL DROP Statement
Dim db As DAO.Database
Set db = CurrentDb
db.Execute "DROP TABLE YourTableName;", dbFailOnError
TableDefs Delete Method
Dim db As DAO.Database
Set db = CurrentDb
db.TableDefs.Delete "YourTableName"
RefreshDatabaseWindow
RefreshDatabaseWindow does as its name implies and will update the navigation pane’s table listing to reflect the deletion. If you omit this command the table will remain in the listing until you reopen that database.
Relationships!
Do keep in mind you can’t delete tables involved in relationships. You must first delete their relationships prior to attempting to delete the table itself. For that be sure to check out
MS Access – VBA – Delete Table Relationships
Sometimes, say when you wish to delete a table, you may need to delete the relationships associated with a given table. Now I’ve seen code to delete all the database relationships, but never for just a specific table. Yesterday, while trying to help in a forum I created the following procedure that does that; delete…
Making It Into A Function
As with most coding, basic built-in functions it is often best to build your own procedure around them to trap common error and return a Boolean value to indicate whether or not the process executed successfully or not.
Below is a reusable function which illustrates exactly that for the DeleteObject Method.
Function DelTbl(strTable As String) As Boolean
On Error GoTo Error_Handler
DoCmd.SetWarnings False 'Disable prompts to confirm deletion
DoCmd.DeleteObject acTable, strTable
DelTbl = True
Error_Handler_Exit:
On Error Resume Next
DoCmd.SetWarnings True 'Reenable prompts
Exit Function
Error_Handler:
If Err.Number = 7874 Then
'Table does not exist
Resume Next
Else
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: DelTbl" & vbCrLf & "Error Description: " & _
Err.Description, vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End If
End Function
Below is a reusable function which illustrates exactly that for the TableDefsDelete Method.
Sub DelTbl(strTable As String)
On Error GoTo Error_Handler
Dim db As DAO.Database
Set db = CurrentDb
db.TableDefs.Delete strTable
RefreshDatabaseWindow
DelTbl = True
Error_Handler_Exit:
On Error Resume Next
If Not db Is Nothing Then Set db = Nothing
Exit Sub
Error_Handler:
If Err.Number = 3265 Then
'Table does not exist
Resume Next
Else
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: DelTbl" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End If
End Sub
.gif)