Any SQL statement can include multiple SELECT statements or stored procedures that invoke one or more SELECT statements. Each SELECT statement generates a result set that must be processed by your code or discarded before the resources are released and the next result set is made available. Because you don’t necessarily know how many result sets will be generated by a stored procedure, your code must be prepared to process an unknown number of result sets. Note that when a stored procedure returns multiple result sets, none of the result sets can be updated.
You can use either client-side cursors or server-side cursors to retrieve multiple result sets. If you use client-side cursors, multiple result sets are returned no matter what type of Recordset object you open. If you use server-side cursors to retrieve multiple result sets, you must open a forward-only-type Recordset object.
Û To retrieve multiple result sets
The following example prints the values of each field for each record in each result set.
Function GetMultipleResults() Dim wrk As Workspace, rst As Recordset, cnn As Connection, qdf As QueryDef Dim fld As Field, strSQL As String, strConnect As String, fDone As Boolean ' Create ODBCDirect workspace. Set wrk = DBEngine.CreateWorkspace("ODBCDirect", "Admin", "", dbUseODBC) ' Create connection string. strConnect = "ODBC;DSN=Pubs;DATABASE=Pubs;UID=sa;PWD=;" ' Open connection. Set cnn = wrk.OpenConnection("", dbDriverNoPrompt, False, strConnect) ' Create SQL statement. strSQL = "SELECT au_lname, au_fname FROM Authors; SELECT title FROM Titles;" ' Set default cursor driver. wrk.DefaultCursorDriver = dbUseServerCursor ' Create temporary QueryDef object. Set qdf = cnn.CreateQueryDef("", strSQL) qdf.CacheSize = 1 ' Open recordset on QueryDef. Set rst = qdf.OpenRecordset(dbOpenForwardOnly) Do Until fDone = True ' Print values for each field in each record of recordset. While Not rst.EOF For Each fld In rst.Fields Debug.Print fld.Value Next fld rst.MoveNext Wend fDone = Not rst.NextRecordset() Loop rst.Close cnn.Close wrk.Close End Function