HOWTO: Open ADO Recordsets Asynchronously Using WithEvents

• ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2
• 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 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://msdn.microsoft.com/library/techart/msdn_adorosest.htm/

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 the asynchronous opening and closing of the ADO resultsets.



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

Additional query words:

Keywords : kbADO200 kbDatabase kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2
Version : WINDOWS:2.0,2.1 SP2,6.0
Platform : WINDOWS
Issue type : kbhowto