Home > Artikel > Ausgabe 1/2015 > Tabellen und Abfragen über DAO verwalten

Tabellen und Abfragen über DAO verwalten

Achtung: Sie sind nicht angemeldet. Wenn Sie Abonnent sind und sich anmelden, lesen Sie den kompletten Artikel, laden das PDF herunter oder probieren die Beispieldatenbank aus (sofern vorhanden).

Der Umgang mit den Datensätzen einer Tabelle über das Recordset-Objekt der DAO-Bibliothek ist das A und O der Programmierung unter Access und dürfte Ihnen deshalb wahrscheinlich geläufig sein. Nicht selten aber wird auch der Zugriff auf die Eigenschaften der Tabellen und Abfragen selbst benötigt – und hier kommt abermals DAO mit seinen TableDef- und QueryDef-Objekten ins Spiel.

Beispieldatenbank

Die Beispiele dieses Artikels finden Sie in der Datenbank 1501_DAO.mdb.

Tabellen und Abfragen im Objektmodell von DAO

Access legt beim Erstellen einer Datenbankdatei automatisch einen Verweis auf die DAO-Bibliothek im VBA-Projekt an, weil ohne sie keine Möglichkeit besteht, an die Datenobjekte heranzukommen. Zwar gibt es auch eine weitere Bibliothek namens ADODB, die ersatzweise verwendet werden kann, doch diese wird inzwischen von Microsoft weder weiterentwickelt, noch empfohlen. Sie war einst in erster Linie für den erweiterten Zugriff auf den SQL-Server gedacht, als Access 2000 mit den Access-Projekten (ADP) aufwartete. Doch da diese etwa von Access 2013 gar nicht mehr unterstützt werden, gibt es auch keinen triftigen Grund mehr, diese Bibliothek einzusetzen.

DAO als Abkürzung für Data Access Objects ist nur der Modellname der Bibliothek. In der Liste der Verweise finden Sie sie jedoch unter der monströsen Bezeichnung Microsoft Office 14.0 Access database engine Object Library, wobei sich das je nach Version von Access geringfügig unterscheidet. Vor Access 2007 lautete die Bezeichnung allerdings tatsächlich Microsoft DAO 3.6 Object Library.

Öffnen Sie im VBA-Editor über das Menü Ansicht den Objektkatalog und wählen aus dem Kombinationsfeld oben links den Eintrag DAO aus, wie in Bild 1. Der Katalog füllt sich dann mit allen Objektklassen, die die Datenzugriffsbibliothek aufweist, bei denen die Klasse Database von zentraler Bedeutung ist und quasi das Ebenbild der Datenbank ausmacht. Beim Klicken auf diese Klasse zeigen sich im rechten Abteil die Methoden und Eigenschaften des Database-Objekts. Da uns für das Thema dieses Beitrags nun besonders die Tabellen und Abfragen interessieren, die in einer Datenbank enthalten sind, müsste das Database-Objekt wohl irgendwelche Methoden aufweisen, um auf diese zugreifen zu können. Tatsächlich ist das mit den beiden Auflistungseigenschaften TableDefs und QueryDefs gegeben, die in Bild 1 markiert wurden. Sie sind beide Container für die einzelnen Tabellen- und Abfragen-Objekte. Dabei steht TableDef für "Tabellendefinition" und QueryDef für "Abfragedefinition".

DIe DAO-Bibliothek im VBA-Objektkatalog

Bild 1: DIe DAO-Bibliothek im VBA-Objektkatalog

Nehmen wir uns zunächst die Aufgabe vor, über diese DAO-Klassen alle Tabellen aufzulisten, die die aktuelle Datenbank enthält. Als Erstes braucht es dafür ein Database-Objekt, von dem sich dann die TableDefs-Auflistung ableitet. Allerdings ist Database ja nur eine Modellklasse und noch kein Objekt – eine Klasse muss erst in Form einer Objektvariablen instanziiert werden. Und da die DAO-Bibliothek eine externe Komponente ist, die lediglich als Modell in das VBA-Projekt eingeklinkt ist, weiß sie eigentlich auch gar nichts von der Access-Datenbank, in der sie untergebracht wurde. Es muss also ein Bezug zu einem Database-Objekt hergestellt werden, das Access bereitstellt. Das tut es auch mit der Eigenschaft CurrentDb des Access-Application-Objekts:

