没有使用adAsyncExecute参数触发ADODB连接事件

Question

我在尝试捕捉异步执行的存储过程的完成时遇到问题。

下面我的代码VBA(在一个类模块命名为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

这个类是PublicNotCreatable

要从模块调用子execSPAsync，我使用以下代码:

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

存储过程非常简单:

alter PROC kp.sp_WaitFor
AS
WAITFOR DELAY '00:00:05'

我的问题是事件ExecuteComplete根本没有被触发，而如果我注释adasynexecexecute参数，一切都工作得很好。你知道怎么解决我的问题吗?

Answer 1

我解决了替换调用代码的问题:

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

的新代码:

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

在异步模式下，对象"a"在过程结束时不再可用(作用域可见性问题)。