Dealing with Multi-Recordsets Returned by Compound SQL Statements

Everyone can deal with one recordset easily. However, if a SQL statement is like this: “SELECT a, b FROM Table1 COMPUTER SUM(a), SUM(b),” looping though the recordset will not return the aggregate value.

Actually, this SQL will return three recordsets—one by one. Either “EXECUTE of Command object” or “OPEN of Recordset object” will get only the first recordset. In our case, it is the “SELECT a, b FROM table1.”

We’ll use an important recordset method to retrieve the aggregate value. It is “NEXTRECORDSET.” Here is the syntax:

 Set NewRecordset = CurrentRecordset.NextRecordSet

This will populate the SUM(a) result to NewRecordset and clear the “SELECT” result, and so on. You can get the SUM(b) the same way.

After that, you still can use the NextRecordSet method, but no new recordset can be retrieved. You won’t get error message, however. Test for this case by verifying that the BOF and EOF properties are both True. Based on the documentation, this method is N/A to the client-side recordset object.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: