Tom’s Tutorials For Excel: Delete a Table in Access From Excel
Sometimes you use Excel to manipulate other Office applications such as Word, PowerPoint, and Access. This macro shows how to connect to an Access database and delete a particular table.
A reference to the Microsoft ActiveX Data Objects 2.8 Library is required for the code to run. Before attempting to run the macro, get into the VBE and from the menu, click Tools > References. Navigate to the reference for Microsoft ActiveX Data Objects 2.8 Library, select it and click OK.
Assuming…
• your database is named Database1.accdb
• the folder path it is in is C:\YourFilePath\
• the name of the table you want to delete is TableTest
• you are using version 2007 or 2010
… then this macro will do the job.
Sub DeleteAccessTable() Dim dbConnection As ADODB.Connection Dim dbCommand As ADODB.Command Dim dbFileName As String dbFileName = "C:\YourFilePath\Database1.accdb" Set dbConnection = New ADODB.Connection With dbConnection .Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFileName & _ ";Persist Security Info=False;" .Open dbFileName End With Set dbCommand = New ADODB.Command Set dbCommand.ActiveConnection = dbConnection dbCommand.CommandText = "DROP TABLE TableTest" dbCommand.Execute Set dbCommand = Nothing Set dbConnection = Nothing End Sub
No real need for the Command object – you can also just use:
dbConnection.execute “drop table tabletest”
Then close the connection.
Thanks very much indeed Rory. It reduced the macro by 5 lines as shown here and tested perfectly sans the dbConnection object.
Sub DeleteAccessTable()Dim dbConnection As ADODB.Connection
Dim dbFileName As String
dbFileName = "C:\YourFilePath\Database1.accdb"
Set dbConnection = New ADODB.Connection
With dbConnection
.Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFileName & _
";Persist Security Info=False;"
.Open dbFileName
End With
dbConnection.Execute "DROP TABLE TableTest"
Set dbConnection = Nothing
End Sub