Microsoft KB Archive/190988

= How To Open ADO Recordsets Asynchronously Using WithEvents =

Article ID: 190988

Article Last Modified on 3/2/2005

-

APPLIES TO


 * 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 6.0 Enterprise Edition

-



This article was previously published under Q190988



SUMMARY
This article describes how to take advantage of asynchronous ActiveX Data Objects (ADO) functionality in Visual Basic 6.0. ADO 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.

The example repeatedly opens and closes ADO recordsets until the user selects Cancel. 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
 Create a new Standard.exe Visual Basic 6.0 project. From the Project menu, choose References and add the Microsoft ActiveX Data Objects Library reference to the project. Add two command buttons to Form1, the project's default form.  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 = " " sUID = "" sPWD = ""

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  Change the values of sServer, sUID, and sPWD in the Form_Load event of Form1 to valid values for your SQL Server environment. Run the project. Click Open Recordsets to begin the asynchronous opening and closing of the ADO resultsets.</li> Select Cancel to stop opening the ADO recordsets. A message box displays telling you how many ADO recordsets were opened.</li></ol>

Keywords: kbhowto kbdatabase KB190988

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.