Deleting a Database Table

You can delete a table programmatically by opening the ADOX Catalog object, accessing its Tables collection, and calling the Delete method to remove the specified table from the Tables collection.

The procedure in Hands-On 11-3 requires a parameter that specifies the name of the table you want to delete.

© Hands-On 11-3: Deleting a Table from a Database

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the Delete_Table procedure shown below.

Sub Delete_Table(strTblName As String) Dim cat As ADOX.Catalog

On Error GoTo ErrorHandler Set cat = New ADOX.Catalog cat.ActiveConnection = CurrentProject.Connection cat.Tables.Delete strTblName Set cat = Nothing Exit Sub

Part II

ErrorHandler:

MsgBox "Table '" & strTblName & _

"' cannot be deleted " & vbCrLf & _ "because it does not exist." Resume Next End Sub

3. To run the above procedure, type the following statement in the Immediate window and press Enter:

Delete_Table("tblFilters")

When you press Enter, Visual Basic deletes the specified table from the current database. If the table does not exist, an appropriate message is displayed.

0 0

Post a comment