ADO Event Instantiation: Visual Basic

In order to handle ADO events in Microsoft® Visual Basic®, you must declare a module-level variable using the WithEvents keyword. The variable can be declared only as part of a class module and must be declared at the module level. This is not as restrictive as it seems, however, because Visual Basic Form objects are also classes. The simplest way to handle ADO events is to declare a variable using WithEvents. The following example handles the ConnectComplete event for a Connection object:

' BeginEventExampleVB02
Dim WithEvents connEvent As Connection
Attribute connEvent.VB_VarHelpID = -1

Private Sub Form_Load()
Dim strConn As String

   ' Create a new object with event
   ' handling enabled.
   strConn = "Provider=sqloledb;" & _
      "Data Source=MyServer;" & _
      "Initial Catalog=Northwind;" & _
      "Integrated Security=SSPI;"
   Set connEvent = New ADODB.Connection
   connEvent.Open strConn
End Sub

Private Sub connEvent_ConnectComplete(ByVal pError As ADODB.Error, _
    adStatus As ADODB.EventStatusEnum, _
    ByVal pConnection As ADODB.Connection)
Dim strMsg As String

   If adStatus = adStatusErrorsOccurred Then
      Select Case pError.Number
         Case adErrOperationCancelled
            ' The operation was cancelled in the
            ' Will event. Notify the user and exit.
            strMsg = "I'm sorry you can't connect right now." & vbCrLf
            strMsg = strMsg & "Click OK to exit."
            Unload Me
         Case Else
            strMsg = "Error " & Format(pError.Number) & vbCrLf
            strMsg = strMsg & pError.Description
            strMsg = strMsg & "Click OK to exit."
            Unload Me
      End Select
   End If
   frmWait.btnOK.Enabled = True
End Sub
' EndEventExampleVB02

The Connection object is declared at the Form level using the WithEvents keyword to enable event handling. The Form_Load event handler actually creates the object by assigning a new Connection object to connEvent and then opens the connection. Of course, a real application would do more processing in the Form_Load event handler than is shown here.