With Microsoft Jet, you can specify one or more query parameters. The parameters are used to accept input values at run time from the user. This user-supplied data is then merged with a QueryDef object. Parameters are commonly used to supply selection criteria, but they can also be used to specify terms in an expression.
You declare parameters with the PARAMETERS declaration, which specifies the names and data types of the parameters. The following example takes an EmployeeID value as a parameter, which causes the query to return values for only the selected employee, and another parameter whose value will be multiplied by the employee’s current salary to calculate a proposed new salary. The first parameter, EmployeeIDSelect, is used as part of the selection criteria; the second parameter, SalaryIncreaseFactor, is used as part of an expression:
PARAMETERS EmployeeIDSelect Long, SalaryIncreaseFactor Single; SELECT Employees.LastName, Employees.FirstName, Employees.Salary, CCur(Employees.Salary*SalaryIncreaseFactor) AS NewSalary FROM Employees WHERE Employees.EmployeeID = EmployeeIDSelect;
How does your program supply the parameter values? If you’re working with Microsoft Access, you may be familiar with the way it prompts the user to supply missing parameter values.
This behavior isn’t built into Microsoft Jet, however. It’s a function performed by Microsoft Access, and then only when the parameter query is executed through the user interface. Microsoft Access prompts the user for parameter values for forms, reports, and datasheets based on parameter queries.
If you want to supply parameter values to an existing QueryDef object that’s being executed programmatically, you have to do so explicitly, as in the following example. Assume that you have saved the parameter query shown previously as a permanent QueryDef object. In this example, strDbPath
is the path to the NorthwindTables database, strQueryName
is the name of the query under which you have saved the preceding SQL statement, lngEmpID
is the Long value you supply for the EmployeeIDSelect parameter, and sngIncFact
is the Single value you supply for the SalaryIncreaseFactor parameter.
For example, to increase the salary for the first employee in the Employees table by 10 percent, you would supply a value of 1 for EmployeeIDSelect and a value of 1.1 for SalaryIncreaseFactor:
Dim dbs As Database Dim qdf As QueryDef Dim rst As Recordset Dim strMsg As String Set dbs = OpenDatabase(strDbPath) ' Return reference to parameter QueryDef object. Set qdf = dbs.QueryDefs(strQueryName) ' Supply values for parameters. qdf.Parameters("EmployeeIDSelect") = lngEmpID qdf.Parameters("SalaryIncreaseFactor") = sngIncFact ' Open recordset. Set rst = qdf.OpenRecordset() With rst If .RecordCount <> 0 Then MsgBox "The proposed new salary is: $" & !NewSalary ' Check whether recordset is updatable. If .Updatable Then ' Prompt to update salary. strMsg = "Would you like to update the salary for " _ & !FirstName & " " & !LastName & " from $" _ & !Salary & " to $" & !NewSalary & "?" ' Update salary field. If MsgBox(strMsg, vbYesNo) = vbYes Then .Edit !Salary = !NewSalary .Update End If End If End If End With
This method assumes that you know the names of the parameters at the time your code is written. One way to avoid the need to explicitly specify parameters is to iterate through all the parameters in the Parameters collection of the QueryDef object and prompt the user for values, thus emulating the behavior inherent in Microsoft Access. In the following example, strDbPath
is the path to the database, and strQueryName
is the name of the query:
Dim dbs As Database Dim qdf As QueryDef Dim rst As Recordset Dim prm As Parameter Set dbs = OpenDatabase(strDbPath) Set qdf = dbs.QueryDefs(strQueryName) For Each prm In qdf.Parameters prm.Value = InputBox("Enter parameter value: " & prm.Name) Next prm Set rst = qdf.OpenRecordset()
Although Microsoft Jet includes the PARAMETERS declaration in order for you to name and type your QueryDef object parameters explicitly, it actually treats any unrecognized term as a parameter. If you make a spelling error when specifying a field name, for example, Microsoft Jet treats the misspelled field name as a parameter.
It’s a good idea to get into the habit of specifying the names and data types of your parameters rather than relying on the implicit parameter behavior of Microsoft Jet.
You can also use the Microsoft Jet PARAMETERS declaration to create a temporary QueryDef object that prompts the user to supply parameter values when your code executes. For more information and an example, see “Temporary QueryDef Objects” in Chapter 3, “Data Definition and Integrity.”
Important You should not use a parameter query to search for Null values. A parameter of Null
will never find a Null record because no two Null values match. If you want to find records that contain fields with Null values, you can use a non-parameter SELECT query. For example, the following SQL statement returns a list of all customers that don’t have a fax number:
SELECT * FROM Customers WHERE Fax = Null;
See Also For a discussion of the various database object collections, including QueryDefs and Parameters, see Chapter 3, “Data Definition and Integrity.”