Home > Artikel > Ausgabe 11/2014 > VBA-Funktionen in Abfragen

VBA-Funktionen in Abfragen

Achtung: Sie sind nicht angemeldet. Wenn Sie Abonnent sind und sich anmelden, lesen Sie den kompletten Artikel, laden das PDF herunter oder probieren die Beispieldatenbank aus (sofern vorhanden).

Ob für die Berechnung von Feldern oder für Kriterien, um den Einsatz von VBA-Funktionen in Abfragen und SQL-Ausdrücken werden Sie häufig nicht herum kommen. Die Liste von originären Funktionen der Access-SQL-Engine ist ziemlich kurz, und für alle weitergehenden Rechenaufgaben verwendet Access nach Aussage von Microsoft den sogenannten VBA Expression Service. Betrachten wir in diesem Beitrag, wie beide zusammenspielen und auf was hier das Augenmerk liegen sollte.

Beispieldatenbank

Die Beispiele dieses Artikels finden Sie in der Datenbank 1411_VBAInAbfragen.mdb.

Abfragen-Komponenten

Wenn Sie in Ihrer Datenbank eine Abfrage zusammenbasteln, so kommen intern gleich drei Komponenten ins Spiel. Die offensichtlichste ist der Abfrageneditor von Access, der eine grafische Oberfläche zur Verfügung stellt, um per Maus und Tastatur, sowie die entsprechenden Hilfsdialoge, eine Abfrage visuell zu erstellen. In Bild 1 sehen Sie etwa den Entwurf einer ganz einfachen Abfrage auf die Tabelle tblStammdaten, um die sich auch in der Beispieldatenbank dieses Beitrags alles rankt. In ihr werden alle Felder und Datensätze der Tabelle ausgegeben, und das Ergebnis ist dasselbe, als würden Sie die Tabelle in der Datenblattansicht öffnen. Die Berechnung des Ergebnisses nimmt dabei aber nicht Access selbst vor, sondern die Office Access Database Engine (ACE), welche sich unter VBA auch über die DAO-Bibliothek ansprechen lässt. Access übersetzt lediglich den visuellen Entwurf in ein SQL-Statement und übergibt es der ACE-Engine.

Die Stammdatentabelle über eine Abfrage ausgeben

Bild 1: Die Stammdatentabelle über eine Abfrage ausgeben

Das SQL-Statement können Sie ja auch über die Ansicht des Entwurfs einsehen oder modifizieren. Die ACE ist das Datenbank-Herzstück und quasi der SQL-Server von Access. Sie erwartet einen bestimmten SQL-Dialekt, reagiert also auf Anweisungen, die aus genau definierten Ausdrücken und Operatoren bestehen. Die Liste ihrer reservierten Ausdrücke finden Sie etwa unter http://msdn.microsoft.com/en-us/library/bb208875%28v=office.12%29.aspx.

Funktionen zur Weiterverarbeitung von Variablenwerten, seien es numerische oder alphanumerische, sind dort indessen so gut wie keine aufgeführt. Es handelt sich hier eher um Operatoren, die mit den Datenmengen selbst umgehen. Beispiele für originäre SQL-Funktionen wären etwa die LIKE- oder BETWEEN-Anweisungen. Sobald Sie jedoch darüber hinaus Funktionen auf Feldinhalte loslassen, klinkt sich der sogenannte VBA Expression Service als dritte Komponente ein. Eine Funktion, wie Left() zum Abschneiden von Strings – von Access im Abfragenentwurf überflüssigerweise in Links() eindeutscht – wird von VBA behandelt, weshalb deren Syntax und Parameter auch identisch sind mit dem, was Sie im VBA-Code angeben würden.

Sie können also fast beliebige VBA-Funktionen auf Feldinhalte anwenden. Der Umfang dieser Funktionen ergibt sich aus dem Objektkatalog des VBA-Editors, indem Sie dort die Bibliothek VBA auswählen. Diese Bibliothek ist kein Bestandteil von Access! Sie können etwa die ACE auf einem System auch ohne Access installieren und dennoch VBA-Funktionen verwenden, wenn Sie die Engine von einer anderen Programmiersprache aus benutzen.

Eine Besonderheit des VBA Expression Service ist jedoch, dass er benutzerdefinierte Funktionen ermöglicht. Die Verbindung zwischen eigenem Code und dem Service wird allerdings von Access vorgenommen. Es sagt dem Service gewissermaßen, wo die Funktionen zu finden sind. Wie genau das funktioniert ist nicht dokumentiert, und es läuft ohne weiteres Zutun Ihrerseits ab.

Zusammengefasst lassen sich die an einer Abfrage beteiligten Komponenten im Überblick schematisch wie in Bild 2 darstellen. Access stellt damit ein recht leistungsfähiges System zur Verfügung, das mit sehr geringem Verwaltungsaufwand für die Entwicklung von Abfragen auskommt.

An einer Access-Abfrage beteiligte Komponenten (Schema)

Bild 2: An einer Access-Abfrage beteiligte Komponenten (Schema)

VBA-Einsatz in Abfragen

Wo Licht ist, da ist auch Schatten. Bekannt etwa dürfte sein, dass VBA-Funktionen nicht unbedingt rasende Performance aufweisen. Das gilt im Besonderen für selbstprogrammierte Module, da VBA keinen richtigen Binär-Code erzeugt, sondern halbinterpretierend arbeitet. Eigene VBA-Funktionen sind also ziemlich langsam. Darauf muss geachtet werden, wenn solche Funktionen in Abfragen eingesetzt werden. Die mangelnde Performance von VBA allein ist jedoch noch nicht der einzige Bremsschuh. Um dies zu erläutern, betrachten wir ein Beispiel, welches die Abfrage aus Bild 1 um eine benutzerdefinierte Funktion erweitert (siehe Bild 3). Die VBA-Funktion fux ist im Modul mdlTest der Beispieldatenbank so angelegt:

Abfrage mit zusätzlich eingefügter VBA-Funktion

Bild 3: Abfrage mit zusätzlich eingefügter VBA-Funktion

Function fux(lId As Long) As Long

     fux = lId

End Function

Sie tut nichts anderes, als den ihr übergebenen Wert als Resultat wieder zurückzugeben. Genauso gut hätte man für die Abfragenspalte n auch auch schlicht das Feld ID direkt einsetzen können.

Um die Performance dieser Abfrage zu prüfen, existiert im Modul mdlTest eine Routine TestPerformance, die in einer Schleife hundertmal ein Recordset auf die Abfrage öffnet und alle Datensätze durchläuft. Die dafür benötigte Zeit wird gemessen. Sie ist in Listing 1 zu finden und wird später noch gesondert beleuchtet. Im VBA-Direktfenster gibt sie schließlich die benötigte Zeit für ihre Ausführung aus. Rufen Sie sie etwa über diese Anweisung auf:

Sub TestPerformance(ByVal TestNo As Long)

     Dim t As Single, i As Long, J As Long

     Dim rs As DAO.Recordset

     Dim V As Variant, sQry As Variant

     Select Case TestNo

     Case 0: sQry = Array("qry_Stammdaten", "qry_StammdatenVBA")

     Case 1: sQry = Array("qry_Stammdaten_Sort", "qry_StammdatenVBA_Sort")

     Case 2: sQry = Array("qry_Stammdaten1965_12", "qry_Stammdaten1965_12_2", _

"qry_Stammdaten1965_12_3", "qry_Stammdaten1965_12VBA")

     Case 3: sQry = Array("qry_Stammdaten_Mann", "qry_Stammdaten_MannVBA")

     Case 4: sQry = Array("qry_Stammdaten_BE", "qry_Stammdaten_BEVBA")

     Case 5: sQry = Array("qry_Stammdaten_Feiertag", "qry_Stammdaten_FeiertagVBA")

     Case 6: sQry = Array("qry_Stammdaten_NotNull", "qry_Stammdaten_NotNull2", "qry_Stammdaten_NotNullVBA")

     Case 7: sQry = Array("qry_Stammdaten_StrNull", "qry_Stammdaten_StrNullVBA")

     Case 8: sQry = Array("qry_StammdatenGeb", "qry_StammdatenGebVBA")

     End Select

     For J = 0 To UBound(sQry)

         t = VBA.Timer

         For i = 1 To 100

             Set rs = DBEngine(0)(0).OpenRecordset("SELECT * FROM " & sQry(J), dbOpenDynaset)

             Do While Not rs.EOF

                 V = rs.Fields(0).Value

                 rs.MoveNext

             Loop

             rs.Close

             Set rs = Nothing

         Next i

         Debug.Print sQry(J), 1000 * (VBA.Timer - t) & " ms"

     Next J

End Sub

Listing 1: Aufrufen des Detailformular mit entsprechendem Filter

TestPerformance 0

