ADO Recordset FetchComplete Event

[Marcster]

ADO Recordset has an event FetchComplete, I've put the following in a Class module but can't get the event to fire?!.

Private WithEvents CN As ADODB.Connection
Private WithEvents RST As ADODB.Recordset

The CN_ events like connect do work.

Anyone else tried using the ADO Recordset FetchComplete event?

[Bob Phillips]

You are somewhat light on detail. Have you read up on FetchComplete, yyou know what it does, when and how to use it?

[Marcster]

I can get the ADO FetchComplete event to fire in a UserForm as follows:


Option Explicit

Private WithEvents rst As ADODB.Recordset

Private Sub UserForm_Initialize()
Dim strCnn As String

strCnn = "Provider=SQLOLEDB;Data Source=SQLSERVERNAME;Initial Catalog=Pubs;Integrated Security=SSPI"

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open "Select * from publishers", strCnn, adOpenKeyset, adLockOptimistic, adAsyncFetch
rst.MoveLast
End Sub

Private Sub rst_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
MsgBox "rst_FetchComplete event"
End Sub

But I want to be able to see the event firing in a module/class module:

Module:

Sub Call_FL()
Dim oCl As Class1
Set oCl = New Class1
oCl.FL
End Sub


'In a Class Module named Class1:

Option Explicit

Private WithEvents rst As ADODB.Recordset

Public Sub FL()
Dim strCnn As String
strCnn = "Provider=SQLOLEDB;Data Source=SQLSERVERNAME;Initial Catalog=Pubs;Integrated Security=SSPI"

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open "Select * from publishers", strCnn, adOpenKeyset, adLockOptimistic, adAsyncFetch
rst.MoveLast
End Sub

Private Sub rst_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
MsgBox "rst_FetchComplete"
End Sub


The ADO Recordset Event rst_FetchComplete does not fire when I run the Macro Call_FL() in the module :-(.

Do not know what I am doing wrong...

Using Microsoft ActiveX Data Objects 2.6 Library : msado26.tlb : version 2.81.1132.0

[Marcster]

The above code works in a UserForm, i.e. rst_FetchComplete event fires and works in VB6 too.

Guess this is some kind of bug for Excel VBA????...


As I cannot get the event to fire in an Excel Class Module!...