Cancel Method Example

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

Public Sub CancelX()

   Dim cnn1 As ADODB.Connection
   Dim strCnn As String
   Dim strCmdChange As String
   Dim strCmdRestore As String
   Dim booChanged As Boolean
   
   ' Open a connection.
   Set cnn1 = New ADODB.Connection
   strCnn = "driver={SQL Server};server=srv;" & _
      "uid=sa;pwd=;database=pubs"
   cnn1.Open strCnn
   
   ' 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.
   cnn1.BeginTrans
   cnn1.Execute strCmdChange, , adRunAsync
   
   ' If the connection is not open, cancel the connection
   ' and roll back the transaction. Otherwise, commit the
   ' transaction.
   If Not (cnn1.State = adStateOpen) Then
      cnn1.Cancel
      cnn1.RollbackTrans
      booChanged = False
      MsgBox "Update canceled."
   Else
      cnn1.CommitTrans
      booChanged = True
      MsgBox "Update complete."
   End If
   
   ' If the change was made, restore the data
   ' because this is a demonstration.
   If booChanged Then
      cnn1.Execute strCmdRestore
      MsgBox "Data restored."
   End If
      
   cnn1.Close
   
End Sub