ADO events not firing.

[romanemul]

Hello boys,

This is my code placed in a Workbook module. But the events do not fire. Can anyone know what is going on ?

Private WithEvents dbConnection As ADODB.Connection
Private WithEvents dbrecordset As ADODB.Recordset


Private Sub dbConnection_ConnectComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
        
        MsgBox "connection complete" & adStatus & ""
End Sub


Private Sub dbconnection_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 "Finished"
    
End Sub


Private Sub dbconnection_InfoMessage(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)


    MsgBox "You are not connected" & adStatus & " & pError & "


End Sub


Private Sub dbConnection_WillExecute(Source As String, CursorType As ADODB.CursorTypeEnum, LockType As ADODB.LockTypeEnum, Options As Long, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
    MsgBox "yes"
End Sub


Private Sub dbrecordset_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
   
 MsgBox "F Complete"

End Sub


Private Sub dbrecordset_WillChangeField(ByVal cFields As Long, ByVal Fields As Variant, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

   'somecode
    
End Sub


Private Sub Workbook_Open()
    
    Dim dbConnection As ADODB.Connection
    Dim dbCommand As ADODB.Command
    Dim dbrecordset As ADODB.Recordset
    
If dbConnection Is Nothing Then
    
    Set dbConnection = New ADODB.Connection
    Set dbCommand = New ADODB.Command
    Set dbrecordset = New ADODB.Recordset
        
        dbConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=somefile.accdb;Persist Security Info=False;"
        dbConnection.Open (dbConnection.ConnectionString)
        dbCommand.ActiveConnection = dbConnection
        dbCommand.CommandType = adCmdStoredProc
        Set dbrecordset = dbConnection.Execute("SELECT first_name FROM Record")
        
Else
    
    MsgBox "Already connected"
            
End If
    
    
End Sub

Help will be appreciated.

Roman

 

[RoryA]

Remove these two lines from the Workbook_Open code:

    Dim dbConnection As ADODB.Connection
    Dim dbrecordset As ADODB.Recordset

 

[romanemul]

Thanks RoryA. You have saved me once again.

So the Withevents keyword has the same effect as dim statement ?

 

[RoryA]

No - Private works the same way as Dim here. WithEvents specifies that you want to be able to monitor the object's events.

 

[romanemul]

That never cross my mind. Thanks for clarifying.