Access/VBA Library

QueryRows

Executes a SELECT-query and returns the output of the query as a Collection of rows.

Syntax

QueryRows( query )
Parameters
Name Type Description
query String Required.
Return value

Collection : A Collection of rows. Each item reprrsents a row and contains a Dictionary to store the data.

Code
Function QueryRows(query As String) As Collection

    Const cFunctionName = "QueryRows"

On Error GoTo QueryRows_Error
    
    Dim rs As Recordset
    Dim result As New Collection
    
    Set rs = CurrentDb.OpenRecordset(query, dbOpenSnapshot, dbReadOnly)
    
    If Not rs.EOF Then
        rs.MoveFirst
        
        While Not rs.EOF
            result.Add FetchRow(rs)
            rs.MoveNext
        Wend
    Else
        Set result = Nothing
    End If
       
QueryRows_Exit:
    On Error Resume Next
    Set QueryRows = result
    Exit Function

QueryRows_Error:
    Set result = Nothing
    Set result = New Collection
    ShowError functionName:=cFunctionName
    Resume QueryRows_Exit

End Function

Databasetag:DatabaseSQLtag:SQLCollectiontag:Collection

Changelog

Date Changes
2018-04-02 Performance change: open recordset as read-only snapshot.

See also

  • FetchRow
  • QueryRow