Home » Dealing with Multi-Recordsets Returned by Compound SQL Statements

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.

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.