Tabellen per IntelliSense

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.

Auswahl der Tabelle per IntelliSense ...

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

... und auch die Feldnamen stehen zur Verfügung!

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.

Direkter Zugriff auf das Ergebnis einer Suche

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).

Die Suche steht auch als Standard-Member zur Verfügung.

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

Schreibe einen Kommentar