0

Hello Everyone,

I am having some trouble with a VBA application I am writing for work. I am using ADODB to connect to an Oracle database and return and I want to return a 2-D array from my function. The problem I am having seems to be determining how big the recordset is so that I can ReDim my 2-D array to fit the results. Any help would be greatly appreciated. I have included the function below.

Thanks,
Nick

Public Function QueryArray(QueryString As String) As String()
    Dim RowCount As Integer
    Dim ResultArray() As String
    Dim i As Integer
    RowCount = 0
    rsOra.Open QueryString, cnOra, adOpenForwardOnly
    ReDim ResultArray(0 To 100, 0 To 100) ' THIS IS WHERE I AM HAVING THE PROBLEM
    While Not rsOra.EOF
        For i = 0 To rsOra.Fields.Count - 1
            If IsNull(rsOra.Fields(i)) Then
                ResultArray(RowCount, i) = ""
            Else
                ResultArray(RowCount, i) = rsOra.Fields(i)
            End If
        Next
        RowCount = RowCount + 1
        rsOra.MoveNext
    Wend
    If IsArrayEmpty(ResultArray) Then
        Exit Function
    Else
        QueryArray = ResultArray
    End If
End Function
2
Contributors
2
Replies
3
Views
9 Years
Discussion Span
Last Post by stupidenator
0

Do you want to find no of records? If yes Recordset.RecordCount will help you to find no of records

Also try RecordSet.GetRows() method which returns array of records

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.