6

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?

Community
  • 1
  • 1
stexcec
  • 1,143
  • 1
  • 18
  • 34
  • Do you get the drop down on the right with all the events listed after you select the object, in your case cnn? – glh Apr 23 '13 at 11:12
  • What do you get if you handle the `WillExecute` event? Does this pop up a message box? Also you may need to be looking at the `FetchComplete` event as this is used when retrieving a large asynchronous set. – glh Apr 23 '13 at 11:35
  • @glh: If I handle a new event, WillExecute, this new one fires correctly. – stexcec Apr 23 '13 at 12:25
  • What about fetchcomplete? – glh Apr 23 '13 at 12:31
  • There is no FetchComplete event for the ADODB Connection, but only for Recordset object. – stexcec Apr 23 '13 at 12:35

1 Answers1

6

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).

stexcec
  • 1,143
  • 1
  • 18
  • 34