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
Dim strMsg As String

Private Sub Form_Load()
    On Error GoTo ErrHandler:
    
    Dim strConn As String
    
    ' Create a new object with event
    ' handling enabled.
    strConn = "Provider='sqloledb';" & _
        "Data Source='MySqlServer';" & _
        "Initial Catalog='Northwind';" & _
        "Integrated Security='SSPI';"
    Set connEvent = New ADODB.Connection
    connEvent.Open strConn
    
    Exit Sub
    
ErrHandler:
    MsgBox strMsg
End Sub

Private Sub connEvent_ConnectComplete(ByVal pError As ADODB.Error, _
    adStatus As ADODB.EventStatusEnum, _
    ByVal pConnection As ADODB.Connection)
    
    If adStatus = adStatusErrorsOccurred Then
        If Not pError Is Nothing 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
        Else
            strMsg = "Error occured. Click OK to exit."
            Unload Me
        End If
    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.