Assigning recordset to array errors when zero records returned

[bradles]

I have developed the following function to put the results of a SQL query into a variant array.

Public Function QryXLBookToArray(ByRef sPath As String, sSQL As String) As Variant

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & sPath & ";" & _
                    "Extended Properties=""Excel 8.0;HDR=Yes"";"

    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly

    If rst.RecordCount > 0 Then

        QryXLBookToArray = rst.GetRows(rst.RecordCount)
    
    Else
        QryXLBookToArray = ""
    End If
    
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing

End Function

However, when I try to assign this function to a variant variable and there are no records returned in the SQL query I get the following error:

Run-time error '13':
Type mismatch

It works fine when there are records being returned from the SQL query but falls over whenever there are zero records found.

As you can see, I tried setting the function to "" if the RecordCount was 0...to no avail.

Anyone have any ideas how I can handle this function when no records are returned from the SQL query?

[RichardSchollar]

Hi

Dim myVar As Variant
myVar =QryXLBookToArray("C:\Test.xls", "SELECT * FROM Data;")

works for me where Test.xls only contains Headers (and no data).

Make sure that you aren't assigning the variant variable like so:

myVar() = QryXLBookToArray(sPath,sSql)

You must not include the parentheses () after the variant variable name.

Richard

[Bob Phillips]

That code works for me too, but I wouldn't assign the value to "" on no records, I would leave it empty.

[rory]

Just so you are aware, under certain conditions ADO will return -1 as the recordcount even though there are records returned, so you would be better off checking that rst.EOF and rst.BOF are not both true to determine whether the recordset is empty.