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.

Help! I'm having the same error, but the solutions below did not help: http://stackoverflow.com/questions/1682717/vba-adodb-run-time-error-3704 — Steven, Nov 05 '09 at 23:17

score 26 · Accepted Answer · edited Sep 14 '10 at 09:07

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

It worked for me, thanks, remember to add the reference to Microsoft ActiveX Data Objects 2.1, I added the 2.6 version though. Also don't you need to close the connection at the end? like objMyRecordset.Close ? — Oscar Ortiz, Jan 15 '15 at 14:18

score 17 · Answer 2 · edited Sep 14 '10 at 09:08

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

+1 for removing the parentheses. Unnecessary parentheses combined with object references seem to cause weird VBA errors — barrowc, Jul 13 '09 at 23:14
I realize this is an old thread - but was wondering if there's a way to modify above code to retrieve the table headers as well? — firedrawndagger, Nov 16 '10 at 00:55

score 1 · Answer 3 · answered Jul 13 '09 at 16:54

Is that a proper connection string?
Where is the SQL Server instance located?

You will need to verify that you are able to conenct to SQL Server using the connection string, you specified above.

EDIT: Look at the State property of the recordset to see if it is Open?
Also, change the CursorLocation property to adUseClient before opening the recordset.

score 1 · Answer 4 · answered Jul 13 '09 at 21:25

I'm sitting at a computer with none of the relevant bits of software, but from memory that code looks wrong. You're executing the command but discarding the RecordSet that objMyCommand.Execute returns.

I'd do:

Set objMyRecordset = objMyCommand.Execute

...and then lose the "open recordset" part.

score 0 · Answer 5 · answered Aug 09 '10 at 23:03

0

Add set nocount on to the beginning of the stored proc (if you're on SQL Server). I just solved this problem in my own work and it was caused by intermediate results, such as "1203 Rows Affected", being loaded into the Recordset I was trying to use.

answered Aug 09 '10 at 23:03

eric.christensen

3,191
4
29
35

score 0 · Answer 6 · answered Jan 11 '17 at 07:38

@firedrawndagger: to list field names/column headers iterate through the recordset Fields collection and insert the name:

Dim myRS as ADODB.Recordset
Dim fld as Field
Dim strFieldName as String 

For Each fld in myRS.Fields
    Activesheet.Selection = fld.Name
    [Some code that moves to next column]
Next

Accessing SQL Database in Excel-VBA

6 Answers6

Linked