Tom’s Tutorials For Excel: Delete a Table in Access From Excel

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
Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
2 comments on “Tom’s Tutorials For Excel: Delete a Table in Access From Excel
  1. Rory Archibald says:

    No real need for the Command object – you can also just use:
    dbConnection.execute “drop table tabletest”

    Then close the connection.

    • TomU says:

      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

Leave a Reply

Your email address will not be published.

*