Getting Access Query Data Into Excel Using VBA

[Goofy]

I have an access database which has some queries in it, they have each have
two parameters in them for year and week_no.

I need in VBA to be able to call a macro to open those queries haviong
passed the parameter values to them.

Can anyone offers sim simple code to do this or point me at an appropriate
reference.

Thanks In Advance

 

[Guest]

Your subject title indicates you need help to export to Excel....but your
message indicates you want help getting Criteria of a Query. Is the value of
the criteria that you need for the Query found in Excel or in Access?....

 

[Goofy]

Here is my problem.

I am trying to query a query held in Access 2003 from Microsoft Excel. I
have done this successfully using VBA. The problem is that I dont want the
access query to contain ALL the data so I need to use criteria ( Where
Clause ). This is ok except for the fact that the ODBC Driver returns an
error telling me it requires a number of parameters.

Any ideas on how to get around this

 

[Brendan Reynolds]

Example parameter query ...

SELECT Table1.*
FROM Table1
WHERE (((Table1.TestID)=[Which ID?]));

Example code ...

Public Sub RecordsetFromParamQuery()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim fld As DAO.Field

Set db = CurrentDb
Set qdf = db.QueryDefs("qryTest")
qdf.Parameters("[Which ID?]") = InputBox("Which ID?")
Set rst = qdf.OpenRecordset()
For Each fld In rst.Fields
Debug.Print fld.Name, fld.Value
Next fld
rst.Close

End Sub

Result in Immediate Window ...

recordsetfromparamquery
TestID 2
TimeIn 09:30:00
TimeOut 16:30:00
Pl LName Null

CurrentDb is Access-specific, so if you're executing the code from the Excel
end, you'll need to use OpenDatabase rather then CurrentDb to get a
reference to your database.

 

[Goofy]

Thanks for your reply. Unfortunately, this is not really the answer to my
question. What you have done here is to use a form as the datasource for the
WHERE clause. In my case I am using VBA In Excel to query the data in a
'Query' in the access database. VIA and ODBC driver.

Example parameter query ...

SELECT Table1.*
FROM Table1
WHERE (((Table1.TestID)=[Which ID?]));

Example code ...

Public Sub RecordsetFromParamQuery()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim fld As DAO.Field

Set db = CurrentDb
Set qdf = db.QueryDefs("qryTest")
qdf.Parameters("[Which ID?]") = InputBox("Which ID?")
Set rst = qdf.OpenRecordset()
For Each fld In rst.Fields
Debug.Print fld.Name, fld.Value
Next fld
rst.Close

End Sub

Result in Immediate Window ...

recordsetfromparamquery
TestID 2
TimeIn 09:30:00
TimeOut 16:30:00
Pl LName Null

CurrentDb is Access-specific, so if you're executing the code from the
Excel end, you'll need to use OpenDatabase rather then CurrentDb to get a
reference to your database.

--
Brendan Reynolds
Access MVP

Here is my problem.

I am trying to query a query held in Access 2003 from Microsoft Excel. I
have done this successfully using VBA. The problem is that I dont want
the access query to contain ALL the data so I need to use criteria (
Where Clause ). This is ok except for the fact that the ODBC Driver
returns an error telling me it requires a number of parameters.

Any ideas on how to get around this