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).
Syntax
expression .Direction
expression A variable that represents a Parameter object.
Remarks
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.
Example
This example uses the Direction property to configure the parameters of a query to an ODBC data source.
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