Cancel Method Example (VB)

This example uses the Cancel method to cancel a command executing on a Connection object if the connection is busy.
'BeginCancelVB

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string
    
Public Sub CancelX()

    'recordset and connection variables
   Dim Cnxn As ADODB.Connection
   Dim strCnxn As String
   Dim strCmdChange As String
   Dim strCmdRestore As String
    'record variables
   Dim blnChanged As Boolean
   
   ' Open a connection
   Set Cnxn = New ADODB.Connection
   strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
   Cnxn.Open strCnxn
   
   ' Define command strings
   strCmdChange = "UPDATE titles SET type = 'self_help' WHERE type = 'psychology'"
   strCmdRestore = "UPDATE titles SET type = 'psychology' " & _
                     "WHERE type = 'self_help'"
   
   ' Begin a transaction, then execute a command asynchronously
   Cnxn.BeginTrans
   Cnxn.Execute strCmdChange, , adAsyncExecute
   ' do something else for a little while –
   ' use i = 1 to 32000 to allow completion
    Dim i As Integer
      For i = 1 To 1000
         i = i + i
         Debug.Print i
      Next i

   ' If the command has NOT completed, cancel the execute and
   ' roll back the transaction; otherwise, commit the transaction
   If CBool(Cnxn.State And adStateExecuting) Then
      Cnxn.Cancel
      Cnxn.RollbackTrans
      blnChanged = False
      MsgBox "Update canceled."
   Else
      Cnxn.CommitTrans
      blnChanged = True
      MsgBox "Update complete."
   End If
   
   ' If the change was made, restore the data
   ' because this is only a demo
   If blnChanged Then
      Cnxn.Execute strCmdRestore
      MsgBox "Data restored."
   End If
      
    ' clean up
   Cnxn.Close
   Set Cnxn = Nothing
 
End Sub
'EndCancelVB
Cancel Method Example (VB)

See Also
