NextRecordset Method Example (VB)
This example uses the NextRecordset method to view the data in a recordset that uses a compound command statement made up of three separate SELECT statements.
'BeginNextRecordsetVB
'To integrate this code
'replace the data source and initial catalog values
'in the connection string
Public Sub Main()
On Error GoTo ErrorHandler
' connection and recordset variables
Dim rstCompound As ADODB.Recordset
Dim Cnxn As ADODB.Connection
Dim strCnxn As String
Dim SQLCompound As String
Dim intCount As Integer
' Open connection
Set Cnxn = New ADODB.Connection
strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
"Initial Catalog='Pubs';Integrated Security='SSPI';"
Cnxn.Open strCnxn
' Open compound recordset
Set rstCompound = New ADODB.Recordset
SQLCompound = "SELECT * FROM Authors; " & _
"SELECT * FROM stores; " & _
"SELECT * FROM jobs"
rstCompound.Open SQLCompound, Cnxn, adOpenStatic, adLockReadOnly, adCmdText
' Display results from each SELECT statement
intCount = 1
Do Until rstCompound Is Nothing
Debug.Print "Contents of recordset #" & intCount
Do Until rstCompound.EOF
Debug.Print , rstCompound.Fields(0), rstCompound.Fields(1)
rstCompound.MoveNext
Loop
Set rstCompound = rstCompound.NextRecordset
intCount = intCount + 1
Loop
' clean up
Cnxn.Close
Set rstCompound = Nothing
Set Cnxn = Nothing
Exit Sub
ErrorHandler:
' clean up
If Not rstCompound Is Nothing Then
If rstCompound.State = adStateOpen Then rstCompound.Close
End If
Set rstCompound = Nothing
If Not Cnxn Is Nothing Then
If Cnxn.State = adStateOpen Then Cnxn.Close
End If
Set Cnxn = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub
'EndNextRecordsetVB