Hey there, Error 3704 ("Operation is not allowed when the object is closed") is super common when working with ADO Recordsets in VBA—usually it means you’re trying to use CopyFromRecordset on a Recordset that’s either closed, empty, or never opened successfully. Let’s walk through how to fix this based on your code snippet.

First, let’s break down the key issues in your current code and how to address them:

1. Always Validate the Recordset State #

Before calling CopyFromRecordset, you need to confirm two things: the Recordset is actually open, and it contains data. If your SQL query returns no results, the Recordset might still be open but empty; if there was an error opening it, it could be closed entirely.

2. Complete Error Handling & Object Cleanup #

Your code starts an error handler, but it’s incomplete. You should ensure that even if an error occurs, you close any open connections and recordsets to avoid memory leaks, and explicitly handle cases where the Recordset didn’t open correctly.

Corrected Code Example #

Here’s a revised version of your function that addresses these gaps:

Option Explicit
Const conString As String = "Provider = sqloledb;Server=dbsrv;Database=xxxx;User Id=xxxx;Password=xxxx;"

Public Function execSql(ByVal sql As String, ByVal pasteRange As Range) As Integer
    On Error GoTo ErrorHandler
    
    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    Dim recordsAffected As Integer
    
    ' Initialize connection and recordset objects
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    ' Open the database connection
    cn.Open conString
    
    ' Open recordset with a static cursor (best for CopyFromRecordset)
    rs.Open sql, cn, adOpenStatic, adLockReadOnly
    
    ' Check if recordset is open and has data
    If Not rs Is Nothing And rs.State = adStateOpen Then
        If Not rs.EOF Then
            ' Paste records starting at the specified range
            pasteRange.CopyFromRecordset rs
            recordsAffected = rs.RecordCount
        Else
            ' No records returned from the query
            recordsAffected = 0
        End If
    Else
        ' Recordset failed to open (likely due to invalid SQL or connection issue)
        recordsAffected = -1
    End If

Cleanup:
    ' Always close and release objects to avoid memory leaks
    If Not rs Is Nothing Then
        If rs.State = adStateOpen Then rs.Close
        Set rs = Nothing
    End If
    If Not cn Is Nothing Then
        If cn.State = adStateOpen Then cn.Close
        Set cn = Nothing
    End If
    
    execSql = recordsAffected
    Exit Function

ErrorHandler:
    ' Display error details for debugging
    MsgBox "Error: " & Err.Number & " - " & Err.Description, vbCritical
    recordsAffected = -1
    Resume Cleanup
End Function

Key Improvements: #

• Added checks for rs.State = adStateOpen to ensure we only work with an open Recordset.
• Checked rs.EOF to handle empty result sets gracefully (no more trying to paste nothing).
• Added a dedicated Cleanup section that runs even if an error occurs, ensuring connections and recordsets are properly closed.
• Used adOpenStatic cursor type, which is recommended for CopyFromRecordset (some cursor types don’t play well with this method).

Troubleshooting Tips: #

• Test your SQL query: Run the exact query directly in SQL Server Management Studio to confirm it returns results. If it doesn’t, that’s why your Recordset is empty.
• Verify your connection string: Double-check the server name, database name, and credentials—any typo here will fail the connection and leave the Recordset closed.
• Enable ADO references: Make sure you’ve added a reference to "Microsoft ActiveX Data Objects x.x Library" (go to Tools > References in the VBA editor).

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