Excel VBA调用返回Recordset函数报对象已关闭错误如何解决？

问题根因 #

• 最直接的报错原因：你在getSQLData函数中将Recordset赋值给返回值后，立刻执行了rs.Close关闭记录集，同时还关闭了数据库连接，导致返回到SearchSQL过程的Recordset对象已经是被销毁的关闭状态，自然触发“对象已关闭”错误。你函数里的测试代码Sheets(1).Range("A2").CopyFromRecordset rs是在关闭rs之前运行的，所以单独测试函数时看起来正常，但返回出去的对象已经失效。
• RecordCount返回-1的原因：直接用Connection.Execute返回的Recordset默认是仅向前、只读游标，这类游标本身RecordCount属性固定返回-1，而且必须依赖数据库连接保持打开状态才能正常读取数据，你提前关闭连接后记录集直接失效。
• 隐藏bug：空查询结果时你返回了Null，如果查询无结果，SearchSQL里调用CopyFromRecordset接收Null会直接触发类型不匹配错误。

修正方案 #

1. 重写getSQLData函数 #

改用客户端游标让记录集脱离连接也能独立使用，不要在函数内部关闭返回的记录集，空结果也返回合法的Recordset对象而非Null：

Public Function getSQLData(query As String) As ADODB.Recordset
    Dim c As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim connectionstring As String

    connectionstring = "Provider=SQLNCLI11;Data Source=Skynet-SQL\Skynet;integrated security=SSPI;Persist Security Info=false;initial catalog=SQL_Team_MGMT"

    Set c = New ADODB.Connection
    c.Open connectionstring
    
    Set rs = New ADODB.Recordset
    ' 启用客户端游标，记录集可以脱离连接独立存在
    rs.CursorLocation = adUseClient
    rs.Open query, c, adOpenStatic, adLockReadOnly
    
    ' 断开记录集和连接的绑定
    Set rs.ActiveConnection = Nothing
    
    ' 现在可以安全关闭连接
    If CBool(c.State And adStateOpen) Then c.Close
    Set c = Nothing
    
    ' 统一返回记录集对象，避免Null值报错
    Set getSQLData = rs
End Function

2. 调整SearchSQL调用逻辑 #

先判断返回的记录集是否有数据再执行写入，用完再手动释放记录集：

Public Sub SearchSQL(frm As IForm)
    Dim rs As ADODB.Recordset
    Dim query As String

    If (frm.dialogResult = vbOK) Then
        Sheets(1).Range("A2:E1000").Clear
        If (frm.searchBox = "*" Or frm.searchBox = "") Then
            query = "set nocount on;Select server_name from inventory.servers"
        Else
            ' 替换单引号避免SQL语法错误，有条件建议改用参数查询彻底避免注入风险
            query = "set nocount on;Select server_name from inventory.servers where server_Name like '" & Replace(frm.searchBox, "'", "''") & "%';"
        End If
        
        Set rs = getSQLData(query)
        ' 先判断非空再写入
        If Not rs.EOF Then
            Sheets(1).Range("A2").CopyFromRecordset rs
        End If
        ' 调用方用完再关闭释放记录集
        rs.Close
        Set rs = Nothing
    End If
End Sub

注意事项 #

如果没有提前引用Microsoft ActiveX Data Objects库，代码里的常量adUseClient、adOpenStatic、adLockReadOnly会报错，可以直接替换为对应数值：adUseClient=3、adOpenStatic=3、adLockReadOnly=1。

内容的提问来源于stack exchange，提问作者Purclot