Dim dbs As Database

Set dbs = Application.CurrentDb

Das Application kann auch weggelassen werden, da es sich um eine globale Eigenschaft von Access handelt.

Auch das Übertragen auf eine Objektvariable dbs ist nicht zwingend notwendig, da CurrentDb jederzeit erneut abgefragt werden kann. Neben CurrentDb gibt es noch eine weitere Variante der Zuweisung:

Set dbs = Application.DbEngine(0)(0)

Die Nullen in Klammern sind eine zulässig abgekürzte Form von

Dbengine.Workspaces(0).Databases(0)

Nachdem nun ein gültiges Database-Objekt vorliegt, kann es auch an die Liste der Tabellen gehen:

CurrentDb.TableDefs.Count

Das gibt die Anzahl der in der Datenbank enthaltenen Tabellen im Direktfenster aus. Analog geht das für die Abfragen mit

CurrentDb.QueryDefs.Count

Damit Sie nicht spekulieren müssen, wie mit diesen Auflistungen weiter verfahren wird, sind in Bild 2 deren Methoden aus dem Objektkatalog dargestellt.

Methoden der TableDefs- und QueryDefs-Auflistungsklassen

Bild 2: Methoden der TableDefs- und QueryDefs-Auflistungsklassen

Count ermittelt die Anzahl der jeweiligen Objekte. Delete löscht ein Element. Da ein TableDef-Objekt direkt an die zugehörige Tabelle gekoppelt ist, bedeutet Löschen hier, dass auch die Tabelle aus der Datenbank entfernt wird:

Currentdb.TablesDefs.Delete "Tabelle1"

Tabelle1 wandert damit ins Nirwana. Append würde eine Tabelle zur Datenbank hinzufügen. Das Vorgehen ist hier allerdings nicht so einfach, wie beim Löschen, denn Append erwartet ein fertiges TableDef-Objekt, das erst über die Database-Methode CreateTableDef erzeugt und mit allerlei weiteren Eigenschaften, wie Feldern, versehen sein muss. Tabellen über VBA anlegen zu müssen, dürfte jedoch ziemlich selten vorkommen, so dass dies hier nicht weiter besprochen wird.

Refresh ist eine Methode, die die Auflistung auf den neuesten Stand bringt. Löschen Sie etwa eine Tabelle per Delete, so findet sie sich zunächst trotzdem noch in der Auflistung. Erst ein Refresh aktualisiert die Liste wieder.

Item schließlich ist der Bezug zu einem einzelnen TableDef-Objekt, das über den Index der Auflistung angesprochen wird:

CurrentDb.TablesDefs.Item(0)

Hier erhält man das TableDef-Objekt, welches auf die erste Tabelle der Datenbank verweist. Weil Item die sogenannte Default-Eigenschaft der Klasse ist, erkennbar am blauen Punkt im Objektkatalog, kann der Methodenname auch weggelassen werden:

Dim tdf As TableDef

Set tdf = CurrentDb.TableDefs(0)

Außerdem kann auch direkt der Name des Objekts als Index statt der Ordinalzahl verwendet werden:

CurrentDb.TableDefs("Tabelle1")

Mit diesem Wissen könnten Sie bereits die Routine zum Ausgeben aller Tabellen der Datenbank anfertigen. Es muss nur die Zahl der TableDefs mit Count ermittelt und dann in einer Schleife über eine Zählervariable n jedes Objekt mit Item(n) angesprochen werden. Name ist die Eigenschaft des TableDef-Objekts, welches auch dem Namen der Tabelle entspricht.

Es geht aber noch einfacher. Weil die Auflistungen COM-Enumeration unterstützen, kann man auf das Ansprechen per Index verzichten und eine For..Each-Schleife einsetzen. Der Beispielcode steht in Listing 1. Im VBA-Direktfenster wird erst die Zahl der Tabelle ausgegeben, danach jedes Element der Auflistung durchlaufen und der Name der entsprechenden Tabelle angezeigt.

