How do you send parameters to an Access query?

  • Thread starter Thread starter Willam Roberts
  • Start date Start date
W

Willam Roberts

I really like using the stored queries in Access- the ones you make in query
designer then save. I know how to name parameters in those queries, but what
is a good way to send parameters to the query via variables via code from a
module's function or sub routine?

Thanks
William Roberts
 
You can set the Parameters in code (not "send them to the query"). It'd be
something like this (aircode, and I haven't used parameter queries from code
in a long, long time):

Dim db as DAO.Database
Dim qd as DAO.QueryDef
Dim rs as DAO.Recordset
Set db = CurrentDB
Set qd = db.QueryDefs("nameof yourquerydef")
qd.Parameters(0) = valueofyourfirstparameter
qd.Parameters(1) = valueofyoursecondparameter
Set rs = qd.OpenRecordset
.... actions processing recordset

That said, I found it to be much simpler in my view, to create a WHERE
clause and append it to the SQL of the saved query, saving the revised SQL
back into the query's .SQL property. I use this approach sometimes when I
specify a saved query without criteria as the RecordSource of a Report.

And, that, too, said, I find it even simpler to create the whole SQL
statement, including the WHERE clause, save it in a string and open a
recorset using the SQL, rather than a saved query. (In all but the simplest
cases, I create the Query in the Query Builder and copy the SQL into my
code. So, while it is not a saved query, I do take advantage of my built-in
assistant for writing SQL, the Query Builder.)

Larry Linson
Microsoft Access MVP
 
Back
Top