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.
• 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