Sub ListTables()

     Dim tdf As DAO.TableDef

     

     Debug.Print CurrentDb.TableDefs.Count & " Tabellen"

     For Each tdf In CurrentDb.TableDefs

         Debug.Print tdf.Name

     Next tdf

     

End Sub

Listing 1: Ausgeben aller Tabellen der Datenbank über die TableDefs-Auflistung

Wie aus Bild 2 hervorgeht, gleichen sich die Auflistungen für TableDefs und QueryDefs vollständig. Deshalb ist der Code zur Ausgabe aller Abfragen ebenfalls identisch (Listing 2).

Sub ListQueries()

     Dim qdf As DAO.QueryDef

     

     Debug.Print CurrentDb.QueryDefs.Count & " Abfragen"

     For Each qdf In CurrentDb.QueryDefs

         Debug.Print qdf.Name

     Next qdf

     

End Sub

Listing 2: Ausgeben aller Abfragen der Datenbank über die QueryDefs-Auflistung

Das TableDef-Objekt

Hat man erst einmal ein TableDef-Objekt, so lässt sich damit natürlich noch mehr anstellen, als nur die Ausgabe des Tabellennamens. Eine Übersicht aller Methoden sowohl der TableDef- als auch der QueryDef-Klassen zeigt der kombinierte Screenshot des Objektkatalogs in Bild 3. Einige davon sind nur für sehr spezielle Aufgaben von Belang, etwa für Tabellen, die über ODBC in die Datenbank verknüpft sind, oder PassThrough-Abfragen, die ebenfalls auf einen SQL-Server aufsetzen. Gleiches gilt für Tabellen, die in Datenbanken mit Replikation vorkommen - teilweise Features, die die aktuellen Versionen von Access gar nicht mehr unterstützen! Diese Methoden sind der Kompatibilität wegen noch im Objektmodell vorhanden, geben meist aber gar keine Werte mehr zurück. In Bild 3 sind alle Methoden, die Sie kaum interessieren dürften, grau hinterlegt.

TableDef- und QueryDef-Methoden

Bild 3: TableDef- und QueryDef-Methoden

Im Folgenden werden die relevantesten Methoden der TableDef-Klasse angeführt. Alles Weitere erfahren Sie in der Hilfe zu DAO, die allerdings leider nicht automatisch mit den neueren Access-Versionen installiert wird. Es gibt auch keine aktualisierte Hilfedatei zu DAO seit Access 2003. Deshalb gelangen Sie auch nicht zur Hilfe, wenn Sie etwa in Listing 2 den Ausdruck QueryDef markieren und F1 drücken. Denn in das Objektmodell ist nach wie vor ein Bezug zur Hilfedatei DAO360.chm gespeichert, der ins Leere führt, wenn sich diese Datei nicht auf ihrem System befindet.

Wenn Sie die kontextsensitive Hilfe verwenden wollen, so besorgen Sie sich diese Hilfedatei im Internet und speichern sie im Windows-Verzeichnis im Unterordner Help. Ansonsten sind Sie darauf angewiesen, die Hilfe zu Access-VBA über das Menü des VBA-Editors aufzurufen und dann in der Access Entwicklerreferenz den Zweig Microsoft-Datenzugriffsobjekte (DAO)-Referenz zu erforschen. Zurück zu den TableDef-Methoden:

DateCreated gibt zurück, wann eine Tabelle erstellt, LastUpdated den Zeitpunkt, wann sie zuletzt im Entwurf aktualisiert wurde. Fields, Indexes und Properties sind Auflistungseigenschaften, auf die wir noch gesondert zu sprechen kommen.

RecordCount ist eine interessante Eigenschaft: Sie zeigt die Zahl der in der Tabelle gespeicherten Datensätze an, soweit es sich um eine nicht-verknüpfte Tabelle handelt.

Im Gegensatz zu den missverständlichen Erläuterungen der DAO-Hilfe stimmt diese Zahl immer, auch wenn nicht, wie dort angeführt, bereits erst auf alle Datensätze zugegriffen wurde. Dies gilt nur für ein Recordset-Objekt.

