excelvbams-accessmacrosbyref

Unable to Pass ADODB.Recordset as a parameter to a function call


following is my declaration of recordset

    Dim expectedRs, actualRs As ADODB.Recordset

    Set expectedRs = accessDatabse.getResultSetForSqlQuery(tempArr(1))
    Set actualRs = accessDatabse.getResultSetForSqlQuery(tempArr(2))

the following works fine,

    excelFunc.writeQueryResultsToExcel( tempArr(0),   accessDatabse.getResultSetForSqlQuery(tempArr(1)),    accessDatabse.getResultSetForSqlQuery(tempArr(2)))

But, I cant pass the variables in the method call

    excelFunc.writeQueryResultsToExcel CStr(tempArr(0)), expectedRs,actualRs 

I get an compile time error "ByRef Argument Type Mismatch"

Following is my function being Called

    Public Function writeQueryResultsToExcel(workbookName As String, expectedRs As Object, actualRs As Object)

        Dim wkb As Workbook
        Dim strPath As String

        strPath = globalObj.getDefaultRunInstancePath()

        Set wkb = Workbooks.Open(strPath + workbookName + ".xlsx")

        'rs.Open strSQL
        'Sheet4.Range("A1").CopyFromRecordset rs
    '***********************************UntestedCode******************************
        wkb.Sheets("Expected").Range("A1").CopyFromRecordset expectedRs
        wkb.Sheets("Actual").Range("A1").CopyFromRecordset actualRs
        wkb.Save
        wkb.Close
    '***********************************UntestedCode******************************

    End Function

Solution

  • Dim expectedRs, actualRs As ADODB.Recordset
    

    Actually means:

    Dim expectedRs As Variant, actualRs As ADODB.Recordset
    

    And your function expects expectedRs to be Object and actualRs to be Object as well. Hence do the following changes:

    Dim expectedRs, actualRs As ADODB.Recordset
    

    change to:

    Dim expectedRs As ADODB.Recordset, actualRs As ADODB.Recordset
    

    and

    Public Function writeQueryResultsToExcel(workbookName As String, expectedRs As Object, actualRs As Object)
    

    change to:

    Public Function writeQueryResultsToExcel(workbookName As String, expectedRs As ADODB.Recordset, actualRs As ADODB.Recordset)