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.

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