Accessing SQL Database in Excel-VBA

Question

I am copying an VBA code snippet from MSDN that shows me how to grab results from a SQL query into excel sheet (Excel 2007):

Sub GetDataFromADO()      'Declare variables'         Set objMyConn = New ADODB.Connection         Set objMyCmd = New ADODB.Command         Set objMyRecordset = New ADODB.Recordset      'Open Connection'         objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;User ID=abc;Password=abc;"         objMyConn.Open      'Set and Excecute SQL Command'         Set objMyCmd.ActiveConnection = objMyConn         objMyCmd.CommandText = "select * from myTable"         objMyCmd.CommandType = adCmdText         objMyCmd.Execute      'Open Recordset'         Set objMyRecordset.ActiveConnection = objMyConn         objMyRecordset.Open objMyCmd      'Copy Data to Excel'         ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)  End Sub 

I have already added Microsoft ActiveX Data Objects 2.1 Library under as a reference. And this database is accessible.

Now, when I run this subroutine, it has an error:

Run-time error 3704: Operation is not allowed when object is closed.

On the statement:

ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset) 

Any idea why?

Thanks.

Answer 1

I've added the Initial Catalog to your connection string. I've also abandonded the ADODB.Command syntax in favor of simply creating my own SQL statement and open the recordset on that variable.

Hope this helps.

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 

Answer 2

Suggested changes:

• Do not invoke the Command object's Execute method;
• Set the Recordset object's Source property to be your Command object;
• Invoke the Recordset object's Open method with no parameters;
• Remove the parentheses from around the Recordset object in the call to CopyFromRecordset;
• Actually declare your variables :)

Revised code:

Sub GetDataFromADO()      'Declare variables'         Dim objMyConn As ADODB.Connection         Dim objMyCmd As ADODB.Command         Dim objMyRecordset As ADODB.Recordset          Set objMyConn = New ADODB.Connection         Set objMyCmd = New ADODB.Command         Set objMyRecordset = New ADODB.Recordset      'Open Connection'         objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=localhost;User ID=abc;Password=abc;"             objMyConn.Open      'Set and Excecute SQL Command'         Set objMyCmd.ActiveConnection = objMyConn         objMyCmd.CommandText = "select * from mytable"         objMyCmd.CommandType = adCmdText      'Open Recordset'         Set objMyRecordset.Source = objMyCmd         objMyRecordset.Open      'Copy Data to Excel'         ActiveSheet.Range("A1").CopyFromRecordset objMyRecordset  End Sub