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 = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
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, , adAsyncExecute
' do something else for a little while – this could be changed
For i = 1 To 10
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(cnn1.State And adStateExecuting) 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