Home

Triggering ADO Events

Download a copy of the project.

Making the ADO Events Project

This sample project demonstrates how to use the ADO 2.1 Recordset and Command objects as event sources. Basically, all you need to do is declare Recordset and Command object variables using the WithEvents keyword. Once you do that, events for the the object variables are available in the event dropdown in the code window.

  1. Create a DSN referring to the Northwind database. For information on creating DSNs, see these two MSDN Help topics:
  2. Open a new project in Visual Basic 6.0, and add a reference to the Microsoft ActiveX Data Objects 2.1 Library.
  3. Add a command button and a label to the form.
  4. Set the command button's Caption property to Events.
  5. Copy the following code into the form.

    Option Explicit

    'Declare the Connection and Recordset objects as event sources.
    Private WithEvents cnNWind As Connection
    Private WithEvents rsNWind As Recordset

    Private Sub Command1_Click()
        Dim cmNWind As ADODB.Command

        'Instantiate Command and Connection objects.
        Set cnNWind = New Connection
        Set cmNWind = New Command

        'Open method uses Northwind DSN.
        cnNWind.Open "northwind"

        'Attach connection to the Command object.
        Set cmNWind.ActiveConnection = cnNWind

        'Compile and execute the Command object.
        With cmNWind
            .CommandText = "SELECT productname FROM products"
            .Prepared = True
        End With

        Set rsNWind = cmNWind.Execute

        'Display results.
        Set Label1.DataSource = rsNWind
        Label1.DataField = "ProductName"

        'Cleanup
        cnNWind.Close
        Set cmNWind = Nothing
        Set cnNWind = Nothing
        Set rsNWind = Nothing

    End Sub

    Private Sub rsNWind_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
        MsgBox "rsNWind_FetchComplete"
    End Sub

    Private Sub cnNWind_ConnectComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
        MsgBox "cnNWind_ConnectComplete"
    End Sub

    Private Sub cnNWind_Disconnect(adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
        MsgBox "cnNWind_Disconnect"
    End Sub

    Private Sub cnNWind_InfoMessage(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
        MsgBox "Connection object attributes = " & cnNWind.Attributes
        Debug.Print cnNWind.Attributes
    End Sub
  6. Save the project, then run it.
Last changed 9/17/00