Why Does My Excel-VBA Code Get a '3704: Operation is not allowed when object is closed' Error When Copying Data from a SQL Recordset?

Error Handling in Excel-VBA for SQL Database Access

While accessing a SQL database from Excel-VBA, a common issue arises when attempting to copy data from a recordset to an Excel sheet, resulting in runtime error 3704: "Operation is not allowed when object is closed."

Root Cause:
The error message suggests that the recordset (objMyRecordset) referenced in the "CopyFromRecordset" method has been closed. This may occur if the connection to the database is lost or if the recordset is explicitly closed prematurely.

Solution:
To address this issue, ensure that the database connection is stable and the recordset remains open throughout the data-retrieval process. Here's a revised VBA code:

Sub GetDataFromADO()
    'Declare variables'
        Set objMyConn = New ADODB.Connection
        Set objMyRecordset = New ADODB.Recordset
        Dim strSQL As String

    'Open Connection'
        objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=MyDatabase;User ID=abc;Password=abc;"
        objMyConn.Open

    'Set and Execute SQL Command'
        strSQL = "select * from myTable"

    'Open Recordset'
        Set objMyRecordset.ActiveConnection = objMyConn
        objMyRecordset.Open strSQL

    'Copy Data to Excel'
        ActiveSheet.Range("A1").CopyFromRecordset objMyRecordset

    'Close recordset and connection (optional, to ensure no open object handles)
        objMyRecordset.Close
        objMyConn.Close
End Sub

Caution:
It's important to note that the "Close" methods (for both recordset and connection objects) should be used after the data-retrieval process is complete to avoid potential errors. However, it's not strictly necessary to close these objects explicitly, as they should be automatically closed when the subroutine exits.

The above is the detailed content of Why Does My Excel-VBA Code Get a '3704: Operation is not allowed when object is closed' Error When Copying Data from a SQL Recordset?. For more information, please follow other related articles on the PHP Chinese website!