A Parameter object represents a value supplied to a query. The parameter is associated with a QueryDef object created from a parameter query.
Remarks
Parameter objects allow you to change the arguments in a frequently run QueryDef object without having to recompile the query.
Using the properties of a Parameter object, you can set a query parameter that can be changed before the query is run. You can:
- Use the Name property to return the name of a parameter.
- Use the Value property to set or return the parameter values to be used in the query.
- Use the Type property to return the data type of the Parameter object.
- Use the Direction property to set or return whether the parameter is an input parameter, an output parameter, or both.
Properties
Name
| Description
|
|---|
|
| NOTE: ODBCDirect workspaces are not supported in Microsoft Access 2013. Use ADO if you want to access external data sources without using the Microsoft Access database engine.
Sets or returns a value that indicates whether a Parameter object represents an input parameter, an output parameter, both, or the return value from the procedure (ODBCDirect workspaces only). |
|
| Returns the name of the specified object. Read-only String.
|
|
| Returns the Properties collection of the specified object. Read-only. |
|
| Sets or returns a value that indicates the operational type or data type of an object. Read/write Integer.
|
|
| Sets or returns the value of an object. Read/write Variant.
|
Example
This example demonstrates Parameter objects and the Parameters collection by creating a temporary QueryDef and retrieving data based on changes made to the QueryDef object's Parameters. The ParametersChange procedure is required for this procedure to run.
Sub ParameterX()
Dim dbsNorthwind As Database
Dim qdfReport As QueryDef
Dim prmBegin As Parameter
Dim prmEnd As Parameter
Set dbsNorthwind = OpenDatabase("Northwind.mdb")
' Create temporary QueryDef object with two
' parameters.
Set qdfReport = dbsNorthwind.CreateQueryDef("", _
"PARAMETERS dteBegin DateTime, dteEnd DateTime; " & _
"SELECT EmployeeID, COUNT(OrderID) AS NumOrders " & _
"FROM Orders WHERE ShippedDate BETWEEN " & _
"[dteBegin] AND [dteEnd] GROUP BY EmployeeID " & _
"ORDER BY EmployeeID")
Set prmBegin = qdfReport.Parameters!dteBegin
Set prmEnd = qdfReport.Parameters!dteEnd
' Print report using specified parameter values.
ParametersChange qdfReport, prmBegin, #1/1/95#, _
prmEnd, #6/30/95#
ParametersChange qdfReport, prmBegin, #7/1/95#, _
prmEnd, #12/31/95#
dbsNorthwind.Close
End Sub
Sub ParametersChange(qdfTemp As QueryDef, _
prmFirst As Parameter, dteFirst As Date, _
prmLast As Parameter, dteLast As Date)
' Report function for ParameterX.
Dim rstTemp As Recordset
Dim fldLoop As Field
' Set parameter values and open recordset from
' temporary QueryDef object.
prmFirst = dteFirst
prmLast = dteLast
Set rstTemp = _
qdfTemp.OpenRecordset(dbOpenForwardOnly)
Debug.Print "Period " & dteFirst & " to " & dteLast
' Enumerate recordset.
Do While Not rstTemp.EOF
' Enumerate Fields collection of recordset.
For Each fldLoop In rstTemp.Fields
Debug.Print " - " & fldLoop.Name & " = " & fldLoop;
Next fldLoop
Debug.Print
rstTemp.MoveNext
Loop
rstTemp.Close
End Sub