Direction Property 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)
    Set conMain = wrkMain.OpenConnection("Publishers", _
        dbDriverNoPrompt, False, _
        "ODBC;DATABASE=pubs;UID=sa;PWD=;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