未使用 adAsyncExecute 参数触发 ExecuteComplete ADODB 连接事件 [英] ExecuteComplete ADODB Connection event not fired with adAsyncExecute parameter

问题描述

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

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

在我的代码 VBA 下方(在名为 clsAsync 的类模块中):

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

这个类是 PublicNotCreatable.

This class is PublicNotCreatable.

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

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

存储过程很简单:

alter PROC kp.sp_WaitFor
AS

WAITFOR DELAY '00:00:05'

我的问题是事件 ExecuteComplete 根本没有被触发，而如果我评论 adAsynExecute 参数一切正常.关于如何解决我的问题的任何想法?

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?

推荐答案

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

I solved my problem replacing the calling code:

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"在过程结束时不再可用(范围可见性问题).

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

这篇关于未使用 adAsyncExecute 参数触发 ExecuteComplete ADODB 连接事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！