HOWTO: Open ADO Recordsets Asynchronously Using WithEvents

• ActiveX Data Objects (ADO), version 2.0
• Microsoft Visual Basic Enterprise Edition for Windows, version 6.0

SUMMARY

This article describes how to take advantage of asynchronous ActiveX Data Objects (ADO) functionality in Visual Basic 6.0. ADO 2.0 gives developers the ability to declare ADO object variables with the WithEvents keyword, which provides enhanced control over asynchronous operations.

MORE INFORMATION

The following example uses the Pubs sample database that ships with SQL Server to demonstrate the opening of asynchronous ADO recordsets from within Visual Basic 6.0. For developers who are not using Visual Basic for Applications within Visual Basic, the ADO Rosetta Stone whitepaper, titled "Implementing ADO with Various Development Languages" is available from the following URL:

   http://www.microsoft.com/data/

The example repeatedly opens and closes ADO recordsets until the user clicks the Cancel command button. The sample assumes that the Pubs sample database is used for this project and that the database contains the Publishers, Titles, and Authors tables.

To create the example, use the following steps:

Step-by-Step Example

1. Create a new Standard.exe Visual Basic 6.0 project.

2. From the Project menu, choose References and add the Microsoft ActiveX Data Objects 2.0 Library reference to the project.

3. Add two command buttons to Form1, the project's default form.

4. Cut and paste the following code into the project:

         Option Explicit
   

         Dim WithEvents con As ADODB.Connection
         Dim rst As New ADODB.Recordset
         Dim iExecutionCount As Integer
   

         Private Sub Form_Load()
   

            Dim sConnect As String
            Dim sServer As String
            Dim sUID As String
            Dim sPWD As String
   
            On Error GoTo EH
   
            ' Specify connection parameters.
   
            sServer = "<your server name>"
            sUID = "<your SQL Server user ID>"
            sPWD = "<your SQL Server user ID password>"
   
            sConnect = "Driver={SQL Server};Server=" & sServer & _
                    ";Database=Pubs;"
            sConnect = sConnect & "UID=" & sUID & ";"
            sConnect = sConnect & "PWD=" & sPWD & ";DSN='';"
            Set con = New ADODB.Connection
            con.CursorLocation = adUseClient
   
            ' Open the connection.
   
            con.Open sConnect
   
            command1.Caption = "Open Recordsets"
            command2.Caption = "Cancel"
            Exit Sub
   
          EH:
          MsgBox "Could not establish ODBC connection.", vbCritical + vbOKOnly
          Set con = Nothing
          End
   
         End Sub
   
        Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
   
         On Error Resume Next
         rst.Close
         Set rst = Nothing
         Set con = Nothing
   
         End Sub
   
         Private Sub Command1_Click()
   
         ' This code begins the process of repeatedly opening ADO recordsets.
   
         command1.Enabled = False
         command2.Enabled = True
         GetRecordsetData
   
         End Sub
   
         Private Sub Command2_Click()
   
         ' This code ends the process of repeatedly opening ADO recordsets.
   
         On Error Resume Next
         rst.Close
         MsgBox "The recordset was opened " & iExecutionCount & " time(s)."
         iExecutionCount = 0
         command1.Enabled = True
         command2.Enabled = False
   
         End Sub
   
         Private Sub GetRecordsetData()
   
         iExecutionCount = iExecutionCount + 1
         If rst.State <> adStateClosed Then
            rst.Close
         End If
         rst.Open _
            "Select * From Pubs..Publishers, Pubs..Titles, Pubs..Authors", _
            con, adOpenKeyset, adLockOptimistic, adAsyncExecute
   
         End Sub
   
         Private Sub con_ExecuteComplete(ByVal RecordsAffected As Long, _
          ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, _
          ByVal pCommand As ADODB.Command, _
          ByVal pRecordset As ADODB.Recordset, _
          ByVal pConnection As ADODB.Connection)
   
         ' When the ADO recordset has been populated with data, begin opening
         ' the next ADO recordset.
         GetRecordsetData
   
         End Sub
   
   

5. Change the values of sServer, sUID, and sPWD in the Form_Load event of Form1 to valid values for your SQL Server environment.

6. Run the project. Click the button labeled "Open Recordsets" to begin the asynchronous opening and closing of the ADO resultsets.

7. Click the button labeled Cancel to stop opening the ADO recordsets. A message box displays telling you how many ADO recordsets were opened.