How Do I Return Multiple Recordsets from SQL Store

Question

I’ve created a pass-through query in Access which executes a stored procedure that searches for a string across all tables in my SQL database. The stored procedure on the SQL server runs as expected, returning multiple Recordsets that contain the value of my search string. However, when I double-click on the pass-through query in Access, in Datasheet View I see the results of only one Recordset. Since it appears that Access is not designed to handle multiple result sets, then how do I use VBA in Access to accomplish this?

exec sqlsp_searchalltables @Tablenames='',  @SearchStr='%motion%'

Answer 1

I'm not quite sure how you expected to "bind" your form to the multiple recordsets returned by the stored procedure, but as far as I know the only way to deal with SQL Server stored procedures that return multiple recordsets is to use ADODB.Recordset objects.

(Don't be misled by the "Recordset.NextRecordset Method (DAO)" article here. If you try that approach you will receive run-time error '3847': "ODBCDirect is no longer supported. Rewrite the code to use ADO instead of DAO.")

For example, I have a SQL Server stored procedure that returns two recordsets and I create a pass-through named [dbo_myMultiRsSp_1] to call it:

EXEC dbo.myMultiRsSp @id=1

If I open it in Datasheet View by double-clicking it I see the results of the first recordset.

If I want to process all of the recordsets in VBA I cannot use the pass-through query directly, but I can use its .Connect and .SQL properties as follows

Option Compare Database
Option Explicit

Sub MultiRsSpTest()
    Dim cdb As DAO.Database
    Dim con As ADODB.Connection, cmd As ADODB.Command
    Dim r1 As ADODB.Recordset, r2 As ADODB.Recordset

    Set cdb = CurrentDb
    Set con = New ADODB.Connection

    ' connect directly to the SQL Server 
    '    (by using the .Connect property of the pass-through query)
    con.Open Mid(cdb.QueryDefs("dbo_myMultiRsSp_1").Connect, 5)  ' omit "ODBC:" prefix
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = con
    cmd.CommandType = adCmdText
    cmd.CommandText = cdb.QueryDefs("dbo_myMultiRsSp_1").SQL

    Set r1 = cmd.Execute
    Debug.Print
    Debug.Print "First Recordset:"
    Do Until r1.EOF
        Debug.Print r1(0).Value
        r1.MoveNext
    Loop

    Set r2 = r1.NextRecordset
    Debug.Print
    Debug.Print "Second Recordset:"
    Do Until r2.EOF
        Debug.Print r2(0).Value
        r2.MoveNext
    Loop

    ' r1.Close  (happens implicitly)
    Set r1 = Nothing
    r2.Close
    Set r2 = Nothing
    Set cmd = Nothing
    Set con = Nothing
    Set cdb = Nothing
End Sub