NextRecordset Method and DefaultCursorDriver Property Example

This example uses the NextRecordset method to view the data from a compound SELECT query. The DefaultCursorDriver property must be set to dbUseODBCCursor when executing such queries. The NextRecordset method will return True even if some or all of the SELECT statements return zero records; it will return False only after all the individual SQL clauses have been checked.

Sub NextRecordsetX()

   Dim wrkODBC As Workspace
   Dim conPubs As Connection
   Dim rstTemp As Recordset
   Dim intCount As Integer
   Dim booNext As Boolean

   ' Create ODBCDirect Workspace object and open Connection
   ' object. The DefaultCursorDriver setting is required 
   ' when using compound SQL statements.
   Set wrkODBC = CreateWorkspace("", _
      "admin", "", dbUseODBC)
   wrkODBC.DefaultCursorDriver = dbUseODBCCursor
   Set conPubs = wrkODBC.OpenConnection("Publishers", , , _
      "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")

   ' Construct compound SELECT statement.
   Set rstTemp = conPubs.OpenRecordset("SELECT * " & _
      "FROM authors; " & _
      "SELECT * FROM stores; " & _
      "SELECT * FROM jobs")

   ' Try printing results from each of the three SELECT 
   ' statements.
   booNext = True
   intCount = 1
   With rstTemp
      Do While booNext
         Debug.Print "Contents of recordset #" & intCount
         Do While Not .EOF
            Debug.Print , .Fields(0), .Fields(1)
            .MoveNext
         Loop
         booNext = .NextRecordset
         Debug.Print "  rstTemp.NextRecordset = " & _
            booNext
         intCount = intCount + 1
      Loop
   End With

   rstTemp.Close
   conPubs.Close
   wrkODBC.Close

End Sub

Another way to accomplish the same task would be to create a prepared statement containing the compound SQL statement. The CacheSize property of the QueryDef object must be set to 1, and the Recordset object must be forward-only and read-only.

Sub NextRecordsetX2()

   Dim wrkODBC As Workspace
   Dim conPubs As Connection
   Dim qdfTemp As QueryDef
   Dim rstTemp As Recordset
   Dim intCount As Integer
   Dim booNext As Boolean

   ' Create ODBCDirect Workspace object and open Connection
   ' object. The DefaultCursorDriver setting is required
   ' when using compound SQL statements.
   Set wrkODBC = CreateWorkspace("", _
      "admin", "", dbUseODBC)
   wrkODBC.DefaultCursorDriver = dbUseODBCCursor
   Set conPubs = wrkODBC.OpenConnection("Publishers", , , _
      "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")

   ' Create a temporary stored procedure with a compound
   ' SELECT statement.
   Set qdfTemp = conPubs.CreateQueryDef("", _
      "SELECT * FROM authors; " & _
      "SELECT * FROM stores; " & _
      "SELECT * FROM jobs")
   ' Set CacheSize and open Recordset object with arguments
   ' that will allow access to multiple recordsets.
   qdfTemp.CacheSize = 1
   Set rstTemp = qdfTemp.OpenRecordset(dbOpenForwardOnly, _
      dbReadOnly)

   ' Try printing results from each of the three SELECT
   ' statements.
   booNext = True
   intCount = 1
   With rstTemp
      Do While booNext
         Debug.Print "Contents of recordset #" & intCount
         Do While Not .EOF
            Debug.Print , .Fields(0), .Fields(1)
            .MoveNext
         Loop
         booNext = .NextRecordset
         Debug.Print "  rstTemp.NextRecordset = " & _
            booNext
         intCount = intCount + 1
      Loop
   End With

   rstTemp.Close
   qdfTemp.Close
   conPubs.Close
   wrkODBC.Close

End Sub