Excel VBA Run-time Error '3704'

Question

I am getting run-time error '3704' when trying to dump a recordset into the tab of an Excel 2010 workbook. The recordset should contain a couple of hundred records that come from a stored procedure I wrote using SQL Server 2008 R2. I know the exact same statement executes when ran in SQL Server Management Studio and I have used the same connection string in the past so I am pretty sure those parts of my code are working correctly.

I have researched the error and the only solutions I have seen have to do with the connection timing out. You will see that I have set CommandTimeout = 30 (I assume that is in seconds). I get the error in just a few seconds of clock time so I am sure that this is not a problem with my connection timing out.

My VBA code:

Sub Add_Results_Of_ADO_Recordset()
     
    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stSQL As String
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim rnStart As Range
     
     
    Const stADO As String = "Provider=SQLOLEDB.1;User ID =xxxxx;Password=xxxxx;" & _
    "Persist Security Info=False;" & _
    "Initial Catalog=MyDatabase;" & _
    "Data Source=xxxxxxxxxxxx"
    
    Set wbBook = ActiveWorkbook
    Set wsSheet = wbBook.Worksheets(1)
     
    With wsSheet
        Set rnStart = .Range("TopLeft").Offset(1, 0)
    End With
     
    Set cnt = New ADODB.Connection
     
    With cnt
        .CursorLocation = adUseClient
        .Open stADO
        .CommandTimeout = 30
    End With
        
    stSQL = "EXEC MyDatabase.dbo.PolicyList '2/1/2014','2/1/2014','BOOK'"
    
    
    With cnt
     Set rst = .Execute(stSQL)
    End With
    
    'Dump recordset into my WorkBook
    rnStart.CopyFromRecordset rst    'This is where the error occurs!!!
        
     
     'Cleaning up.
    rst.Close
    cnt.Close
    Set rst = Nothing
    Set cnt = Nothing
     
End Sub

This is the error message I get:

Run-time error '3704':

Operation is not allowed when object is closed.

@Pankaj Jaju I have entered the following into the immediate window: ?rst.fields(0) When I do that I get an error box that says: * Run-time error '3265': Item cannot be found in the collection corresponding to the requested name or ordinal. * Beyond that I am not sure how to check. — GlennW
When I execute the statement in SQL Server Management Studio I get 279 records back. So there should be records. — GlennW
Well for some reason the same isnt being fetched from VBA ... could be because of connection string or because the stored procedure needs different user credentials. Have you tried troubleshooting it .... display records one by one rather than pasting it in Excel at once? Take a look at this too. — Pankaj Jaju

Lee Berry Lee Berry · Accepted Answer · 2018-08-15T14:03:41

Add SET NOCOUNT ON to the head of your stored proc.

You can see if the proc is returning any text in the Messages tab in SQLS MS. Messages like '100 rows affected' will cause VBA to close the recordset immediately.

Excel VBA Run-time Error '3704'

1 Answers