VBA Tipp: Zeilen einer Spalte einer Tabelle in kommaseparierte Liste umwandeln

Aus DBWiki
Wechseln zu: Navigation, Suche

Das Problem

Oft benötigt man eine Methode, um viele Zeilen einer Tabelle/Abfrage in einen kommaseparierten String umzuwandeln - am häufigsten z.B., um eine Liste von IDs zu erhalten, die man in einer "WHERE..IN"-Klausel als Parameter einfügen kann.

Die Lösung

Hier eine Methode für DAO:

Public Function fnConcatColumn(ByVal strSQL As String _ 
                    , Optional ByVal strSeparator As String = ", ") As String 
    Dim strIDArray() As Variant 
    Dim i As Long 
    Dim strResult As String 
    Dim rs As DAO.Recordset 
 
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly) 
    fnConcatColumn = "" 
    If Not rs Is Nothing Then 
        If Not (rs.EOF And rs.BOF) Then 
            rs.MoveLast 
            rs.MoveFirst 
            strIDArray() = rs.GetRows(rs.RecordCount) 
            For i = 0 To UBound(strIDArray, 2) 
                strResult = strResult & Trim(strIDArray(0, i)) & strSeparator 
            Next i 
            fnConcatColumn = Left(strResult _ 
                                , Len(strResult) - Len(strSeparator)) 
        End If 
        rs.Close 
        Set rs = Nothing 
    End If 
End Function

Der Funktion muß ein SQL-String übergeben werden, der die gewünschte Spalte und sonstige Kriterien zur Auswahl enthält (und wenn es nicht gerade um IDs geht, kann man auch noch ein ORDER BY hinzufügen, wenn man eine sortierte Ausgabe benötigt). Außerdem kann ein Trennzeichen bestimmt werden, das auch aus mehreren Zeichen bestehen kann.

Aus dem SQL-String wird ein Recordset gemacht und per "GetRows" in ein Array eingelesen - das funktioniert bedeutend schneller, als eine Schleife per Do..Loop durchzugehen, da alles mit einem Kommando erledigt wird (und die Ausführung von Access-Funktionen immer schneller ist als VBA-Ausführung). Das Umwandeln des Arrays geschieht dann nur noch im Speicher, so daß nicht jede Zeile einzeln eingelesen werden muß.

Die Alternative mit ADO

Schneller geht es noch mit ADO:

Erstens muß bei ADO die ebenfalls vorhandene GetRows-Methode nicht mit einer Anzahl gefüttert werden, sondern überträgt selbständig alle Datensätze, so daß man auch für das Recordset die schnellere "OpenForwardOnly"-Methode verwenden kann, die kein rs.Movexxx erlaubt.

Zweitens kann der GetRows-Befehl hier auch auf eine bestimmte Spalte einer Abfrage angesetzt werden, so daß nur diese eingelesen wird.

Drittens enthält das ADO-Objekt auch noch eine "GetStrings"-Methode, die den gewünschten Job beinahe von allein erledigt, so daß auch die Array-Manipulation oben nicht mehr notwendig ist und der Code sich weiter verkürzt:

Public Function ADOConcatColumn(ByVal strSQL As String _ 
                              , Optional ByVal strSeparator As String = ", ") As String
    Dim rs As ADODB.Recordset 
 
    Set rs = New ADODB.Recordset 
    rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly 
    ADOConcatColumn = "" 
    If Not rs Is Nothing Then 
        If Not (rs.EOF And rs.BOF) Then 
            ADOConcatColumn = rs.GetString(, , , strSeparator) 
        End If 
        rs.Close 
        Set rs = Nothing 
    End If 
End Function

Beispiel

Zum Aufruf kann man z.B. so verfahren:

Dim strResult As String
strResult = fnConcatColumn("SELECT ID FROM MyTable WHERE MyText='Sample' ORDER BY MyDate")

Der Aufruf der ADO-Funktion funktioniert genauso.

Hinweise

  • verlockend wäre, die Funktion nun ihrerseits in einer Abfrage zu verwenden, um sie in einer IN-Klausel einzusetzen, denn SQL unterstützt ja den Aufruf von VBA-Funktionen. Das funktioniert aber leider nicht, denn der Rückgabestring wird als Ganzes interpretiert und somit ist das Ergebnis immer leer. Hier muß man also den SQL-String per VBA zusammensetzen und dann ausführen.
  • zur Ausführung des ADO-Beispiels muß man unter Verweise im VBA-Editor den Verweis auf die ADO-Library setzen (2.8 für Windows XP aufwärts, 6.0 für Vista aufwärts)