sql - 如何从 Access 2010 中的 SQL 存储过程返回多个记录集

Question

我在 Access 中创建了一个传递查询，它执行一个存储过程，该过程在我的 SQL 数据库中的所有表中搜索字符串。 SQL 服务器上的存储过程按预期运行，返回多个包含我的搜索字符串值的记录集。但是，当我在 Access 中双击传递查询时，在数据 TableView 中我只看到一个记录集的结果。既然 Access 似乎不是为处理多个结果集而设计的，那么如何在 Access 中使用 VBA 来实现这一点呢？

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

Answer 1

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

(不要被“Recordset.NextRecordset 方法 (DAO)”文章 here 误导。如果您尝试该方法，您将收到运行时错误“3847”:“不再支持 ODBCDirect。重写代码以使用ADO 而不是 DAO。”)

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

EXEC dbo.myMultiRsSp @id=1

如果我通过双击它在数据 TableView 中打开它，我会看到第一个记录集的结果。

如果我想处理 VBA 中的所有记录集，我不能直接使用传递查询，但我可以使用它的 .Connect和 .SQL属性如下

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