How To Open ADO Recordsets Asynchronously Using WithEvents

This article was previously published under Q190988
This article describes how to take advantage of asynchronous ActiveX DataObjects (ADO) functionality in Visual Basic 6.0. ADO gives developersthe ability to declare ADO object variables with the WithEvents keyword, which provides enhanced control over asynchronous operations.
The following example uses the Pubs database that ships with SQLServer to demonstrate the opening of asynchronous ADO recordsets fromwithin Visual Basic 6.0.

The example repeatedly opens and closes ADO recordsets until the userselects Cancel. The sample assumes that the Pubs sampledatabase is used for this project and that the database contains thePublishers, 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 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 Open Recordsets to begin theasynchronous opening and closing of the ADO resultsets.
  7. Select Cancel to stop opening the ADO recordsets. Amessage box displays telling you how many ADO recordsets were opened.

Article ID: 190988 - Last Review: 03/02/2005 19:22:46 - Revision: 1.3

Microsoft ActiveX Data Objects 2.0, Microsoft ActiveX Data Objects 2.1 Service Pack 2, Microsoft ActiveX Data Objects 2.5, Microsoft ActiveX Data Objects 2.6, Microsoft ActiveX Data Objects 2.7, Microsoft Visual Basic Enterprise Edition for Windows 6.0

  • kbdatabase kbhowto KB190988