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.