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"
Private Sub CN_ConnectComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
Debug.Print Now() & " ConnectComplete"
Private Sub CN_Disconnect(adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
Debug.Print Now() & " Disconnected"
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
Public Sub DisConnect()
If CN.State = adStateOpen Then
If Not CN Is Nothing Then Set CN = Nothing
Now let us use this class in any code module.
Dim oCX As ClassXLApp
On Error GoTo ADO_ERROR
Set oCX = New ClassXLApp
'''Code using database values
If Err <> 0 Then
Debug.Assert Err = 0
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