How to Fix Run-time Error 3704 When Accessing SQL Databases in Excel VBA?

Accessing SQL Database in Excel-VBA: Resolving Run-time Error 3704

In accessing data from a SQL database using VBA code, you may encounter an error when attempting to copy data from the recordset to Excel. Specifically, the error "Run-time error 3704: Operation is not allowed when object is closed" occurs when the recordset object is closed.

To resolve this issue:

1. Verify database accessibility: Ensure that the database can be accessed by VBA. Make sure the database is running, the connection string is correct, and the user has the necessary permissions to access the database.
2. Add initial catalog to connection string: Modify the connection string to include the initial catalog, which represents the database name. For instance:

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

3. Open the recordset directly: Instead of using the ADODB.Command object, you can directly open the recordset using the SQL statement. Here's an updated code snippet:

Const adCmdText As Long = 1
Dim strSQL As String

... // Other code above

' Open Recordset'
Set objMyRecordset.ActiveConnection = objMyConn
strSQL = "select * from myTable"
objMyRecordset.Open strSQL            

... // Rest of the code below

By making these adjustments, you should be able to successfully copy data from the SQL database to Excel using VBA without encountering the Run-time error 3704.

The above is the detailed content of How to Fix Run-time Error 3704 When Accessing SQL Databases in Excel VBA?. For more information, please follow other related articles on the PHP Chinese website!