Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.
Kennen Sie das auch Sie konzentrieren sich gerade mal wieder voll auf die Programmierung einer Prozedur und wollen eine SQL-Anweisung zusammenstellen. Dummerweise fällt Ihnen der Name der Tabelle und/oder des Feldes nicht ein, das Sie dort verwenden möchten. Also wechseln Sie zum Access-Fenster, ermitteln dort den gesuchten Namen und kehren wieder zum VBA-Editor zurück. Dort ergänzen Sie dann die gesuchte Anweisung. Damit ist jetzt Schluss: Dieser Beitrag stellt nämlich eine einfache Lösung vor, mit der Sie die Namen aller Tabellen, Abfragen und Felder ganz einfach per Kontextmenü nachschlagen und zum Code hinzufügen können.
Mir selbst passiert es immer wieder, dass ich nicht mehr genau weiß, wie ein bestimmtes Feld einer Tabelle oder Abfrage heißt, das ich für eine Anweisung im VBA-Code benötige. Hieß das Feld nun Anzahl oder Menge Werden die E-Mail-Adressen unter EMail oder EMailAdresse gespeichert Und was ist mit Strasse, Strasse_Firma, StrasseFirma oder FirmaStrasse Ganz abgesehen von den Feldnamen kann man sich mit wachsender Komplexität einer Anwendung auch nicht mehr alle Tabellennamen merken – oder auch die Namen von Abfragen.
Und wenn wir schon einmal dabei sind: Was geschieht mit den übrigen Objekten wie Formularen und Berichten und den darin enthaltenen Steuerelementen Sie sehen schon: Die Idee, die Namen von Objekten, Feldern und Steuerelementen per Kontextmenü im VBA-Codefenster zur Verfügung zu stellen, liefert schnell eine große Anzahl Anwendungsmöglichkeiten.
Wie soll das Ganze aber nun funktionieren – was haben Sie von der in diesem Beitrag vorgestellten Lösung zu erwarten Die Ausgangssituation ist, dass Sie den VBA-Editor geöffnet haben und programmieren.
Bei einer Access-Anwendung kommt es nur selten vor, dass Sie größere Mengen Code produzieren, ohne die Objekte der Datenbank wie Tabellen, Abfragen, Formulare oder Berichte zu referenzieren. Sie dürfen dann beispielsweise Ausdrücke wie Forms!frmKunden!sfmEMailadressen.Form!txtEMail aus dem Handgelenk schütteln. Wenn Sie denn die Namen aller Objekte direkt im Kopf haben. Falls nicht, müssen Sie immer wieder zum Access-Fenster wechseln und dort nachsehen, wie Formular, Unterformular oder Steuerelemente heißen.
Die hier beschriebene Lösung soll diese Informationen direkt im VBA-Editor verfügbar machen – leicht zu erreichen per Kontextmenü. Sprich: Sie markieren die Stelle im Code, an der eine Referenz auf das gewünschte Objekt/Steuerelement eingefügt werden soll, und betätigen dann die rechte Maustaste. Das Kontextmenü, das nun üblicherweise erscheint, reichern wir mit einem Untereintrag etwa namens Objekte an. Dieses enthält weitere Untereinträge wie Tabellen, Abfragen, Formulare und Berichte.
Der Eintrag Tabellen liefert wiederum die Namen aller verfügbaren Tabellen, die Ebene darunter die Liste der Felder einer jeden Tabelle. Bei den übrigen Objekttypen sieht es ähnlich aus. Bild 1 zeigt beispielsweise, wie Sie den Namen einer Tabelle für den Einsatz in einer OpenRecordset-Methode nachschlagen können.
Bild 1: Tabellenname vergessen und keine Lust, zum Access-Fenster zu wechseln Kein Problem!
Nach dem Auswählen eines der Elemente wird der entsprechende Ausdruck an der angeklickten Stelle eingefügt.
Passendes Kontextmenü finden
Wenn wir einem bestimmten Kontextmenü einen oder mehrere Einträge hinzufügen möchten, müssen wir zunächst herausfinden, wie das Kontextmenü heißt.
Dazu wende ich immer einen einfachen Trick an: Ich füge allen verfügbaren Kontextmenüs einen weiteren Eintrag hinzu, der schlicht und einfach den Namen des Kontextmenüs enthält. Dann brauche ich das Kontextmenü nur noch anzuzeigen und erhalte seinen Namen (s. Listing 1). Die Prozedur deklariert ein CommandBar– und ein CommandBarButton-Objekt. Es durchläuft dann in einer For Each-Schleife die Auflistung CommandBars des VBE-Objekts. Die Angabe von VBE ist wichtig – wenn Sie nur CommandBars angeben, liefert dies die Auflistung der Menüs der Access-Anwendung, nicht des VBA-Editors.
Public Sub KontextmenueFinden() Dim cbr As CommandBar Dim cbb As CommandBarButton For Each cbr In VBE.CommandBars If cbr.Type = msoBarTypePopup Then On Error Resume Next Set cbb = cbr.Controls.Add(msoControlButton, , , , True) With cbb cbb.Caption = "Menüname: " & cbr.Name End With On Error GoTo 0 End If Next cbr End Sub
Listing 1: Hinzufügen eines Eintrags mit dem Menünamen zu den einzelnen Kontextmenüs
Wenn das aktuell durchlaufene Objekt den Typ msoBarTypePopup aufweist, handelt es sich um ein Kontextmenü – diesem wollen wir nun ein neues Element hinzufügen. Dies erledigen wir mit der Add-Methode der Controls-Auflistung des CommandBar-Objekts. Dabei verwenden Sie als ersten Parameter die Konstante msoControlButton (für die Erstellung einer Schaltfläche) und für den letzten Parameter Temporary den Wert True – auf diese Weise verschwinden die Einträge beim Schließen von Access automatisch wieder.
Für das neue CommandBarButton-Objekt stellen wir als Beschriftung (Caption) den Ausdruck Menüname und den Inhalt der Name-Eigenschaft des Menüs hinzu, also etwa Menüname Code Window. Wenn Sie diese Prozedur einmalig ausgeführt haben, brauchen Sie nur noch mit der rechten Maustaste an die gewünschte Stelle im Codefenster zu klicken – schon finden Sie im untersten Eintrag den Namen des aktuell angezeigten Kontextmenüs (s. Bild 2). Ein Rechtsklick auf das VBA-Codefenster zeigt also beispielsweise das Kontextmenü Code Window an.
Bild 2: Anzeige des Kontextmenünamens per Kontextmenü
Welche Objekte – und wie
Bevor wir in den Code einsteigen, überlegen wir uns noch, welche Elemente verwendet werden sollen und wann und wie wir diese einlesen.
Wenn Sie nur die Tabellen verwenden möchten, können Sie das Kontextmenü je nach der Anzahl der Tabellen vermutlich direkt vor dem Anzeigen erstellen. Wenn Sie jedoch alle Tabellen, Abfrage, Formulare und Berichte samt Steuerelementen nutzen möchten, können Sie das Kontextmenü nicht bei jeder Anzeige dynamisch erstellen – dies würde viel zu viel Zeit in Anspruch nehmen. Sie müssen, wenn Sie auch die Steuerelemente der Formulare auswählen möchten, immerhin alle Formulare im Entwurf öffnen und die Steuerelemente (sowie eventuell verschachtelte Elemente wie Unterformular) einlesen.
Wir wollen die umfangreiche Variante wählen und gehen folgendermaßen vor:
Beim Start der Anwendung muss eine Prozedur einmalig aufgerufen werden. Diese liest alle Objekte und deren Felder beziehungsweise Steuerelemente ein. Damit dies nicht jedes Mal nötig ist, speichern wir allerdings auch gleichzeitig den Zeitpunkt der letzten änderung eines jeden Objekts. Auf diese Weise können wir prüfen, ob sich das Objekt seit dem letzten Zusammenstellen der Daten für das Kontextmenü geändert hat, und brauchen die Inhalte dann nur bei Bedarf neu einzulesen.
Die ermittelten Informationen speichern wir in einer Tabelle namens tblObjekte. Im gleichen Zuge, also beim Start der Anwendung, bauen wir auch das Kontextmenü neu auf. Dies ist auch nötig, wenn Sie während einer Sitzung umfangreiche änderungen vornehmen und direkt auf die neuen Elemente zugreifen möchten – die geänderten Objekte werden dann erneut eingelesen und das Kontextmenü auf Basis des aktuell in der Tabelle tblObjekte gespeicherten Stands erstellt. Die Tabelle tblObjekte sieht im Entwurf wie in Bild 3 aus. Sie enthält die folgenden Felder:
Bild 3: Tabelle zum Zwischenspeichern der Objekte
- ObjektID: Primärschlüsselfeld der Tabelle
- Objekt: Name des Objekts (Tabelle, Abfrage, Formular, Bericht, Feld, Steuerelement)
- Objekttyp: Zahlenwert, der den Objekttyp angibt
- UnterobjektVonObjektID: Gibt an, ob es sich um ein untergeordnetes Objekt handelt (etwa für Felder oder Steuerelemente)
- LetzteAenderung: Enthält Datum und Zeit der letzten änderung
- Loeschen: Ja/Nein-Feld, mit dem festgelegt wird, welche Elemente nach dem erneuten Einlesen gelöscht werden sollen
- EnthaeltUnterformular: Gibt an, welches Unterformular ein Unterformular-Steuerelement enthält.
- Referenz: Enthält den Ausdruck, der beim Einfügen im VBA-Code verwendet werden soll – zum Beispiel Forms!frmFormular!txtTextfeld.
Einlesen der Elemente
Die Prozedur ObjekteEinlesen aus Listing 2 startet den Einlesevorgang der Elemente der aktuell geöffneten Datenbankdatei. Die Prozedur füllt zunächst eine Variable vom Typ Database mit einem Verweis auf die aktuelle Datenbank. Dann verwendet sie deren Execute-Methode, um das Feld Loeschen für alle Datensätze der Tabelle tblObjekte auf den Wert True einzustellen. Nachfolgend stellt die Prozedur für alle noch vorhandenen Elemente den Wert dieses Feldes auf False ein. So kann sie mit der letzten Anweisung alle Elemente löschen, die gegebenenfalls seit dem vorherigen Einlesevorgang gelöscht wurden.
Public Sub ObjekteEinlesen() Dim datLetzteAenderung As Date Dim datLetzteGespeicherteAenderung As Date Dim db As dao.Database Dim tdf As dao.TableDef Dim qdf As dao.QueryDef Dim obj As AccessObject Dim frm As Form Dim lngID As Long Set db = CurrentDb db.Execute "UPDATE tblObjekte SET Loeschen = -1", dbFailOnError For Each tdf In db.TableDefs TabellenAbfragenFelder db, tdf, 0 Next tdf For Each qdf In db.QueryDefs TabellenAbfragenFelder db, qdf, 1 Next qdf For Each obj In CurrentProject.AllForms datLetzteAenderung = obj.DateModified datLetzteGespeicherteAenderung = Nz(DLookup("LetzteAenderung", _ "tblObjekte", "Objekt = ''" & obj.Name & "''")) If CSng(datLetzteGespeicherteAenderung) < CSng(datLetzteAenderung) _ Or datLetzteGespeicherteAenderung = 0 Then DoCmd.OpenForm obj.Name, acDesign Set frm = Forms(obj.Name) FormulareBerichteSteuerelemente db, frm, -1, _ datLetzteAenderung, datLetzteGespeicherteAenderung DoCmd.Close acForm, obj.Name Else lngID = DLookup("ObjektID", "tblObjekte", "Objekt = ''" & obj.Name & "'' AND Objekttyp = -1") db.Execute "UPDATE tblObjekte SET Loeschen = 0 " _ & "WHERE ObjektID = " & lngID, dbFailOnError db.Execute "UPDATE tblObjekte SET Loeschen = 0 " _ & "WHERE UnterobjektVonObjektID = " & lngID, dbFailOnError End If Next obj For Each obj In CurrentProject.AllReports db.Execute "INSERT INTO tblObjekte(Objekt, Objekttyp) VALUES(''" _ & obj.Name & "'', " & acForm & ")", dbFailOnError Next obj db.Execute "DELETE FROM tblObjekte WHERE Loeschen = -1", dbFailOnError End Sub
Listing 2: Einlesen der Tabellen, Abfrage und Formulare sowie Felder und Steuerelemente
Doch eins nach dem anderen: Zunächst einmal durchläuft die Prozedur alle Elemente der TableDefs-Auflistung der Datenbank und referenziert diese jeweils mit der Objektvariablen tdf. Damit ruft sie die Prozedur TabellenAbfragenFelder auf, die sich um das Schreiben der Informationen zur jeweiligen Tabelle in die Tabelle tblObjekte kümmert. Dazu übergibt die Prozedur den Verweis auf die Datenbank (db) und auf das TableDef-Element (tdf) sowie den Wert 0 als Typ des Elements (0 entspricht einer Tabelle, 1 einer Abfrage). Diese Prozedur trägt je einen Datensatz für die Tabelle und je einen weiteren für jedes Tabellenfeld in die Tabelle tblObjekte ein. Wir schauen uns diese Prozedur weiter unten an.
Anschließend durchläuft die Prozedur ObjekteEinlesen in einer ähnlichen For Each-Schleife die QueryDef-Objekte der Datenbank, also die Abfragen. Auch hier ruft die Prozedur für jedes Element die Prozedur TabellenAbfragenFelder auf, die wir weiter unten beschreiben. Diesmal übergibt die Anweisung jedoch den Verweis auf das QueryDef-Objekt als zweiten Parametern (qdf).
Schließlich durchläuft die Prozedur alle Elemente der Auflistung AllForms des Objekts CurrentProject und referenziert das jeweilige Objekt mit der Variablen obj.
Innerhalb der Schleife ermittelt die Prozedur nun zunächst das Datum der letzten änderung des aktuellen Formulars und speichert dieses in der Variablen datLetzteAenderung. Außerdem durchsucht sie die Tabelle tblObjekte nach einem eventuell bereits gespeicherten Datensatz für das aktuelle Formular-Objekt. Ist eines vorhanden, liest sie den Wert des Feldes LetzteAenderung für dieses Formular in die Variable datLetzteGespeicherteAenderung ein.
Dann vergleicht die Prozedur die Werte von datLetzteGespeicherteAenderung und datLetzteAenderung. Wurde das Formular nach dem letzten Speichern in der Tabelle tblObjekte geändert, ist datLetzteGespeicherteAenderung kleiner als datLetzteAenderung und die in der entsprechenden If…Then-Bedingung enthaltenen Anweisungen werden ausgeführt – sprich: Das Formular wird erneut eingelesen. Dazu öffnet die Prozedur das Formular in der Entwurfsansicht und referenziert es mit der Variablen frm. Diese wird dann, nebst einigen weiteren Informationen, an die Prozedur FormulareBerichteSteuerelemente übergeben – mehr dazu weiter unten in der Beschreibung dieser Prozedur. Nach dem Einlesen schließt die Prozedur ObjekteEinlesen das im Entwurf geöffnete Formular wieder.
Sollte datLetzteGespeicherteAenderung nicht kleiner als datLetzte-Aenderung sein, sind eigentlich keine weiteren Schritte nötig. Allerdings haben wir ja zu Beginn der Prozedur das Feld Loeschen für alle Elemente auf True eingestellt. Wenn sich an einem Objekt jedoch nichts geändert hat, soll es auch nicht gelöscht werden – also stellen wir im Else-Teil der If…Then-Bedingung den Wert von Loeschen auf False ein. Dazu ermittelt die Prozedur zunächst den Primärschlüsselwert aus dem Feld ObjektID für das Objekt mit dem angegebenen Namen und dem Wert -1 im Feld Objekttyp. Dann stellt sie das Feld Loeschen zunächst für den so ermittelten Datensatz auf False ein und dann für alle Datensätze, die diesem untergeordnet sind und dementsprechend den ermittelten Zahlenwert im Feld UnterobjektVonObjektID aufweisen – in diesem Fall also für die Steuerelemente des Formulars.
Schließlich löscht die Prozedur alle Datensätze der Tabelle tblObjekte, deren Feld Loeschen auch nach dem Durchlaufen und gegebenenfalls Aktualisieren aller Objekte und Unterobjekte noch den Wert True hat.
Eintragen von Tabellen, Abfragen und Feldern in tblObjekte
Nachdem der grundlegende Ablauf von der Prozedur ObjekteEinlesen gesteuert wird, ruft diese zwei weitere Prozeduren auf, um die Daten der Objekte wie Tabellen, Abfragen oder Formulare in der Tabelle tblObjekte zu speichern.
Wir schauen uns als Erstes die Prozedur TabellenAbfragenFelder an, die in Listing 3 abgebildet ist. Die Prozedur erwartet die folgenden Parameter:
Public Sub TabellenAbfragenFelder(db As dao.Database, objTdfQdf As Object, lngObjekttypID As Long) Dim lngObjektID As Long Dim fld As dao.Field Dim datLetzteAenderung As Date Dim datLetzteGespeicherteAenderung As Date datLetzteAenderung = objTdfQdf.LastUpdated datLetzteGespeicherteAenderung = Nz(DLookup("LetzteAenderung", "tblObjekte", _ "Objekt = ''" & objTdfQdf.Name & "''")) If Not datLetzteGespeicherteAenderung = 0 Then lngObjektID = DLookup("ObjektID", "tblObjekte", "Objekt = ''" & objTdfQdf.Name & "''") If CSng(datLetzteGespeicherteAenderung) < CSng(datLetzteAenderung) Then db.Execute "UPDATE tblObjekte SET LetzteAenderung = " & ISODatum(datLetzteAenderung) _ & ", Loeschen = 0 WHERE ObjektID = " & lngObjektID, dbFailOnError db.Execute "DELETE FROM tblObjekte WHERE UnterobjektVonObjektID = " & lngObjektID For Each fld In objTdfQdf.Fields db.Execute "INSERT INTO tblObjekte(Objekt, Objekttyp, " _ & "UnterobjektVonObjektID, Referenz) VALUES(''" & fld.Name _ & "'', " & lngObjekttypID & ", " & lngObjektID & ", ''" & objTdfQdf.Name _ & "." & fld.Name & "'')", dbFailOnError Next fld Else db.Execute "UPDATE tblObjekte SET Loeschen = 0 WHERE ObjektID = " _ & lngObjektID, dbFailOnError db.Execute "UPDATE tblObjekte SET Loeschen = 0 WHERE UnterobjektVonObjektID = " _ & lngObjektID, dbFailOnError End If Else db.Execute "INSERT INTO tblObjekte(Objekt, Objekttyp, " _ & "LetzteAenderung, Referenz) VALUES(''" & objTdfQdf.Name & "'', " _ & lngObjekttypID & ", " & ISODatum(datLetzteAenderung) _ & ", ''" & objTdfQdf.Name & "'')", dbFailOnError lngObjektID = db.OpenRecordset("SELECT @@IDENTITY").Fields(0) For Each fld In objTdfQdf.Fields db.Execute "INSERT INTO tblObjekte(Objekt, Objekttyp, " _ & "UnterobjektVonObjektID, Referenz) VALUES(''" & fld.Name _ & "'', " & lngObjekttypID & ", " & lngObjektID & ", ''" _ & objTdfQdf.Name & "." & fld.Name & "'')", dbFailOnError Next fld End If End Sub
Listing 3: Eintragen der Tabellen und Felder in die Tabelle tblObjekte
- db: Verweis auf die aktuelle Datenbank
- objTdfQdf: Verweis auf das zu untersuchende TableDef– oder QueryDef-Objekt
- lngObjectType: Objekttyp, hier 0 (Tabelle) oder 1 (Abfrage)
Die Prozedur liest zunächst den Zeitpunkt der letzten Aktualisierung der Tabelle oder Abfrage aus der Eigenschaft LastUpdated in die Variable datLetzteAenderung ein. Dann ermittelt sie, wiederum per DLookup-Funktion, den Zeitpunkt der letzten Speicherung der Daten dieser Tabelle oder Abfrage in der Tabelle tblObjekte.
Dann prüft die Prozedur TabellenAbfragenFelder, ob der mit der Nz-Funktion ermittelte Wert von datLetzteGespeicherteAktualisierung nicht den Wert 0 enthält. Dies ist der Fall, wenn dieses Objekt noch gar nicht gespeichert wurde, und führt dazu, dass das Objekt im ersten Teil der If…Then-Bedingung aktualisiert und im zweiten Teil neu angelegt wird.
Der erste Teil der If…Then-Bedingung unterscheidet wiederum, ob die letzte Speicherung in der Tabelle tblObjekte älter als die aktuelle Version der Tabelle oder Abfrage ist.
Falls ja, aktualisiert sie die Informationen zu dieser Tabelle oder Abfrage, falls nein, aktualisiert sie nur das Feld Loeschen für die Tabelle/Abfrage und die enthaltenen Felder auf den Wert False.
Wenn die Daten aktualisiert werden müssen, weil das Objekt zwischenzeitlich geändert wurde, führt die Prozedur zunächst eine UPDATE-Abfrage aus, welche den Zeitpunkt der letzten änderung auf die aktuelle Zeit und das Feld Loeschen auf den Wert False einstellt.
Die Felder der Tabelle oder Abfrage wollen wir in diesem Fall gleich gründlich aktualisieren – und zwar durch Löschen und erneutes Anlegen. Dies erledigen die beiden folgenden DELETE– und INSERT INTO-Anweisungen. Letztere wird dabei in einer For Each-Schleife über alle Field-Objekte ausgeführt und legt so für jedes Feld der soeben untersuchten Tabelle oder Abfrage einen Datensatz in der Tabelle tblObjekte an. Dabei ist wichtig, dass das Feld UnterobjektVonObjektID den Primärschlüsselwert des Eintrags erhält, der die Tabelle oder Abfrage selbst ausweist.
Sollten keine änderungen vorliegen, trägt die Prozedur einfach nur für alle betroffenen Datensätze (also den für die Tabelle/Abfrage und die für die einzelnen Felder) den Wert False für das Feld Loeschen ein.
Fehlt noch der Fall, dass die Tabelle oder Abfrage noch gar nicht gespeichert wurde. Die Prozedur trägt dann zunächst einen neuen Datensatz für die Tabelle oder Abfrage in die Tabelle tblObjekte ein. Sie ermittelt mit SELECT @@IDENTITY den Primärschlüsselwert des neu angelegten Datensatzes und verwendet diesen in der folgenden Schleife über alle Felder der Tabelle oder Abfrage, um in UnterobjektVonObjektID die Zuordnung des Feldes zum übergeordneten Tabelle/Abfrage-Datensatz herzustellen.
Nach dem Einlesen einiger Beispieltabellen sieht die Tabelle tblObjekte etwa wie in Bild 4 aus.
Bild 4: Die Tabelle tblObjekte mit einigen Beispieldaten
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
Testzugang
eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel
diesen und alle anderen Artikel mit dem Jahresabo