How to delete an access table from Excel?

[peter_z]

Hey guys, does anyone have any code to delete an access table from excel?

Thanks in advance for your help.

 

[Tom Urtis]

What version of office are you using...the database engine connection provider is jet in pre 2007 and ace in > = version 2007.

 

[peter_z]

I'm using 2007.

 

[Tom Urtis]

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

and

the folder path it is in is C:\YourFilePath\

and

the name of the table you want to delete is TableTest

and

you are using version 2007 or 2010

then

this macro worked for me:

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

 

[peter_z]

Wow awesome thank you so much, that works great! Much appreciated.

Just wondering how would I get the Environ("Username") code to work with this?

i.e something like this:

Sub DeleteAccessTable()
Dim dbConnection As ADODB.Connection
Dim dbCommand As ADODB.Command
Dim dbFileName As String
Dim StrUser As String
StrUser = Environ("Username")
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" & StrUser
dbCommand.Execute
Set dbCommand = Nothing
Set dbConnection = Nothing
Debug.Print StrUser
End Sub

 

[Tom Urtis]

I guess I'm not following you in the sense that I don't understand what good that information does in the macro. If you want to know the username, could you not just have a message box at the end of the macro that says the table has been deleted by the username, quasi air example:

MsgBox "The table has been deleted by " & Environ("Username")

Keep in mind, environ(username) deals with the network user name, whereas the name displayed in the Options dialog is derived by Application.Username.

Sorry to be redundant with this final comment, but I don't understand what you are after, seeing as the person who deletes the table, that is, the person who executes the macro, knows who they are.

 

[peter_z]

Never mind mate, I think I have had too much coding for one day just forgot the space in between.

dbCommand.CommandText = "DROP TABLE" & " " & StrUser

Thanks a lot for your help!

 

[peter_z]

I guess I'm not following you in the sense that I don't understand what good that information does in the macro. If you want to know the username, could you not just have a message box at the end of the macro that says the table has been deleted by the username, example:

MsgBox "The table has been deleted by " & Environ("Username")

Keep in mind, environ(username) deals with the network user name, wheeras the name displayed in the Options dialog is derived by Application.Username.

Sorry to be redundant with this final comment, but I don't understand what you are after, seeing as the person who deletes the table, that is, the person who executes the macro, knows who they are.

This is all part of a much larger project where each user saves a specific body of text via create table VBA into access. The saved file is in their own network name.

I was having trouble writing a create table code in excel vba that would overite the created table hence the need to figure out how to delete the table instead.