How to Fix 'Run-time error 3704: Operation is not allowed when object is closed' in Excel VBA When Accessing SQL Databases?

Accessing SQL Database in Excel-VBA: Resolving Error 3704

In response to the query regarding the "Run-time error 3704: Operation is not allowed when object is closed" encountered while copying data from an SQL database to an Excel sheet using VBA, the answer provided suggests the following modifications:

Modified Code Snippet:

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)

End Sub

Changes Made:

• Initial Catalog Added: The connection string is now initialized with the "Initial Catalog" parameter, specifying the database to access.
• Simplified SQL Command: Instead of using ADODB.Command, a simple SQL statement (strSQL) is created and used directly to open the recordset.
• Field Assignments Simplified: The "Fields["FieldName].Value" format used to assign field values is removed.

By implementing these modifications, the issue with the recordset being closed is resolved, allowing for successful data retrieval and copying to the Excel sheet.

The above is the detailed content of How to Fix 'Run-time error 3704: Operation is not allowed when object is closed' in Excel VBA When Accessing SQL Databases?. For more information, please follow other related articles on the PHP Chinese website!