Why Am I Getting 'Operation is not allowed when object is closed' When Accessing a SQL Database in Excel-VBA?

Accessing SQL Database in Excel-VBA: Troubleshooting Connection String

In accessing a SQL database from Excel-VBA, users may encounter the following runtime error: "Operation is not allowed when object is closed." when attempting to copy data from the recordset to an Excel range. This error often indicates an issue with the connection string or the state of the recordset.

One potential cause of this issue is the incorrect specification of the connection string. Ensure that all necessary parameters are provided, including the database name and any required credentials. The connection string provided in the original code snippet is missing the Initial Catalog parameter, which specifies the database to connect to.

By adding the Initial Catalog parameter to the connection string and using a simpler method for executing the SQL statement directly (as opposed to an ADODB.Command object), the issue can be resolved. The revised code below includes the necessary modifications:

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 Excecute 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

The above is the detailed content of Why Am I Getting 'Operation is not allowed when object is closed' When Accessing a SQL Database in Excel-VBA?. For more information, please follow other related articles on the PHP Chinese website!