Processing Multiple Result Sets

SQL pass-through queries can return more than one result set. When you assign the result set to a Recordset object or display the results using a Microsoft Visual Basic Data control or a Microsoft Access datasheet, form, or report, only the first result set from the query is processed. To remedy this situation, you must use a make-table query, that is, a SELECT INTO SQL statement, to capture the additional result sets into Microsoft Jet tables.

When Microsoft Jet returns the result sets from your query, it writes the results into new tables. The names of these new tables are derived from the table name you specify in the make-table query with ascending integer values following the initial table name. For example, if your query returns two result sets to the Results table, the first table would be named Results and the second would be named Results1. The following code fragment creates and runs a query that returns two result sets. This function calls the PrintRecordset procedure to print the recordset to the Debug window.

Sub ProcessMultipleResultSets(strDbPath As String)
	Dim dbs As Database, qdf As QueryDef, rst As Recordset

	Set dbs = OpenDatabase(strDbPath)
	' Create saved QueryDef.
	Set qdf = dbs.CreateQueryDef("GetAuthorsTitles")

	' Set query properties.
	With qdf
		.Connect = "ODBC;DSN=Pubs;UID=sa;PWD=;DATABASE=pubs"
		.ReturnsRecords = True
		.SQL = "SELECT * FROM authors; SELECT * FROM titles;"
	End With

	' Execute SQL statement to create new tables.
	dbs.Execute "SELECT * INTO Results FROM GetAuthorsTitles"

	' Open recordset on first results table.
	Set rst = dbs.OpenRecordset("Results")
	' Call the PrintRecordset function to print the recordset.
	PrintRecordset rst
	Debug.Print

	' Open recordset on second results table.
	Set rst = dbs.OpenRecordset("Results1")
	' Call the PrintRecordset function to print the recordset.
	PrintRecordset rst
End Sub

Function PrintRecordset(rst As Recordset) As Boolean
	Dim fld As Field

	On Error GoTo Err_PrintRecordset

	With rst
		Do Until .EOF
			For Each fld In .Fields
				Debug.Print fld.Value
			Next fld
			Debug.Print
			.MoveNext
		Loop
	End With
	PrintRecordset = True

Exit_PrintRecordset:
	Exit Function

Err_PrintRecordset:
	MsgBox "Error: " & Err & vbCrLf & Err.Description
	PrintRecordset = False
	Resume Exit_PrintRecordset
End Function

The tables returned support all the normal table operations. Normally you would use these tables to browse the data returned and then delete them in preparation for the next time you run the SELECT INTO query. Note that you only need to create the GetAuthorsTitles query once. For this reason, you may choose to save the SELECT INTO query rather than having it embedded in code. Also, you must use "SELECT *" to have multiple tables returned — you can’t select specific fields, even if they occur in all the result sets.

Note The previous example uses a SELECT INTO statement to create temporary tables in a Microsoft Jet database. If you use a similar procedure that creates temporary tables on the server database instead, the ODBC connection to the server may hang. This happens if you use Microsoft SQL Server version 6.0 with the Select Into/Bulk Copy option set for the server database and the DSN that is used to connect to the database has the Generate stored procedure for prepared statements option set. To work around this problem, use the ODBC Data Source Administrator to modify the DSN and clear the Generate stored procedure for prepared statements option. This isn’t a problem when using a Microsoft SQL Server 6.5 database.

See Also When using an ODBCDirect Workspace object, you can use the NextRecordset method to loop through multiple result sets returned from your server. For more information, see “Retrieving Multiple Result Sets” later in this chapter.