Class Parameter (DAO VBA)

A Parameter object represents a value supplied to a query. The parameter is associated with a QueryDef object created from a parameter query. To use a Parameter class variable it first needs to be instantiated, for example

Dim prm as Parameter
Set prm = Workspaces(1).Databases(1).QueryDefs(1).Parameters(Item:=1)

Direction

expression .Direction

The setting or return value is a Long that can be set to one of the ParameterDirectionEnum constants. Use the Direction property to determine whether the parameter is an input parameter, output parameter, both, or the return value from the procedure. Some ODBC drivers do not provide information on the direction of parameters to a SELECT statement or procedure call. In these cases, it is necessary to set the direction prior to executing the query. For example, the following procedure returns a value from a stored procedure named "get_employees": {? = call get_employees} This call produces one parameter — the return value. You need to set the direction of this parameter to dbParamOutput or dbParamReturnValue before executing the QueryDef. You need to set all parameter directions except dbParamInput before accessing or setting the values of the parameters and before executing the QueryDef. You should use dbParamReturnValue for return values, but in cases where that option is not supported by the driver or the server, you can use dbParamOutput instead. The Microsoft SQL Server driver automatically sets the Direction property for all procedure parameters. Not all ODBC drivers can determine the direction of a query parameter. In these cases, it is necessary to set the direction prior to executing the query.

Sub DirectionX() 
 
 Dim wrkMain As Workspace 
 Dim conMain As Connection 
 Dim qdfTemp As QueryDef 
 Dim rstTemp As Recordset 
 Dim strSQL As String 
 Dim intLoop As Integer 
 
 ' Create ODBC workspace and open a connection to a 
 ' Microsoft SQL Server database. 
 Set wrkMain = CreateWorkspace("ODBCWorkspace", _ 
 "admin", "", dbUseODBC) 
 
 ' Note: The DSN referenced below must be configured to 
 ' use Microsoft Windows NT Authentication Mode to 
 ' authorize user access to the Microsoft SQL Server. 
 Set conMain = wrkMain.OpenConnection("Publishers", _ 
 dbDriverNoPrompt, False, _ 
 "ODBC;DATABASE=pubs;DSN=Publishers") 
 
 ' Set SQL string to call the stored procedure 
 ' getempsperjob. 
 strSQL = "{ call getempsperjob (?, ?) }" 
 
 Set qdfTemp = conMain.CreateQueryDef("", strSQL) 
 
 With qdfTemp 
 ' Indicate that the two query parameters will only 
 ' pass information to the stored procedure. 
 .Parameters(0).Direction = dbParamInput 
 .Parameters(1).Direction = dbParamInput 
 
 ' Assign initial parameter values. 
 .Parameters(0) = "0877" 
 .Parameters(1) = 0 
 
 Set rstTemp = .OpenRecordset() 
 
 With rstTemp 
 ' Loop through all valid values for the second 
 ' parameter. For each value, requery the recordset 
 ' to obtain the correct results and then print out 
 ' the contents of the recordset. 
 For intLoop = 1 To 14 
 qdfTemp.Parameters(1) = intLoop 
 .Requery 
 Debug.Print "Publisher = " & _ 
 qdfTemp.Parameters(0) & _ 
 ", job = " & intLoop 
 Do While Not .EOF 
 Debug.Print , .Fields(0), .Fields(1) 
 .MoveNext 
 Loop 
 Next intLoop 
 .Close 
 End With 
 
 End With 
 
 conMain.Close 
 wrkMain.Close 
 
End Sub 
 

Name

Returns the name of the specified object.

Dim prm As DAO.Parameter: Set prm = 
prm.Name

Properties

Returns the Properties collection of the specified object.

Dim prm As DAO.Parameter: Set prm = 
prm.Properties

Type

Sets or returns a value that indicates the operational type or data type of an object.

The setting or return value is a constant that indicates an operational or data type. For a Parameter object in a Microsoft Access workspace the property is read-only. For a Parameter object, the possible settings and return values are described in the following table.

Dim prm As DAO.Parameter: Set prm = 
prm.Type =

Value

Sets or returns the value of an variant. Read/write Variant.

The setting or return value is a Variant data type that evaluates to a value appropriate for the data type, as specified by the Type property of an object. Generally, the Value property is used to retrieve and alter data in Recordset objects. The Value property is the default property of the Field, Parameter, and Property objects. Therefore, you can set or return the value of one of these objects by referring to them directly instead of specifying the Value property. Trying to set or return the Value property in an inappropriate context (for example, the Value property of a Field object in the Fields collection of a TableDef object) will cause a trappable error.

Dim prm As DAO.Parameter: Set prm = 
prm.Value =