How to use Connection events in ADO (VBA)

ADO Connection is associated with some interesting events. To consume the events, create a class and declare the Connection object

I have created a class called ClassXLApp and have created an object for Connection.

Private WithEvents CN As ADODB.Connection

WithEvents Keyword that specifies that varname is an object variable used to respond to events triggered by an ActiveX object. WithEvents is valid only in class modules. You can declare as many individual variables as you like using WithEvents, but you can't create arrays with WithEvents. You can't use New with WithEvents.

Select the Object name (CN here) in the Object Box. The Procedures/Events Box lists all the events recognized by Visual Basic for a form or control displayed in the Object box and will display all the events associated with connection

Click on any event to write the code associated with in the code window.

For example, let us use the following events

Private Sub CN_WillConnect(ConnectionString As String, UserID As String, Password As String, Options As Long, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)

Debug.Print Now() & " Will connect"

End Sub

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

Debug.Print Now() & " ConnectComplete"

End Sub

Private Sub CN_Disconnect(adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)

Debug.Print Now() & " Disconnected"

End Sub

Apart from the above three events, we can have two methods – one to connect to the database and one to disconnect from the database

Public Sub Connect2DB()

Set CN = New ADODB.Connection

CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\db1.mdb;Persist Security Info=False"

CN.ConnectionTimeout = 40

CN.Open

End Sub

Public Sub DisConnect()

If CN.State = adStateOpen Then

CN.Close

End If

If Not CN Is Nothing Then Set CN = Nothing

End Sub

Now let us use this class in any code module.

Sub ADODB_Connection_EXample()

Dim oCX As ClassXLApp

On Error GoTo ADO_ERROR

Set oCX = New ClassXLApp

oCX.Connect2DB

'''Code using database values

oCX.DisConnect

ADO_ERROR:

If Err <> 0 Then

Debug.Assert Err = 0

MsgBox Err.Description

Resume Next

End If

End Sub

We have created a connection and disconnected it. This will be fire the events in following order

12/4/2008 5:31:03 PM Will connect

12/4/2008 5:31:03 PM ConnectComplete

12/4/2008 5:31:03 PM Disconnected