How to delete the table in MS ACCESS database using Excel VBA code ?

Please add the reference to Microsoft ActiveX Data Objects 2.x Library in the Excel VBA Editor.

see my post on How to use VBA editor and add references to Excel VBA ?


Sub Delete_Test_Table ()

‘Define the variables to be used in the VBA code
Dim connectdb As String, pathdb As String, connObj As ADODB.Connection

‘Assigning the database name and the connection path to the pathdb variable
pathdb = Sheet1.Range(“A2”).Value

‘In the above case the reference is taken from Range A2 of sheet 1, while we can directly add the path in VBA code e.g.
‘pathdb = “C:\Users\Default\Desktop\Test.accdb”

‘Connection reference and path
connectdb = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & pathdb & “;”

‘Connecting to the database and creating new table
Set connObj = New ADODB.Connection

With connObj
.Open connectdb
.Execute “drop table TEST_TABLE”
End With

‘ Table with name “TEST_TABLE” will be deleted from the database

End Sub

Please note that this function will permanently delete the table and its content, which will be no more recoverable.

Please use this function with caution.

If you check out my other post regarding, How to create table in MS Access using Excel VBA, you will find that only the .Execute line is changed. Anything you enter withing the double quoted after .Execute “” will be executed on the database by the VBA code.

This concludes that, you can use the above connection method and procedure to execute different queries like UPDATE DATA, DELETE DATA, EXPORT DATA and more.

Thank you for reading. Happy Learning !

Please leave a reply if you like the article or if you need any help on excel

%d bloggers like this: