VBA-Funktionen in Abfragen

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.

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.

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:

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

Username or Email

Passwort

Angemeldet bleiben