StillExecuting Property and Cancel Method Example

This example uses the StillExecuting property and the Cancel method to asynchronously open a Connection object.

Sub CancelConnectionX()

    Dim wrkMain As Workspace
    Dim conMain As Connection
    Dim sngTime As Single

    Set wrkMain = CreateWorkspace("ODBCWorkspace", _
        "admin", "", dbUseODBC)
    ' Open the connection asynchronously.
    Set conMain = wrkMain.OpenConnection("Publishers", _
        dbDriverNoPrompt + dbRunAsync, False, _
        "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")

    sngTime = Timer

    ' Wait five seconds.
    Do While Timer - sngTime < 5
    Loop

    ' If the connection has not been made, ask the user
    ' if she wants to keep waiting. If she does not, cancel
    ' the connection and exit the procedure.
    Do While conMain.StillExecuting

        If MsgBox("No connection yet--keep waiting?", _
                vbYesNo) = vbNo Then
            conMain.Cancel
            MsgBox "Connection cancelled!"
            wrkMain.Close
            Exit Sub
        End If

    Loop

    With conMain
        ' Use the Connection object conMain.
        .Close
    End With

    wrkMain.Close

End Sub

This example uses the StillExecuting property and the Cancel method to asynchronously execute a QueryDef object.

Sub CancelQueryDefX()

    Dim wrkMain As Workspace
    Dim conMain As Connection
    Dim qdfTemp As QueryDef
    Dim sngTime As Single

    Set wrkMain = CreateWorkspace("ODBCWorkspace", _
        "admin", "", dbUseODBC)
    Set conMain = wrkMain.OpenConnection("Publishers", _
        dbDriverNoPrompt, False, _
        "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")

    Set qdfTemp = conMain.CreateQueryDef("")

    With qdfTemp
        .SQL = "UPDATE roysched " & _
            "SET royalty = royalty * 2 " & _
            "WHERE title_id LIKE 'BU____' OR " & _
            "title_id LIKE 'PC____'"

        ' Execute the query asynchronously.
        .Execute dbRunAsync

        sngTime = Timer

        ' Wait five seconds.
        Do While Timer - sngTime < 5
        Loop

        ' If the query has not completed, ask the user if
        ' she wants to keep waiting. If she does not, cancel
        ' the query and exit the procedure.
        Do While .StillExecuting

            If MsgBox( _
                    "Query still running--keep waiting?", _
                    vbYesNo) = vbNo Then
                .Cancel
                MsgBox "Query cancelled!"
                Exit Do
            End If

        Loop

    End With

    conMain.Close
    wrkMain.Close

End Sub

This example uses the StillExecuting property and the Cancel method to asynchronously move to the last record of a Recordset object.

Sub CancelRecordsetX()

    Dim wrkMain As Workspace
    Dim conMain As Connection
    Dim rstTemp As Recordset
    Dim sngTime As Single

    Set wrkMain = CreateWorkspace("ODBCWorkspace", _
        "admin", "", dbUseODBC)
    Set conMain = wrkMain.OpenConnection("Publishers", _
        dbDriverNoPrompt, False, _
        "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")
    Set rstTemp = conMain.OpenRecordset( _
        "SELECT * FROM roysched", dbOpenDynaset)

    With rstTemp

        ' Call the MoveLast method asynchronously.
        .MoveLast dbRunAsync

        sngTime = Timer

        ' Wait five seconds.
        Do While Timer - sngTime < 5
        Loop

        ' If the MoveLast has not completed, ask the user if
        ' she wants to keep waiting. If she does not, cancel
        ' the MoveLast and exit the procedure.
        Do While .StillExecuting

            If MsgBox( _
                    "Not at last record yet--keep waiting?", _
                    vbYesNo) = vbNo Then
                .Cancel
                MsgBox "MoveLast cancelled!"
                conMain.Close
                wrkMain.Close
                Exit Sub
            End If

        Loop

        ' Use recordset.

        .Close

    End With

    conMain.Close
    wrkMain.Close

End Sub