如何从函数返回记录集

Question

我正在 Excel VBA 中构建数据访问层，但在返回记录集时遇到问题。我的类中的 Execute() 函数肯定是从数据库中检索一行，但似乎没有返回任何内容。

以下函数包含在名为 DataAccessLayer 的类中。该类包含函数 Connect 和 Disconnect，它们处理打开和关闭连接。

Public Function Execute(ByVal sqlQuery As String) As ADODB.recordset
    Dim rs As ADODB.recordset
    Set rs = New ADODB.recordset
    Dim recordsAffected As Long

    ' Make sure we're connected to the database.
    If Connect Then
        Set command = New ADODB.command

        With command
            .ActiveConnection = connection
            .CommandText = sqlQuery
            .CommandType = adCmdText
        End With

        'Set rs = command.Execute(recordsAffected)
        'Set Execute = command.Execute(recordsAffected)
        rs.Open command.Execute(recordsAffected)
        rs.ActiveConnection = Nothing
        Set Execute = rs
        Set command = Nothing
        Call Disconnect
    End If
End Function

这是我在电子表格的单元格 A1 中使用的公共函数以进行测试。

Public Function Scott_Test()
    Dim Database As New DataAccessLayer
    'Dim rs As ADODB.recordset
    'Set rs = CreateObject("ADODB.Recordset")
    Set rs = New ADODB.recordset

    Set rs = Database.Execute("SELECT item_desc_1 FROM imitmidx_sql WHERE item_no = '11001'")
    'rs.Open Database.Execute("SELECT item_desc_1 FROM imitmidx_sql WHERE item_no = '11001'")
    'rs.Open

    ' This never displays.
    MsgBox rs.EOF

    If Not rs.EOF Then
        ' This is displaying #VALUE! in cell A1.
        Scott_Test = rs!item_desc_1
        rs.Close
    End If

    rs.ActiveConnection = Nothing
    Set rs = Nothing
End Function

我做错了什么？

Answer 1

问题在于设置 ActiveConnection = Nothing。以下代码有效：

Public Function Execute(ByVal sqlQuery As String) As ADODB.recordset
    Dim rs As ADODB.recordset
    Set rs = New ADODB.recordset
    Dim recordsAffected As Long

    ' Make sure we are connected to the database.
    If Connect Then
        Set command = New ADODB.command

        With command
            .ActiveConnection = connection
            .CommandText = sqlQuery
            .CommandType = adCmdText
        End With

        rs.Open command.Execute(recordsAffected)

        Set Execute = rs
        Set command = Nothing
        Call Disconnect
    End If
End Function

Answer 2

Set Execute = recordset

创建一个指向记录集的指针，退出函数时将其关闭。
这就是为什么它不能包含任何东西。

我也对您的变量名称感到犹豫，这些名称与可能的保留字（记录集）相同。我通常使用 rs 或 rsIn 或 rsWhateverYouWant...

Answer 3

正如帕特里克所提到的，记录集是一个指针。
调用者“Scott_Test”应该调用 recordset.Close。

执行方法不能调用 recordset.Close，但我相信保留 recordset.ActiveConnection = Nothing 是可以的