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
Helpful example of using WithEvents on the Connection Obect and making calls.
ReplyDelete