Friday, December 05, 2008

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

1 comment:

  1. Anonymous6:33 AM

    Helpful example of using WithEvents on the Connection Obect and making calls.

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.