Running parametised queries from code

We've all been there sometimes - you've got a parametised query which works fine, but you want to call it and supply the parameters from code. The most likely reason for doing this is that you want to run the query in a batch job or just without requiring user input.

Aside from the obvious comment (if you know the values you want to use, why use your query at all? Why not just embed some SQL in your code?), if you really want to use your query and keep things simple, here's how you do it from code. In this example, you've got a query called qryFilterStaffByTitle which takes a parameter to restrict staff records to those of certain job titles. In the query design grid, the prompt used for the parameter is [Please enter the required job title here]. Here's the equivalent code for running the query and supplying the value of "Manager" to the job title parameter:

Dim dbs As Database, rst As Recordset, qdfParam As QueryDef
Set dbs = DBEngine(0)(0)
Set qdfParam = dbs.QueryDefs("qryFilterStaffByTitle")
qdfParam![Please enter the required job title here] = "Manager"
Set rst = qdfParam.OpenRecordset()