如何在Access 2010中从SQL存储过程返回多个记录集 [英] How Do I Return Multiple Recordsets from SQL Stored Procedure in Access 2010

问题描述

我在Access中创建了一个传递查询，该查询执行一个存储过程，该过程在SQL数据库的所有表中搜索字符串. SQL Server上的存储过程按预期运行，返回包含我的搜索字符串值的多个Recordset.但是，当我双击Access中的传递查询时，在数据表视图中，我只能看到一个Recordset的结果.由于看来Access并非旨在处理多个结果集，那么如何在Access中使用VBA来完成此任务?

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%'

推荐答案

我不太确定您希望如何将表单绑定"到存储过程返回的多个记录集，但据我所知处理返回多个记录集的SQL Server存储过程的一种方法是使用ADODB.Recordset对象.

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.

(不要被"Recordset.NextRecordset方法(DAO)"文章所误导

(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.")

例如，我有一个SQL Server存储过程，该过程返回两个记录集，并创建一个名为[dbo_myMultiRsSp_1]的传递来调用它:

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.

如果要处理VBA中的所有记录集，则不能直接使用传递查询，但可以按如下方式使用其.Connect和.SQL属性

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

这篇关于如何在Access 2010中从SQL存储过程返回多个记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！