locked
CopyFromRecordset Method bugs Stepping Through Code (F8) and jumps to the end of Sub RRS feed

  • Question

  • I am trying to copy a Recordset to cell ("A2") of a worksheet using the following macro:

    Sub sub_copy_Recordset()
    
        Dim objRecordset As ADODB.Recordset
        Dim strConnection As String
        Dim input_portfolio, setRange As String
        Dim end_date As Date
        Dim i, record_count As Integer
    
        input_portfolio = ActiveWorkbook.Sheets("_portfolio").Range("main").Cells(1, 1).Value
        end_date = ActiveWorkbook.Sheets("_portfolio").Range("main").Cells(2, 1).Value
        ini_date = ActiveWorkbook.Sheets("_portfolio").Range("main").Cells(3, 1).Value
    
    
            strConnection = "[connection parameters];"
            Set objConnection = New ADODB.Connection
            Set objRecordset = New ADODB.Recordset
            objRecordset.CursorLocation = adUseClient
            objConnection.Open strConnection
    
            With objRecordset
                .ActiveConnection = objConnection
                .Open "[select statement]"
            End With
    
            With ActiveWorkbook.Sheets("_tables")
                .Range("A2").CopyFromRecordset objRecordset
                record_count = objRecordset.RecordCount
                objRecordset.Close
                Set objRecordset = Nothing
            End With
    
    objConnection.Close
    Set objConnection = Nothing
    
    MsgBox "End Sub"
    
    End Sub

    When I step into and F8 in line 

    .Range("A2").CopyFromRecordset objRecordse
     

    the macro jumps to the last line

    MsgBox "End Sub"
    of the Sub, skipping all lines in between. No way to step into lines after executing CopyFromRecordset.
    Monday, April 27, 2020 6:15 PM

All replies

  • Most likely problem is either the connection.open failed or the recordset open failed.

    When single stepping, after recordset .open in the immediate window type:

    ?objRecordset.eof

    It should be false to show the recordset has data to read. If true, your select statement may well be the problem. Test it in SQL Ser Management Studio.


    Rod Gill
    Author of the one and only Project VBA Book and VBA developer.
    www.project-systems.co.nz

    Thursday, April 30, 2020 9:38 PM