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

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

Thanks for your help! I will give that a try

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.