E
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
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