ADODB from Excel - Running a Query

[EricG]

I have the following code linked to a button on a worksheet in Excel. I am
trying to run two existing queries in my Access database. The first is a
"make table" query that creates a table and loads data into it from another
table in the database. The second query is an "append" query that adds new
data to the table the first query creates. When I run these queries manually
in Access, everything works.

When I run the code below in Excel, as soon as I get to the .Execute line
for the first query, I get an error message telling me that the table already
exists. It does exist, and I'd simply like to delete the existing version
and create a new one. How can I avoid the error message? Is there a command
I can use to delete the existing table before I run the queries?

Thanks in advance,

Eric


Sub RunAccessQueries_ADO()

Dim cn As ADODB.Connection
Dim cm As ADODB.Command

dbPath = "d:\data\mypath\"
dbName = "mydb.mdb"

Set cn = New ADODB.Connection
Set cm = New ADODB.Command

With cn
.CommandTimeout = 0
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & dbPath & dbName
.Open
End With

With cm
.CommandText = "qryMakeTable"
.CommandType = adCmdStoredProc
.ActiveConnection = cn
.Execute
'
.CommandText = "qryAppend"
.CommandType = adCmdStoredProc
.ActiveConnection = cn
.Execute
'
End With
'
cn.Close
'
End Sub

 

[EricG]

Never mind, I figured it out. Just use the "DROP TABLE" command before
trying to create the table.

Eric