Updatable kennzeichnet, ob der Inhalt einer Tabelle schreibgeschützt ist, oder aktualisierbar. Für die Systemtabellen wird hier in der Regel ein False zurückgegeben.

ValidationRule und ValidationText sind Synonyme für die Gültigkeitsregel einer Tabelle und die gegebenenfalls ausgegebene Meldung. Das sind Eigenschaften, von denen selten Gebrauch gemacht wird, da Gültigkeitsbeschränkungen meist auf Feldebene definiert werden.

Die Create-Methoden korrelieren mit den entsprechenden Auflistungen zu Fields, Indexes und Properties. Hierüber können neue Felder in einer Tabelle erzeugt, neue Indizes oder neue benutzerdefinierte Eigenschaften hinzugefügt werden.

Connect und RefreshLink haben nur Bedeutung im Zusammenhang mit verknüpften Tabellen. In Connect wird der Pfad zu einer Backend-Datenbank angegeben und mit RefreshLink erst wirklich aktualisiert. Alle anderen Tabellen zeigen für Connect einen leeren String.

OpenRecordset öffnet eine Datensatzgruppe auf die Tabelle. Das dürfte Ihnen bekannt vorkommen. Hier handelt es sich um eine Alternative zur Syntax mit

CurrentDb.OpenRecordset("tblXY")

Dasselbe Ergebnis erhalten Sie mit

Dim tdf As TableDef

Set tdf = CurrentDb.TableDefs("tblXY")

tdf.OpenRecordset

Beide Varianten sind in Listing 3 aufgeführt. Im ersten Teil kommt die konventionelle Methode zum Einsatz, im zweiten das TableDef-Objekt. Eigentlich gibt es keinen Grund, zu dieser Variante zu greifen. Sie ist hier aber aufgeführt, weil sie so schön zwei VBA-Spezialitäten demonstriert: Einmal die With-Anweisung – in dieser Ausgabe bereits besprochen – und dann die Gültigkeit eines Objekts. Machen Sie den Versuch und schreiben zum Setzen der TableDef-Variablen diesen Code:

Set tdf = _

     CurrentDb.TableDefs("tblLaender")

Set rs = tdf.OpenRecordset()

Während die erste Zeile noch anstandslos ausgeführt wird, kommt es bei der zweiten Zeile zu der Fehlermeldung, dass ein Objekt nicht mehr festgelegt sei. Dass die tdf-Variable nicht der Übeltäter sein kann, erfahren Sie über den Test im Direktfenster, nachdem Sie einen Haltepunkt auf die Zeile setzten:

tdf.Name

gibt korrekt tblLaender zurück. Was aber sollte dann nicht festgelegt sein? Es bleibt nur noch das CurrentDb-Objekt. Tatsächlich handelt es sich bei diesem um ein nichtpersistentes Objekt, welches nur eine Momentaufnahme der aktuellen Datenbank versinnbildlicht. In der zweiten Code-Zeile ist es sozusagen bereits wieder verschwunden. Die übliche Lösung für diesen Umstand besteht im zusätzlichen Anlegen einer Database-Variablen:

Dim dbs As Database

Set dbs = CurrentDb

Set tdf = dbs.TableDefs("tblLaender")

Der Clou jedoch ist, dass dies mit der With-Anweisung umgangen werden kann. Da, wie im Beitrag zu With in dieser Ausgabe bereits erläutert, der Bezug zur mit With gesetzten Objektvariablen aufrechterhalten wird, bis ein End With auftritt, funktioniert die Zuweisung an tdf auch in Listing 3 – CurrentDb bleibt bis zum End With gültig. Das ist ein netter Beleg für den sinnvollen Einsatz der With-Anweisung.

Sub OpenRecordSetTDF()

     Dim rs As DAO.Recordset

     Dim tdf As TableDef

     Set rs = CurrentDb.OpenRecordset("tblLaender", dbOpenDynaset)

     Do While Not rs.EOF

         Debug.Print rs(0).Value, rs(1).Value

         rs.MoveNext

     Loop

     rs.Close

     With CurrentDb

         Set tdf = .TableDefs!tblLaender

         Set rs = tdf.OpenRecordset(dbOpenDynaset)

     End With

     Do While Not rs.EOF

         Debug.Print rs(0).Value, rs(1).Value

         rs.MoveNext

     Loop

     rs.Close