Auf einem etwas betagten Rechner kommt für die in Bild 3 dargestellte Abfrage ein Wert von 3300 ms heraus. Ändern Sie die Abfrage ab, indem Sie die VBA-Funktion fux eliminieren und einfach durch den Inhalt das Feldes ID ersetzen, so gibt TestPerformance eine Zeit von 3360 ms aus. Das ist irritierend, denn zu erwarten wäre nach dem bisher Gesagten eher, dass die Ausführung nun schneller vonstatten gehen müsste. Der Grund liegt in der Code-Zeile verborgen, die einen Feldwert eines Datensatzes ermittelt:

V = rs.Fields(0).Value

Es wird der Wert der ersten Spalte des Recordsets abgefragt, und das ist hier die ID des Datensatzes. Darum kommt die VBA-Funktion überhaupt nicht zur Geltung. Beweisen lässt sich dies, indem Sie einen Haltepunkt in die Funktion setzen und die Testroutine abermals starten: Ein Aufruf der Funktion fux findet nicht statt. Das Bild ändert sich, wenn Sie statt der Spalte 0 die elfte abfragen, also jene, die die Funktion fux auswertet.

Schon steigt die Ausführungszeit auf 8600 ms, also auf etwa das Zweieinhalbfache. Und das, obwohl die VBA-Funktion an sich sehr schnell ist und gar nichts berechnet. Das Ganze ist der Interaktion zwischen der Database Engine und dem VBA Expression Service im Verein mit dem VBA-Modul geschuldet.

Zusätzlich ändert die Engine ihren sogenannten Query Plan, den Ablaufplan der Abfrage, sobald benutzerdefinierte VBA-Funktionen eingebaut sind. Das zu erläutern führte an dieser Stelle zu weit.

Festgehalten werden sollte lediglich, dass Access bei Abfragen mit benutzerdefinierten VBA-Funktionen alle Datensätze eines Recordsets ermittelt, während ohne diese, je nach Gestaltung der Abfrage, meist nur die benötigte Teilmenge berechnet wird.

Weitere Beispiele finden Sie in Bild 4 und Bild 5, wo die Datensätze nach dem Wert der Spalte ID beziehungsweise der Funktion fux aufsteigend sortiert werden. Die Ausführungszeiten bei uns:

Datensätze nach der ID aufsteigend sortieren

Bild 4: Datensätze nach der ID aufsteigend sortieren

Datensätze nach fux() aufsteigend sortieren

Bild 5: Datensätze nach fux() aufsteigend sortieren

qry_Stammdaten_Sort 3343 ms

qry_StammdatenVBA_Sort 10875 ms

Obwohl die Funktionalität bei beiden Abfragen dieselbe ist, unterscheidet sich die benötigte Zeit um den Faktor 3. Minimieren Sie also, wenn möglich, den Einsatz von VBA-Funktionen, so gut es geht.

VBA-String-Funktionen

Die bisherigen Beispiele hatten nur einen demonstrativen Zweck. Wenden wir uns im Folgenden sinnvolleren Beispielen zu. Sie wollten etwa alle Stammdaten ermitteln, deren Nachnamen mit "BE" beginnen. Über VBA ließe sich das per Instr() so lösen:

SELECT tblStammdaten.*

FROM tblStammdaten

WHERE InStr(1,[Nachname],"BE"))=1

Wenn der Teil-String "BE" sich an der ersten Stelle befindet, ist die Bedingung erfüllt. Auf VBA kann aber in diesem Fall verzichtet werden, denn über den LIKE-Operator von SQL geht das Ganze auch so:

SELECT tblStammdaten.*

FROM tblStammdaten

WHERE [Nachname] Like "BE*"

Die Ausführungszeiten im Vergleich:

qry_Stammdaten_BE 437 ms

qry_Stammdaten_BEVBA 8230 ms

Die Performance unterscheidet sich krass! Das gute Abschneiden der SQL-Lösung beruht hier unter anderem auf dem Sachverhalt, dass das Feld Nachname indiziert ist.

Ähnlich verhält es sich mit dem nächsten Beispiel, wo alle Datensätze herausgefiltert werden sollen, in denen das Feld Nachname ein "mann" enthält. Dies sind die VBA- und SQL-Lösungen, sowie deren Ausführungszeiten:

SELECT tblStammdaten.*

FROM tblStammdaten

WHERE InStr(1,[Nachname],"mann"))>0

SELECT tblStammdaten.*

FROM tblStammdaten

WHERE [Nachname] Like "*mann*"

Sie haben das Ende des frei verfügbaren Teil dieses Artikels erreicht!

Wenn Sie mehr lesen und auf viele weitere Artikel zugreifen möchten, melden Sie sich als Abonnent unter Login an. Falls nicht, bestellen Sie doch einfach ein Jahresabonnement!