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

Aus DBWiki
Wechseln zu: Navigation, Suche

Anforderung

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

Lösung mittels DAO.Recordset

Der Funktion muss ein SQL-String übergeben werden, der die gewünschte Spalte und sonstige Kriterien zur Auswahl enthält. 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 erstellt 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 als VBA-Ausführung ist. Das Umwandeln des Arrays geschieht dann nur noch im Speicher, so dass nicht jede Zeile einzeln eingelesen werden muss.

Public Function ConcatColumn(ByVal strSQL As String, _
                             Optional ByVal strSeparator As String = ", ") As String
 
   'Quelle: www.dbwiki.net oder www.dbwiki.de
 
   Dim strIDArray As Variant
   Dim strResult  As String
   Dim rs         As DAO.Recordset
   Dim i          As Long
 
   Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
 
   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
         fnConcatColumn = Left$(strResult, Len(strResult) - Len(strSeparator))
   End If
 
   rs.Close
 
End Function

Lösung mittels ADODB.Recordset

Schneller geht es jedoch mit ADO:

  1. Bei ADO muss die ebenfalls vorhandene GetRows()-Methode nicht mit einer Anzahl gefüttert werden, sondern sie überträgt selbstständig alle Datensätze, so dass man auch für das Recordset die schnellere OpenForwardOnly-Methode verwenden kann, die keine Kombination aus rs.MoveLast und rs.MoveFirst benötigt.
  2. Der GetRows()-Befehl kann hier auch auf eine bestimmte Spalte einer Abfrage angesetzt werden, sodass nur diese eingelesen wird.
  3. Das ADO-Objekt enthält auch noch eine GetStrings()-Methode, die den gewünschten Job beinahe von allein erledigt, sodass 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
 
   'Quelle: www.dbwiki.net oder www.dbwiki.de
 
   Dim rs As ADODB.Recordset
 
   Set rs = New ADODB.Recordset
   rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
   If Not (rs.EOF And rs.BOF) Then
      ADOConcatColumn = rs.GetString(, , , strSeparator)
   End If
 
   rs.Close
 
End Function

Aufruf

Der Aufruf der ADO-Funktion funktioniert analog dem Aufruf der DAO-Funktion.
Beim Aufruf kann man z.B. so verfahren:

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


Wiki hinweis.png

Hinweise:

  • Verlockend wäre es, die Funktion nun ihrerseits in einer Abfrage zu verwenden, um sie in einer IN-Klausel einzusetzen, denn Jet-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 muss man also den SQL-String per VBA zusammensetzen.
  • Zur Ausführung des ADO-Beispiels muss der Verweis auf die ADO-Library (Microsoft ActiveX Data Objects x.x Library) gesetzt werden.