Access/VBA Library

QueryColumnValues

Executes a SELECT-query and returns the values of the first column as an Array.

Syntax

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

Variant : An array which contains all values returned by the query.

Code
Function QueryColumnValues(query As String)
    Const cFunctionName = "QueryColumnValues"

On Error GoTo QueryColumnValues_Error

    Dim rs As Recordset
    Dim pfx As String
    Dim result As String
    
    Set rs = CurrentDb.OpenRecordset(query, dbOpenSnapshot, dbReadOnly)
    With rs
        If Not .EOF Then
            .MoveFirst
            pfx = ""
            While Not .EOF
                result = result & pfx & Nz(.Fields(0).Value, "")
                .MoveNext
                pfx = vbNullChar
            Wend
        End If
        .Close
    End With
    
QueryColumnValues_Exit:
    On Error Resume Next
    QueryColumnValues = Split(result, vbNullChar)
    Exit Function

QueryColumnValues_Error:
    result = ""
    ShowError functionName:=cFunctionName
    Resume QueryColumnValues_Exit

End Function

Databasetag:DatabaseSQLtag:SQLArraytag:Array

Remarks

  • When an error occured, a errormessage will pop up.

Changelog

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

See also

  • ShowError