excel - ExecuteComplete ADODB Connection event not fired with adAsyncExecute parameter

Question

I have a problem trying to catch the completion of a stored proc execute asynchronously.

Below my code VBA (in a class module named clsAsync):

Option Explicit

Private WithEvents cnn As ADODB.Connection


Private Sub cnn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
    MsgBox "Execution completed"
End Sub

Sub execSPAsync()
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    cnn.ConnectionString = "connection to my database SQLSEREVER"
    cnn.Open
    cnn.Execute "kp.sp_WaitFor", adExecuteNoRecords, adAsyncExecute
End Sub

This class is PublicNotCreatable.

To call the sub execSPAsync from a module I use the following code:

Sub testASYNC()
    Dim a As New clsAsync
    Call a.execSPAsync
End Sub

The stored procedure is very simple:

alter PROC kp.sp_WaitFor
AS

WAITFOR DELAY '00:00:05'

My problem is that the event ExecuteComplete is not fired at all, while if I comment the adAsynExecute parameter all is working fine. Any idea on how to solve my question?

Answer 1

I solved my problem replacing the calling code:

Sub testASYNC()
    Dim a As New clsAsync
    Call a.execSPAsync
End Sub

with this new code:

Private a As clsAsync

Sub testASYNC()
    Set a = New clsAsync
    Call a.execSPAsync
End Sub

In the async mode, the object "a" is no longer available at the end of the procedure (scope visibility issue).