End Sub

Listing 3: Öffnen eines Recordsets auf ein TableDef-Objekt zum Durchlaufen der Datensätze der Tabelle tblLaender

Zur TableDef-Eigenschaft Attributes später mehr.

Das QueryDef-Objekt

In vielerlei Hinsicht ähneln sich TableDef- und QueryDef-Objekt. Dennoch ist eine Abfrage nun mal keine Tabelle. Die wichtigsten abweichenden Methoden der Klasse im Überblick:

SQL ist sicher die Eigenschaft, welche im Zentrum steht. Mit ihr lässt sich der SQL-Ausdruck einer Abfrage sowohl auslesen, wie auch bestimmen. Ein Beispiel, wofür sich der lesende Zugriff eignen kann, steht in Listing 4. In einer Schleife werden hier alle Abfragen der Datenbank durchlaufen, der SQL-String ausgelesen und dabei über Instr() überprüft, ob der Ausdruck tblOrte in ihm vorkommt, was darauf schließen lässt, dass diese Tabelle als Quelle in der Abfrage verwendet wird. Falls ja, so werden Name und SQL-String im Direktfenster ausgegeben. In umfangreichen Datenbanken ist so eine Routine etwa nützlich, wenn eine Tabelle umbenannt wird, die auf ihr aufbauenden Abfragen nun aber ebenfalls modifiziert werden müssen. Schnell haben Sie so aus Dutzenden von Abfragen ermittelt, wo Sie ansetzen müssen. Die Routine können Sie leicht so umbauen, dass statt der Ausgabe im Direktfenster der SQL-String der Abfrage automatisch ersetzt wird:

Sub FindQuery()

     Dim qdf As DAO.QueryDef

     Const Expr = "tblOrte"

     For Each qdf In CurrentDb.QueryDefs

         If InStr(1, qdf.SQL, Expr) > 0 Then

             Debug.Print qdf.Name

             Debug.Print qdf.SQL

             Debug.Print

         End If

     Next qdf

End Sub

Listing 4: Ermitteln aller Abfragen der Datenbank, die den Tabellenverweis tblOrte enthalten

qdf.SQL = Replace(qdf.SQL, _

"tblOrte","tblAndernorts")

Mit wenigen Zeilen Code ersparen Sie sich also eine Menge manueller Arbeit.

Execute ist eine Methode des QueryDef-Objekts, die nur für Aktionsabfragen gültig ist. Sie führen sie damit aus. (Auswahlabfragen können nicht direkt ausgeführt werden, sondern dienen als Vorlage für ein zu öffnendes Recordset.) Beliebtes Beispiel wäre das Leeren einer Tabelle über die Delete-Anweisung von SQL:

qdf.SQL = "DELETE * FROM tblOrte"

qdf.Execute

Aktualisierungs-, Definitions- und Anfügeabfragen sind weitere Typen, die die Execute-Methode erlauben.

Hier kommt eine weitere Eigenschaft des QueryDef-Objekts ins Spiel. Mit ReturnsRecords können Sie ermitteln, ob eine Abfrage Datensätze zurückgibt. Sobald Sie den SQL-String der Abfrage gesetzt haben, analysiert die Access Database Engine den Ausdruck und entscheidet, ob es sich um eine Auswahlabfrage handelt. In diesem Fall gibt ReturnsRecords den Wert True zurück. Sonst handelt es sich um eine Aktionsabfrage.

Bei letzterer lässt sich die Eigenschaft RecordsAffected verwenden, um auszulesen, wie viele Datensätze von der Aktion betroffen waren. Nach Execute ist hier ein Wert gesetzt. Nach der Ausführung der Delete-Abfrage oben würde hier stehen, wie viele Datensätze die Tabelle einst hatte.

Schließlich gibt es, wie beim TableDef-Objekt, auch hier die Eigenschaft Updatable, die Ihnen mitteilt, ob die Datensätze eines auf die Abfrage geöffneten Recordsets veränderbar sind.

