Running a Query from VBA...

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

The query has a parmeter. How do I get the parameter
into the query from VBA?

DoCmd.OpenQuery "MyQuery", something....

Thanks,
Mark
 
Dim qdf As DAO.QueryDef
For Each qdf in CurrentDb.QueryDefs
If qdf.Name = "MyQuery" Then Exit for
Next qdf
qdf.Parameters(1) = "MyParamValue"
qdf.Execute


Execute if you're running an action query or open a
recordset based on the querydef if its a select query.

Dim rst as DAO.Recordset
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
 
What do you want to do with the query? Just open the
query in datasheet for the user? Or scroll through it in
code:

Using DAO:


Dim dbs As DAO.Database
Dim qdf as DAO.QueryDef
Dim prm as DAO.Parameter
Dim rst As DAO.Recordset
Dim strValue as String
Set dbs=CurrentDB
Set qdf = dbs.QueryDefs("QueryName")
qdf.Parameter("ParameterName") = strValue
Set rst = qdf.OpenRecordset
Do until rst.EOF

'Code to act on records here
rst.movenext
loop
rst.close
set rst = nothing
set qdf = nothing
set dbs=nothing


Chris Nebinger
 
Its just a nested query, that ultimately creates a table
that is put exported to Excel.

Thanks for your notes.

Did not think it would be VBA involved.
 
Back
Top