In many situations, you’ll want a user or another program to provide parameters to your stored queries and Recordset objects. Microsoft Jet provides the means to do this. You first create a QueryDef object, specifying which parameters are to be provided by the end user. When you open a Recordset object based on the QueryDef object, the application opens a dialog box that prompts the user to enter a value, such as the criteria for a WHERE clause or the field on which to sort the selected records.
The following example creates a temporary query that returns the name and hire date of each employee hired after a certain date. Before running the query, the program calls the InputBox function to prompt the user for a threshold date. The names are then shown in the Debug window, starting with the most recent hire. In this example, strDbPath
is the path to the NorthwindTables database:
Dim dbs As Database, rst As Recordset Dim qdf As QueryDef Dim strSQL As String, strInput As String Set dbs = OpenDatabase(strDbPath) ' Create SQL statement with parameters. strSQL = "PARAMETERS BeginningDate DateTime; " & _ "SELECT FirstName, LastName, HireDate FROM Employees " & _ "WHERE HireDate >= BeginningDate " & _ "ORDER BY HireDate ASC;" ' Create temporary QueryDef. Set qdf = dbs.CreateQueryDef("", strSQL) ' Prompt user for input. strInput = InputBox("Enter the earliest hire date:") If Len(strInput) = 0 Then Exit Sub If Not IsDate(strInput) Then Exit Sub ' Set parameter value. qdf.Parameters("BeginningDate") = strInput ' Open recordset. Set rst = qdf.OpenRecordset() With rst If .BOF Then MsgBox "No employees hired after date: " & strInput Exit Sub End If Do While Not .EOF Debug.Print !FirstName & " " & !LastName & " was hired on: " & !HireDate .MoveNext Loop .Close End With
Most of the database maintenance tasks described in the rest of this chapter can be accomplished by using stored parameter queries.