I am having difficulties creating a macro that can run a data query in Access
and bring the results back into Excel. I hope someone can help me.
I have a database "db1.mdb", which has a couple of tables and a data query
"PerfHistQtr Query". What I am trying to do is to be able to enter a product
name in Excel, have the macro run the query in Access using the product name
as a criteria (lets say for Field1), and bring all results back into Excel.

Here is some code to allow you to run a query against an Access Database. It
returns a recordset based on the perameters of the select query you send it.
The record set is either connected or disconnected. Connected means that you
can write back to the databse with an update command. Disconnected gives you
a read only recordset. If it is a conneccted recordset make sure you close
the connections when you are done. You need to add a reference to "Microsoft
ActiveX Data Objects".

Private Const m_cDBLocation As String = "C:\DB1.mdb"

Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As String, _
ByVal strWhere As String, ByVal strOrderBy, ByVal blnConnected As Boolean)
As ADODB.Recordset
Dim strConnection As String

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
m_cDBLocation & ";"

Set RunQuery = New ADODB.Recordset
With RunQuery
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
End With

RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " &
strOrderBy, strConnection, , , adCmdText
If blnConnected = False Then Set RunQuery.ActiveConnection = Nothing
End Function

Sub Example()
Dim rst As ADODB.Recordset

Set rst = RunQuery("Select *", "From tblMyTable", "", ";", False)

End Sub

--
HTH...

Jim Thomlinson


"gottahavit" wrote:

I am having difficulties creating a macro that can run a data query in Access
and bring the results back into Excel. I hope someone can help me.
I have a database "db1.mdb", which has a couple of tables and a data query
"PerfHistQtr Query". What I am trying to do is to be able to enter a product
name in Excel, have the macro run the query in Access using the product name
as a criteria (lets say for Field1), and bring all results back into Excel.