0

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.

Community
  • 1
  • 1
GlennW
  • 11
  • 1
  • 4
  • 1
    Have you checked if there are any records being fetched? – Pankaj Jaju Mar 05 '14 at 17:15
  • @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 Mar 05 '14 at 18:28
  • Looks like there are no records – Pankaj Jaju Mar 05 '14 at 18:30
  • When I execute the statement in SQL Server Management Studio I get 279 records back. So there should be records. – GlennW Mar 05 '14 at 21:49
  • 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](http://stackoverflow.com/questions/8249706/run-stored-procedure-and-return-values-from-vba) too. – Pankaj Jaju Mar 05 '14 at 22:23
  • @Pankaj Jaju I think my connection is working but no records are being returned. When the error message says "the object is closed" it must be referring to the recordset and not the ADODB connection. I entered the following code just before the line where I get the error: "MsgBox "The connection status is " & cnt.State". The message box says my connection status is 1 (open) so something else must be closed. – GlennW Mar 06 '14 at 21:21
  • I still believe that the recordset is empty because the SP doesnt fetch anything. Have you tried the link I posted earlier for SP call via VBA? – Pankaj Jaju Mar 07 '14 at 09:53
  • @Pankaj Jaju I have looked at the link you provided. Are you saying that I need to set up ADO DB parameters rather than passing text strings as my parameters? I tried modifying the code example in the link you provided to fit my SP but it failed as soon as the VBA stepped into the first "cmd.Parameters.Append" command. – GlennW Mar 10 '14 at 19:27
  • @Pankaj Jaju I am slowly learning how to debug this thing. I found out how to print the error number and error description which are: "5:Invalid procedure call or argument". I suspect SQL isn't liking the parameters I am trying to pass. – GlennW Mar 10 '14 at 20:05
  • Have you tried using [`ADODB.Command`](http://support.microsoft.com/kb/185125) to execute SP? – Pankaj Jaju Mar 10 '14 at 20:54
  • @PankajJaju I have rewritten my code so that it uses the ADODB.cmd to execute the SP. That along with some error trapping code and I now know what is causing the problem. Is there a way that I can share the new code and error message with you or do I have to submit a completely new question? BTW thank you for all the help. – GlennW Mar 11 '14 at 15:13
  • Cool ... open a new question for quick visibility ... I or someone else might be able to help :-) – Pankaj Jaju Mar 11 '14 at 15:17
  • @PankajJaju please see the new question I have opened: [Excel VBA passing wrong number of parameters to ADO command object](http://stackoverflow.com/questions/22331221/excel-vba-passing-wrong-number-of-parameters-to-ado-command-object) – GlennW Mar 11 '14 at 16:49

1 Answers1

1

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.

Lee Berry
  • 11
  • 1