Wenn Sie mit dem VBA-Editor arbeiten und dort gelegentlich SQL-Anweisungen eingeben, müssen Sie die Namen der verwendeten Tabellen, Abfragen und Felder entweder kennen oder immer wieder zum Access-Fenster wechseln, um die Tabellen zu öffnen und die fehlenden Informationen nachzuschlagen. Dies können Sie mit dem hier beschriebenen Vorgehen in Zukunft wesentlich vereinfachen! Dabei können Sie die Techniken entweder nur zur Vereinfachung beim Zusammensetzen von SQL-Anweisungen verwenden oder direkt den Zugriff auf Ihre Daten damit organisieren.
Problem: Unkomfortable Dateneingabe
Wenn Sie beispielsweise eine Datensatzgruppe erstellen wollen, welche die Felder ArtikelID und Artikelname liefern soll, verwenden Sie eine Anweisung wie die folgende (rst muss als DAO.Recordset, db als DAO.Database deklariert sein):
Set rst = db.OpenRecordset("SELECT tblArtikel.ArtikelID, " _
& "tblArtikel.Artikelname FROM tblArtikel", dbOpenDynaset)
Oder nehmen wir an, Sie möchten eine Aktualisierungsabfrage ausführen, die den Einzelpreis eines Artikels anpasst. Dann verwenden Sie, die Deklaration einer Database-Variable namens db vorausgesetzt, etwa diese Anweisung:
db.Execute "UPDATE tblArtikel SET tblArtikel.Einzelpreis = 10 " _
& "WHERE tblArtikel.Artikelname LIKE ''Chai''"
Die Lösung: IntelliSense!
Wie wäre es denn, wenn Sie die SQL-Anweisung mit IntelliSense-Unterstützung erstellen könnten Dies könnten Sie wie in Bild 1 vom Direktfenster aus erledigen oder auch direkt im Codefenster.
Bild 1: Auswahl der Tabelle per IntelliSense …
Dazu geben Sie einfach nur die ersten Zeichen des gesuchten Tabellennamens ein und betätigen dann die Tastenkombination für IntelliSense, nämlich Umschalt + Leertaste.
Und es kommt noch besser: Wenn Sie den Tabellennamen eingegeben haben und einen Punkt hinzufügen, zeigt IntelliSense alle Feldnamen an – s. Bild 2
Bild 2: … und auch die Feldnamen stehen zur Verfügung!
Den so zusammengestellten SQL-Ausdruck müssen Sie nur noch mit vorangehendem
db.Open-Recordset("...", dbOpenDynaset)
ausstatten, schon ist die SQL-Anweisung fertig.
Wie es funktioniert
Wie funktioniert das Ganze Nun: Wir haben für die Tabelle tblArtikel eine gleichnamige Klasse erstellt. Diese enthält beispielsweise einige Property Get-Anweisungen, welche zwar noch Code enthalten, den wir weiter unten erläutern.
Der primäre Zweck allerdings ist, die Namen der Felder als Eigenschaften zur Verfügung zu stellen, damit diese per IntelliSense ausgewählt werden können:
Public Property Get ArtikelID() ArtikelID = rst!ArtikelID End Property Public Property Get Artikelname() Artikelname = rst!Artikelname End Property Public Property Get LieferantID() LieferantID = rst!LieferantID End Property Public Property Get KategorieID() KategorieID = rst!KategorieID End Property Public Property Get Liefereinheit() Liefereinheit = rst!Liefereinheit End Property Public Property Get Einzelpreis() Einzelpreis = rst!Einzelpreis End Property Public Property Get Lagerbestand() Lagerbestand = rst!Lagerbestand End Property Public Property Get BestellteEinheiten() BestellteEinheiten = _ rst!BestellteEinheiten End Property Public Property Get Mindestbestand() Mindestbestand = rst!Mindestbestand End Property Public Property Get Auslaufartikel() Auslaufartikel = rst!Auslaufartikel End Property
Das erklärt, warum nach Eingabe des Punktes die einzelnen Feldnamen angezeigt werden – es handelt sich schlicht um Property Get-Eigenschaften, wie sie in der Klassenprogrammierung verwendet werden. Aber warum können wir einfach so die Klassennamen wie tblArtikel per IntelliSense nutzen Dies gelingt bei herkömmlichen Klassen nicht.
Wir haben an dieser Stelle einen kleinen Trick angewendet, der die Klasse ohne Instanzierung ausführbar macht. Dadurch erscheint auch der Klassenname direkt in der IntelliSense-Liste. Wie dies genau gelingt, erläutern wir weiter unten.
tblArtikel statt rst
Neben der Verwendung von IntelliSense haben wir noch ein cooles Feature zu der Klasse hinzugefügt. Damit können Sie nun direkt über den Tabellennamen auf die enthaltenen Daten zugreifen statt erst ein Recordset erstellen zu müssen. Das sieht dann etwa wie folgt aus:
Public Sub Test() With tblArtikel Do While Not .EOF Debug.Print .ArtikelID, .Artikelname .MoveNext Loop End With End Sub
Sie können also einfach etwa in einer Do While-Schleife auf die Eigenschaft EOF der Klasse tblArtikel zugreifen, ohne diese zuvor als Recordset deklarieren und füllen zu müssen.
Neben den einzelnen Feldern stehen auch noch ein paar weitere Methoden und Eigenschaften wie MoveFirst, MoveLast, MovePrevious, MoveNext oder auch FindFirst zur Verfügung. All dies erreichen wir durch entsprechende Methoden in der Klasse, die so wie in Listing 1 aussehen.
Dim db As DAO.Database Dim rst As DAO.Recordset Private Sub Class_Initialize() Set db = CurrentDb Set rst = db.OpenRecordset("SELECT * FROM tblArtikel", dbOpenDynaset) End Sub Public Sub FindFirst(strFilter As String) rst.FindFirst strFilter End Sub Public Function FindFirstX(strFilter As String) As tblArtikel Dim objX As tblArtikel Set objX = New tblArtikel objX.FindFirst strFilter Set FindFirstX = objX End Function Public Sub MoveNext() rst.MoveNext End Sub Public Sub MovePrevious() rst.MovePrevious End Sub Public Sub MoveFirst() rst.MoveFirst End Sub Public Sub MoveLast() rst.MoveLast End Sub Public Function Count() As Long Dim var As Variant var = rst.Bookmark rst.MoveLast Count = rst.RecordCount rst.MoveFirst rst.Bookmark = var End Function Public Function Filter(strFilter As String) Set rst = db.OpenRecordset("SELECT * FROM tblArtikel WHERE " _ & strFilter, dbOpenDynaset) End Function Public Sub ClearFilter() Set rst = db.OpenRecordset("SELECT * FROM tblArtikel", dbOpenDynaset) End Sub Public Function EOF() As Boolean EOF = rst.EOF End Function Public Function BOF() As Boolean BOF = rst.BOF End Function
Listing 1: Elemente der Klasse tblArtikel
Die Klasse erstellt also direkt beim Initialisieren ein Database-Objekt und ein Recordset-Objekt mit allen Datensätzen der Tabelle. über die Methoden und Eigenschaften können Sie dann genau wie bei einem Recordset-Objekt auf die Tabelle zugreifen.
Für jede Tabellenklasse stehen dann die folgenden Eigenschaften, Methoden und Funktionen zur Verfügung:
- FindFirst: Verschiebt den Datensatzzeiger auf den ersten Datensatz, der dem angegebenen Suchkriterium entspricht
- FindFirstX: Liefert ein Recordset-Objekt, dessen Datensatzzeiger auf den ersten Datensatz zeigt, der dem angegebenen Suchkriterium entspricht
- MoveNext: Verschiebt den Datensatzzeiger zum folgenden Datensatz
- MovePrevious: Verschiebt den Datensatzzeiger zum vorherigen Datensatz
- MoveFirst: Verschiebt den Datensatzzeiger zum ersten Datensatz
- MoveLast: Verschiebt den Datensatzzeiger zum letzten Datensatz
- Count: Liefert die Anzahl der enthaltenen Datensätze. Im Gegensatz zu Recordcount liefert dies immer das korrekte Ergebnis, da der Datensatzzeiger zur Ermittlung auf den letzten Datensatz verschoben wird
- Filter: Filtert das Recordset, das über den Tabellennamen beziehungsweise die Klasse geliefert wird, entsprechend dem angegebenen Filterkriterium
- ClearFilter: Hebt den Filter wieder auf.
- EOF: Gibt an, ob der Datensatzzeiger sich hinter dem letzten Datensatz befindet.
- BOF: Gibt an, ob sich der Datensatzzeiger vor dem ersten Datensatz befindet.
Was hat es nun mit der Funktion FindFirstX auf sich FindFirst verschiebt ja nur den Datensatzzeiger auf den gesuchten Datensatz. Wenn Sie auf einen der Werte zugreifen möchten, müssen Sie diesen in einer weiteren Anweisung abfragen:
tblArtikel.FindFirst "ArtikelID = 10"
Debug.Print tblArtikel.Artikelname
FindFirstX hingegen liefert als Ergebnis direkt das Recordset mit dem verschobenen Datensatzzeiger. Das heißt, dass Sie über die Eigenschaften der Klasse, also etwa die Feldnamen, direkt auf den gefundenen Datensatz zugreifen können:
Debug.Print tblArtikel.FindFirstX ("ArtikelID=10").Artikelname
Bild 3 zeigt, dass Sie auch per IntelliSense auf die Felder des so ermittelten Datensatzes zugreifen können.
Bild 3: Direkter Zugriff auf das Ergebnis einer Suche
Aber es kommt noch besser: Durch einen kleinen Trick, den wir weiter unten erläutern, sorgen wir dafür, dass Sie die Funktion FindFirstX gar nicht angeben müssen, sondern das Suchkriterium gleich als Parameter der Tabellenklasse eingeben können:
tblArtikel("ArtikelID=10").Artikelname
Natürlich gelingt auch dies mithilfe von IntelliSense (s. Bild 4).
Bild 4: Die Suche steht auch als Standard-Member zur Verfügung.
Interessant ist auch noch die Ermittlung der Anzahl der Datensätze des Recordsets. Wenn Sie dies etwa bei einem mit dem Parameter dbOpenDynaset geöffneten Recordset erledigen möchten, müssen Sie zunächst zum letzten Datensatz navigieren, damit die Anzahl der Datensätze korrekt erfasst und mit der Eigenschaft RecordCount ausgegeben werden kann.
Dies erledigt die Eigenschaft Count in einem Zuge: Sie merkt sich die aktuelle Position des Datensatzzeigers, bewegt diesen zum letzten Datensatz, ermittelt die Anzahl der Datensätze und verschiebt den Datensatzzeiger zurück zur zuvor gemerkten Position.
Interessant sind auch noch die beiden Funktionen zum Setzen des Filters und zum Aufheben des Filters. Die Funktion Filter erstellt ein neues Recordset auf Basis der betroffenen Tabelle mit dem angegebenen Filterkriterium. Dabei wird das neu erstellte Recordset wieder der Variablen rst der Klasse zugewiesen, wodurch direkt wieder über die Methoden und Eigenschaften auf das gefilterte Recordset zugegriffen werden kann. Die Methode ClearFilter füllt das Recordset lediglich wieder mit der beim Instanzieren der Klasse verwendeten Datenherkunft, also allen Datensätzen der betroffenen Tabelle.
Tabellenklassen per Code erzeugen
Nun wäre es etwas viel Aufwand, für jede Tabelle von Hand eine entsprechende Klasse zu erzeugen. Deshalb haben wir eine Prozedur geschrieben, welche die Klassen für alle mit tbl beginnenden Tabellen der Datenbank anlegt.
Diese kopieren Sie einfach in ein Standardmodul und starten sie. Achtung: Sie benötigen einen Verweis auf die Bibliothek Microsoft Visual Basic For Applications Extensibility 5.3.
Was macht die Prozedur aus Listing 2 nun Sie erstellt zunächst einen Verweis auf das aktuelle VBA-Projekt, denn diesem sollen ja im Anschluss einige neue Klassen hinzugefügt werden – genau genommen für jede Tabelle, die mit tbl beginnt, eine.
Public Sub TabelleZuKlasse() Dim db As DAO.Database Dim tdf As DAO.TableDef Dim objVBProject As VBIDE.VBProject Dim strCode As String Set objVBProject = VBE.ActiveVBProject Set db = CurrentDb KlassenLoeschen db, objVBProject For Each tdf In db.TableDefs If tdf.Name Like "tbl*" Then strCode = CodeZusammenstellen(tdf) KlasseHinzufuegen objVBProject, strCode, tdf End If Next tdf Set db = Nothing End Sub
Listing 2: Prozedur zum Erstellen der Tabellenklassen
Dann ruft die Prozedur eine weitere Routine namens KlassenLoschen auf, die sich um das Löschen eventuell bereits vorhandener gleichnamiger Klassen kümmert.
Bedenken Sie, dass die Klassen nach änderungen am Datenmodell neu erstellt werden sollten, damit beispielsweise hinzugekommene Felder als Eigenschaften abgebildet werden.
Danach durchläuft die Prozedur in einer For Each-Schleife alle TableDef-Objekte der Datenbank und ruft für alle Einträge, deren Name mit tbl beginnt, die Funktion CodeZusammenstellen auf.
Diese stellt den eigentlichen Klassencode zusammen und gibt diesen an die Variable strCode zurück. Für jedes TableDef-Objekt ruft die Prozedur innerhalb der For Each-Schleife außerdem die Routine KlasseHinzufügen auf. Diese erstellt die Klasse, fügt den Code hinzu und fügt die Klasse zum VBA-Projekt hinzu – mehr dazu weiter unten.
Bestehende Tabellenklassen löschen
Damit kommen wir zu den drei Routinen, welche die Teilaufgaben übernehmen. Die erste löscht eventuell bestehende Tabellenklassen, also solche, die bereits zuvor mit der gleichen Routine erstellt wurden. Beachten Sie, dass nur solche Klassen gelöscht werden, die mit dem Präfix tbl beginnen. Die Routine heißt KlassenLoeschen und ist in Listing 3 zu finden. Sie durchläuft zunächst in einer For Each-Schleife alle Elemente der TableDefs-Auflistung, also alle Tabellen der Datenbank. Dabei prüft sie in einer If…Then-Bedingung, ob der Name des TableDef-Objekts mit tbl beginnt. Ist dies der Fall, wird die Fehlerbehandlung ausgeschaltet und das gleichnamige VBComponent-Objekt gelöscht. Danach schaltet die Prozedur die Fehlerbehandlung wieder ein. Auf die gleiche Weise werden alle Klassen gelöscht, deren Name mit dem Namen einer der Tabellen der Anwendung übereinstimmt, die mit tbl beginnen.
Public Sub KlassenLoeschen(db As DAO.Database, objVBProject As VBProject) Dim tdf As DAO.TableDef Dim objVBComponent As VBComponent Dim i As Integer For Each tdf In db.TableDefs If tdf.Name Like "tbl*" Then On Error Resume Next objVBProject.VBComponents.Remove objVBProject.VBComponents(tdf.Name) On Error GoTo 0 End If Next tdf For i = objVBProject.VBComponents.Count To 1 Step -1 Set objVBComponent = objVBProject.VBComponents(i) If objVBComponent.Name Like "tbl*" Then If MsgBox("Klasse ''" & objVBComponent.Name & "'' löschen " _ & "Es konnte keine passende Tabelle mehr gefunden werden.", vbYesNo) = vbYes Then objVBProject.VBComponents.Remove objVBComponent End If End If Next i End Sub
Listing 3: Löschen der bereits vorhandenen Klassen, deren Name mit tbl beginnt
Möglicherweise hat der Entwickler aber in der Zwischenzeit eine oder mehrere Tabellen gelöscht, ohne die entsprechenden Klassen zu entfernen. Für diesen Fall haben wir der Routine noch eine weitere Schleife hinzufügt, die alle VBComponent-Objekte durchläuft und prüft, ob nach dem ersten Löschdurchlauf noch Klassenmodule vorhanden sind, deren Name mit tbl beginnt. Dazu verwenden wir eine For…Next-Schleife, die rückwärts die Werte von der Anzahl aller noch vorhandenen VBComponent-Objekte bis 1 durchläuft (Step -1). Das wir diesen Weg statt der sonst üblichen For Each-Schleife verwenden, liegt daran, dass wir hier gegebenenfalls Einträge löschen, was die Auflistung durcheinander bringt und zu Fehlern führen kann. Alle Elemente, die noch mit tbl beginnen, werden auf diese Weise ebenfalls gelöscht, allerdings erst nach Rückfrage per MsgBox-Anweisung.
Zusammenstellen des Klassencodes
Die danach aufgerufene Funktion CodeZusammenstellen erwartet als Parameter lediglich einen Verweis auf das TableDef-Objekt für die Tabelle, zu welcher der Code zusammengestellt werden soll (s. Listing 4). Der hier abgebildete erste Teil des Listings stellt zunächst die Anweisungen zum Deklarieren eines Database– und eines Recordset-Objekts dar. Außerdem fügt die Funktion das Ereignis Class_Initialize hinzu, welches beim Instanzieren der Klasse ausgelöst wird. Diese füllt die beiden Variablen db und rst mit dem Verweis auf das aktuelle Database-Objekt sowie auf die betroffene Tabelle. Die Methode FindFirst erlaubt das Suchen nach dem ersten Datensatz mit dem angegebenen Filterausdruck.
Public Function CodeZusammenstellen(tdf As DAO.TableDef) Dim strCode As String Dim fld As DAO.Field strCode = strCode & "Dim db As DAO.Database" & vbCrLf strCode = strCode & "Dim rst As DAO.Recordset" & vbCrLf strCode = strCode & vbCrLf strCode = strCode & "Private Sub Class_Initialize()" & vbCrLf strCode = strCode & " Set db = CurrentDb" & vbCrLf strCode = strCode & " Set rst = db.OpenRecordset(""SELECT * FROM " _ & tdf.Name & """, dbOpenDynaset)" & vbCrLf strCode = strCode & "End Sub" & vbCrLf strCode = strCode & vbCrLf strCode = strCode & "Public Sub FindFirst(strFilter As String)" & vbCrLf strCode = strCode & " rst.FindFirst strFilter" & vbCrLf strCode = strCode & "End Sub" & vbCrLf strCode = strCode & vbCrLf End Function
Listing 4: Zusammenstellen des Codes der Klassen, Teil 1
Weiter geht es in Listing 5. Hier wird zunächst die öffentliche Funktion namens FindFirstX hinzugefügt, die allerdings etwas anders deklariert ist wie FindFirst – nämlich als Function– und nicht als Sub-Prozedur. Dies hat den Hintergrund, dass diese Funktion gleich ein Recordset mit dem Datensatzzeiger auf dem Datensatz mit dem gewünschten Kriterium liefern soll. Später erklären wir noch, wie diese Funktion als Standardfunktion definiert wird, damit Sie etwa mit tblArtikel(„ArtikelID = 10“).Artikelname direkt auf einen gewünschten Artikelnamen zugreifen können. Die übrigen Anweisungen ergänzen die Variable strCode um die anzulegenden Codezeilen für die übrigen Eigenschaften der Tabellenklasse.
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
den kompletten Artikel im PDF-Format mit Beispieldatenbank
diesen und alle anderen Artikel mit dem Jahresabo