Run Existing Access Query From Excel

[EricG]

Hi Everyone,

I'm trying to do something that seems simple but making no headway. I guess
because it's Friday.

In my Access database, I have created two queries. The first is a "make
table" query that creates a new table and inserts some data into it. Call it
"qryMakeTable". The second is an append query that adds more data to the new
table. Call it "qryAppend".

What I need to do is:
1) At the push of a button ("Command1"), execute "qryMakeTable" from Excel
to create or re-create the table in Access, then
2) Execute "qryAppend" to add the secondary data to the table, and
3) Update an existing query on one of the worksheets in my workbook to
bring the data from the table into the worksheet.

The third part is easy, but I'm having trouble running the two pre-defined
queries from Excel. Does anyone have a good simple example for me today?

Thanks in advance,

Eric

 

[OssieMac]

Hi Eric,

What version of Access and Excel are you using?

 

[EricG]

Still using 2003 (SP3, I guess...)

Hi Eric,

What version of Access and Excel are you using?

 

[OssieMac]

Hi again Eric,

My apologies for not getting back to you earlier but I had some unforseen
delays after my previous post.

I am assuming that you have already set up these queries in Access.

Sub RunAccessQueries()
'Late binding method

Dim ac As Object
Const acExportDelim = 2 'Required with late binding
Const acNormal = 0 'Required with late binding

Dim strDbFullname As String
Dim strDocName As String

'Edit following line to match the path and name
'of the access database with the queries.
strDbFullname = ThisWorkbook.Path & "\" & "Test.mdb"

On Error Resume Next
'Try GetObject first in case Access is already open
Set ac = GetObject(, "Access.Application")

If Err.Number > 0 Then
'Error is returned by GetObject if Access not
'already open so use CreateObject
On Error GoTo 0 'Reset error trapping ASAP
Set ac = CreateObject("Access.Application")
End If

ac.OpenCurrentDatabase strDbFullname

'Following line only really required if Access
'already open and GetObject sets ac. However,
'can remain in code.
ac.DoCmd.SetWarnings False

strDocName = "qryMakeTable"
ac.DoCmd.OpenQuery strDocName, acNormal

strDocName = "qryAppend"
ac.DoCmd.OpenQuery strDocName, acNormal

ac.DoCmd.SetWarnings True

ac.Quit

Set ac = Nothing

End Sub

 

[OssieMac]

Hi again Eric,

My apologies for not getting back to you earlier but I had some unforseen
delays after my previous post.

I am assuming that you have already set up these queries in Access.

Sub RunAccessQueries()
'Late binding method

Dim ac As Object
Const acExportDelim = 2 'Required with late binding
Const acNormal = 0 'Required with late binding

Dim strDbFullname As String
Dim strDocName As String

'Edit following line to match the path and name
'of the access database with the queries.
strDbFullname = ThisWorkbook.Path & "\" & "Test.mdb"

On Error Resume Next
'Try GetObject first in case Access is already open
Set ac = GetObject(, "Access.Application")

If Err.Number > 0 Then
'Error is returned by GetObject if Access not
'already open so use CreateObject
On Error GoTo 0 'Reset error trapping ASAP
Set ac = CreateObject("Access.Application")
End If

ac.OpenCurrentDatabase strDbFullname

'Following line only really required if Access
'already open and GetObject sets ac. However,
'can remain in code.
ac.DoCmd.SetWarnings False

strDocName = "qryMakeTable"
ac.DoCmd.OpenQuery strDocName, acNormal

strDocName = "qryAppend"
ac.DoCmd.OpenQuery strDocName, acNormal

ac.DoCmd.SetWarnings True

ac.Quit

Set ac = Nothing

End Sub

 

[EricG]

OssieMac,

Thank you for your reply and inputs. Your code worked well, with a couple
of minor changes for convenience (I went back to early binding because the
rest of my code is that way, and I only do the ac.Quit if Access was not
already open).

The only issue I have, and it's minor, is that I was hoping to avoid opening
the Access database, because it takes quite a while (sometimes up to five
minutes) to open across the network, due to its size and complexity, and I
would have preferred to avoid that by doing something more direct. Do you
know if it's possible to do the same thing using ADO or DAO? I will do more
research and see if I can find an answer.

However, this works and I'll stick with it for now.

Thanks,

Eric

 

[EricG]

I think I've answered my own question. Here is what I came up with using
ADO. It seems to work and it's about 100 times faster!

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 = "DROP TABLE temp_tbl"
.CommandType = adCmdText
.ActiveConnection = cn
.Execute
'
.CommandText = "qryMakeTable"
.CommandType = adCmdStoredProc
.ActiveConnection = cn
.Execute
'
.CommandText = "qryAppend"
.CommandType = adCmdStoredProc
.ActiveConnection = cn
.Execute
'
End With
'
cn.Close
'
End Sub