Zu den Auflistungseigenschaften des QueryDef-Objekts gehört neben Fields und Properties auch die Parameters-Aufzählung. Sie bezieht sich auf variable Parameter, die in einer Abfrage angegeben wurden. Dazu im Folgenden ein Beispiel.

Abfragen On The Fly mit dem QueryDef-Objekt

Während das Erstellen von Tabellen über VBA-Code nur selten benötigt wird, sieht die Sache bei Abfragen schon anders aus. Denn über eine temporäre Abfrage, die Parameter enthält, können Sie komfortabel mehrmals hintereinander unterschiedliche Ergebnisse produzieren, je nachdem, welche Werte Sie als Kriterien den Parametern verabreichen. An dieser Stelle zunächst zunächst die grundsätzliche Syntax für das Erstellen einer Abfrage über DAO, ersichtlich aus Bild 4. Der Vollständigkeit halber ist dort auch noch die Syntax für das Erstellen eines TableDef-Objekts dargestellt.

Erzeugen von Tabellen oder Abfragen per VBA

Bild 4: Erzeugen von Tabellen oder Abfragen per VBA

Per CreateQueryDef des Database-Objekts wird eine Abfrage erstellt, wobei der Name dieser angegeben werden muss und optional hier schon deren SQL-String. Man kann ihn aber auch weglassen und später über die Eigenschaft SQL zuweisen. Mit dem in Name anzugebenden Ausdruck hat es eine besondere Bewandtnis. Wird er auf einen gültigen String gesetzt, so wird die Abfrage physisch in der Datenbank unter diesem Namen angelegt. Wird stattdessen ein Leer-String angegeben, so generiert man eine temporäre Abfrage, die nur im Speicher existiert:

Dim qdf As QueryDef

Set qdf = CurrentDb.CreateQueryDef("")

Nach dem Beenden einer Routine, die diesen Code enthält, verschwindet die Abfrage wieder im Nichts.

Ein Beispiel, das sich diesen Umstand zunutze macht, finden Sie in Listing 5. Nach dem Erzeugen des leeren QueryDef-Objekts wird erst über die SQL-Eigenschaft eine Datenauswahl auf die Tabelle tblStammdaten bestimmt. Der SQL-Ausdruck enthält aber zusätzlich zwei Kriterien auf die Felder Vorname und Nachname, die über die eckigen Klammern als Parameter ausgewiesen sind. Würden Sie die Abfrage physisch erzeugen und dann aufrufen, so würden Sie mit zwei Eingabe-Dialogen konfrontiert, die die Werte für die Parameter P1 und P2 abfragten. Das gäbe sicher Rätsel auf und als Parametername müsste dafür eher so etwas wie [Welcher Nachname] eingesetzt werden. in VBA aber ist es ganz egal, wie die Parameter benannt sind, da Sie sie nie zu Gesicht bekommen.

Sub QdfParameters()

     Dim dbs As Database

     Dim qdf As QueryDef

     Dim rs As DAO.Recordset

         

     Set dbs = CurrentDb

     Set qdf = dbs.CreateQueryDef("")

     qdf.SQL = "SELECT * FROM tblStammdaten " & _

         "WHERE Vorname=[P1] AND Nachname LIKE '*' & [P2] & '*'"

     qdf.Parameters("P1") = InputBox( _

         "Vorname als Kriterium für die Abfrage angeben:", _

         "Parameterabfrage", "Gerhard")

     qdf.Parameters("P2") = "mann"

     Debug.Print qdf.ReturnsRecords

     Set rs = qdf.OpenRecordset(dbOpenDynaset)

     Do While Not rs.EOF

         Debug.Print rs!Nachname, rs!Vorname, rs!Geburtsdatum

         rs.MoveNext

     Loop

Sie haben das Ende des frei verfügbaren Teil dieses Artikels erreicht!

Wenn Sie mehr lesen und auf viele weitere Artikel zugreifen möchten, melden Sie sich als Abonnent unter Login an. Falls nicht, bestellen Sie doch einfach ein Jahresabonnement!