Home > Artikel > Ausgabe 9/2014 > Tabellen bearbeiten mit dem TableDef-Objekt

Tabellen bearbeiten mit dem TableDef-Objekt

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

Wenn Sie über VBA auf die Objekte Ihrer Datenbank zugreifen, dann hat sicher der Datenzugriff über das Recordset-Objekt die größte Bedeutung. Seltener werden Sie die Definitionen der Tabellen auslesen, bearbeiten oder gar anlegen müssen. Wenn doch, so ist das TableDef-Objekt von DAO die zentrale Anlaufstelle. Sehen Sie selbst, wie der Umgang damit aussieht und was möglich ist.

Beispieldatenbank

Die Beispiele dieses Artikels finden Sie in der Datenbank 1409_Tabledefs.mdb.

Die richtige Bibliothek

Die VBA-Zugriffsmethoden für die Datenbank sind nicht Teil von Access selbst, sondern in eine zusätzliche Daten-Engine ausgelagert. Das führt dazu, dass Sie in Ihrem VBA-Projekt einen zusätzlichen Verweis auf diese Engine benötigen. Wenn Sie eine neue Datenbank anlegen, so setzt Access diesen Verweis automatisch, doch leider war man sich bei Microsoft nicht einig, welche Bibliothek hierfür die geeignete ist.

So war man einige Zeit der Meinung, dass ADODB die bessere Zugriffsbibliothek darstellt, und folglich wurde beim Anlegen einer Datenbank darauf ein Verweis gesetzt. Erst ab Access 2007 schwenkte man wieder um und manifestierte DAO als Standard.

Sie können daher nicht sicher sein, dass in Ihrer oder einer fremden Datenbank bereits ein Verweis auf die Bibliothek DAO gesetzt ist, welche die Klasse TableDef enthält, von der hier die Rede sein soll.

Im Zweifel schauen Sie im VBA-Objektkatalog nach, ob dort der Eintrag DAO vorhanden ist, oder rufen alternativ den Verweise-Dialog über das Menü Extras|Verweise... auf. DAO hieß zwar früher schon DAO, der Beschreibungstext des Verweises änderte sich allerdings. So ist bis Access 2003 ein Verweis auf die Microsoft DAO 3.6 Objekt Library zu setzen und für die Folgeversionen einer auf die Microsoft Office 12.0 Access database engine Object Library.

Machen Sie für Access 2010 aus der 12 eine 14 und für Access 2013 eine 15. In allen Fällen aber finden Sie anschließend im Objektkatalog den Eintrag DAO in der linken oberen Combobox. Wird er ausgewählt, so erscheinen die Datenzugriffsklassen der Bibliothek auf der linken Seite, wie in Bild 1, wo auch schon die TableDef-Klasse markiert wurde und rechts ihre Methoden wiedergibt.

Die Klasse DAO.TableDef im VBA-Objektkatalog

Bild 1: Die Klasse DAO.TableDef im VBA-Objektkatalog

TableDefs-Auflistung

Alle Tabellen Ihrer Datenbank sind in einer Art Katalog über die TableDefs-Klasse zu erhalten. Wie in allen Auflistungsklassen können deren TableDef-Elemente in einer For..Each-Schleife durchlaufen werden, die Zahl der Elemente über die Eigenschaft Count ermittelt oder Elemente gezielt mit der (versteckten) Item-Eigenschaft abgefragt werden. Woher jedoch weiß DAO, wenn es gar nicht Teil des Access-Objektmodells ist, welche Tabellen ihn der Access-Datenbank stecken? Dazu muss über ein Database-Objekt, welches selbst die TableDefs-Methode ausweist, eine Verbindung zu ihr geschaffen werden.

Dieses Bindeglied gibt es in Access entweder über die Eigenschaft Application.CurrentDb oder über die Eigenschaft Application.DBEngine. Sobald eine Datenbank in Access geöffnet wird, tritt die Datenbank-Engine in Aktion und lädt in ihr Workspace ein neues Database-Objekt. Eine Kopie davon lässt sich dann über CurrentDb erhalten. Im Prinzip sollte man über beide Ansätze zum gleichen Ergebnis kommen:

1.

Dim dbs As DAO.Database

Set dbs = Application.CurrentDb

2.

Dim dbs As DAO.Database

Dim wrk As DAO.Workspace

Set wrk = Application. _

   DBEngine.Workspaces(0)

Set dbs = wrk.Databases(0)

Abgekürzt:

Set dbs = DBEngine(0)(0)

Dass es leider nicht egal ist, für welche der beiden Methoden man sich entscheidet, werden wir noch sehen.

Versuchen wir zunächst alle Tabellen der aktuellen Datenbank namentlich im VBA-Direktfenster auszugeben. Rufen Sie dazu die erste der drei Routinen in Listing 1 auf. ListTableDefs definiert erst eine TableDef-Variable tdf und füllt diese in der folgenden For..Each-Schleife jeweils mit dem nächsten Element der TableDefs-Auflistung von CurrentDb. Die Eigenschaft Name des TableDef-Objekt wird ins Direktfenster geschrieben.

Auf diese Weise entsteht eine Liste wirklich aller Tabellen der Datenbank, zu der auch die Systemtabellen gehören, welche allesamt das Präfix MSys tragen. Wollten Sie sie ausfiltern, so könnten Sie dies über eine Textfunktion tun, die die ersten vier Zeichen des Namen auswertet.

Da ein TableDef-Objekt aber eine Methode besitzt, welche die Systemeigenschaft direkt anzeigen kann, ist der Gebrauch von Attributes verlässlicher. Attributes ist ein Long-Wert, der verschiedene Eigenschaften der Tabelle wiedergibt und als Zahlen binär addiert. Sie finden diese in den Enumerationskonstanten der Klasse TableDefAttributeEnum von DAO im Objektkatalog. Dort gibt es etwa für Systemtabellen die Konstante dbSystemObject oder für verknüpfte Tabellen das Attribut dbAttachedTable.

Um zu ermitteln, ob dbSystemObjekt im Attributes-Wert der Tabelle steckt, muss diese Eigenschaftszahl per booleschem AND verglichen werden. Genau das geschieht auch in den beiden weiteren Routinen aus Listing 1. ListSysTableDefs gibt alle Systemtabellen aus und ListNonSysTableDefs die von Ihnen angelegten. Wobei hier zu erwähnen wäre, dass über die Attributes-Eigenschaft eine jede Tabelle auch auf den Status Systemobjekt eingestellt werden kann! Wollten Sie etwa der Tabelle tblStammdaten der Beispieldatenbank den Systemstatus verleihen, so genügt dafür eine Zeile:

Sub ListTableDefs()

     Dim tdf As TableDef

     For Each tdf In CurrentDb.TableDefs

         Debug.Print tdf.Name

     Next tdf

End Sub

Sub ListSysTableDefs()

     Dim tdf As TableDef

     For Each tdf In CurrentDb.TableDefs

         If (tdf.Attributes And dbSystemObject) <> 0 Then Debug.Print tdf.Name

     Next tdf

End Sub

Sub ListNonSysTableDefs()

     Dim tdf As TableDef

     For Each tdf In CurrentDb.TableDefs

         If (tdf.Attributes And dbSystemObject) = 0 Then Debug.Print tdf.Name

     Next tdf

End Sub

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

CurrentDB.TableDefs("tblStammdaten").Attributes = dbSystemObject

Ebenso könnten Sie die Tabelle verstecken, indem sie dbHiddenObject zuwiesen. In beiden Fällen würde die Tabelle im Datenbankfenster oder Navigationsbereich nicht mehr auftauchen, so Sie nicht in deren Optionen eingestellt hatten, dass auch versteckte und System-Objekte angezeigt werden sollen.

Tabelleneigenschaften auslesen

Attributes als Eigenschaftswert einer Tabelle wurde eben schon angeführt. Die weiteren Standardeigenschaften einer Tabelle entsprechend jenen, die rechts in Bild 1 zu sehen sind. Listing 2 zeigt beispielhaft, wie das geht.

Sub ReadTabledef(Optional sTable As String = "tblStammdaten")

     Dim dbs As DAO.Database

     Dim tdf As DAO.TableDef

     

' Set tdf = CurrentDb.TableDefs(sTable)

' Set dbs = CurrentDb

' Set tdf = dbs.TableDefs(sTable)

     

     With DBEngine(0)(0)

         .TableDefs.Refresh

         Set tdf = .TableDefs(sTable)

     End With

     

     With tdf

         Debug.Print "Name", tdf.Name

         Debug.Print "Connect", .Connect

         Debug.Print "DateCreated", .DateCreated

         Debug.Print "LastUpdated", .LastUpdated

         Debug.Print "SourceTableName", .SourceTableName

         Debug.Print "Updatable", .Updatable

         Debug.Print "ValidationRule", .ValidationRule

         Debug.Print "ValidationText", .ValidationText

         Debug.Print "Attribute Text:", StrTableAttrib(.Attributes)

         Debug.Print "ReplicaFilter", .ReplicaFilter

         Debug.Print "ConflictTable", .ConflictTable

         Debug.Print "RecordCount", .RecordCount

         Debug.Print "Fields.Count", .Fields.Count

         Debug.Print "Indexes.Count", .Indexes.Count

         Debug.Print "Properties.Count", .Properties.Count

     End With

End Sub

Listing 2: Eigenschaften eines TableDef-Objekts ausgeben

Der Prozedur ReadTableDef können Sie optional den Namen einer Tabelle als Parameter übergeben. Falls Sie dies unterlassen, wird einfach die Stammdatentabelle verwendet. Im With-Block der Routine werden alle verfügbaren Eigenschaften des TableDef-Objekts tdf ausgegeben.

Interessant sind nun die im oberen Bereich auskommentierten Zeilen. Die erste versucht, die TableDef-Variable auf

Currentdb.TableDefs(sTable)

zu setzen. Das wird anstandslos durchgeführt, doch bei ersten Zugriff auf eine Eigenschaft der TableDef-Variablen (tdf.Name) erfolgt eine Fehlermeldung, dass das Objekt nicht mehr gültig sei. Grund dafür ist, dass CurrentDb nur eine temporäre Instanz des Database-Objekts widerspiegelt, das keinen Bestand hat. Sobald Sie das temporäre Objekt aber einer Database-Variablen dbs zuweisen, wie in den nächsten auskommentierten Zeilen, funktioniert die Routine.

Warum trat dann der Fehler nicht bereits in den Prozeduren aus Listing 1 auf? Ganz einfach: Der Schleifen-Enumerator fragt das CurrentDb-Objekt bei jedem Durchlauf neu ab!

Auf die Bedeutung der einzelnen Eigenschaften des TableDef-Objekts soll hier nicht weiter eingegangen werden. Ziehen Sie dazu die Hilfe zu Rate, indem Sie im Objektkatalog eine Methode markieren und F1 drücken. Zum gleichen Ziel führt auch, wenn Sie einen Eigenschaftsnamen im Quellcode markieren und F1 drücken.

Falls Sie über den Ausdruck StrTableAttrib im Code stolpern: Das ist der Name einer Hilfsfunktion im Modul mdlTDFRead, die aus dem numerischen Attributes-Wert einen aussagekräftigeren Begriff macht. Denn was sollte Ihnen der Wert -2147483646 sagen? "SystemObjekt" hilft da schon eher weiter.

Die Methoden des TableDef-Objekts geben nur die Standardeigenschaften einer Tabelle zurück. Zwar ist nicht so recht nachzuvollziehen, warum dazu etwa auch RecordCount gehört, ein berechneter Wert, der die Zahl gespeicherter Datensätze der Tabelle enthält und damit nichts mit der Definition der Tabelle zu tun hat. (RecordCount funktioniert übrigens nur bei lokalen Tabellen, nicht bei verknüpften.) Oder ReplicaFilter und ConflictTable – beides Eigenschaften, die nur im Zusammenhang mit Replikation einen Sinn ergeben, die aber in den neueren Access-Versionen ohnehin nicht mehr unterstützt wird.

Wie dem auch sei, über die Properties-Auflistung des TableDef-Objekts können noch weitere Eigenschaften in Erfahrung gebracht werden.

In Listing 3 ist eine dafür geeignete Routine zu sehen. Auch hier wird eine Schleife verwendet, um der Property-Variablen prp die Elemente der Properties-Auflistung zuzuweisen.

Sub ReadTableDefProperties(Optional sTable As String = "tblStammdaten")

     Dim tdf As DAO.TableDef

     Dim prp As DAO.Property

     

     With DBEngine(0)(0)

         .TableDefs.Refresh

         Set tdf = .TableDefs(sTable)

     End With

     For Each prp In tdf.Properties

         Debug.Print prp.Name, TypeName(prp.Value), prp.Value

     Next prp

End Sub

Listing 3: Weitere Eigenschaften einer Tabelle ausgeben

Im VBA-Direktfenster wird für jedes Property dann der Eigenschaftsname (prp.Name), der Wert (prp.Value) und der Datentyp (Typename) ausgegeben. Der Clou an der Sache ist, dass dabei auch die Standardeigenschaften enthalten sind, die in Listing 2 ermittelt wurden. Da diese Routine hier offensichtlich kürzer ist, liegt es nahe, sie grundsätzlich zum Auslesen von Tabelleneigenschaften zu verwenden.

Wollen Sie nur eine bestimmte Eigenschaft ermitteln, so reicht eine einzige Code-Zeile aus:

CurrentDb.Tabledefs("tblStammdaten").Properties("DateCreated")

Diese Anweisung gibt direkt das Erstelldatum der Tabelle aus.

Zu den über die Properties-Auflistung erhältlichen Eigenschaften der Tabelle gehören zahlreiche, die eigentlich gar keine originären Datenbankeigenschaften sind, sondern die Ansicht des Datenblatts steuern, wenn Sie die Tabelle in dieser Ansicht öffnen. So etwa Schriftart, Hintergrundfarben, oder Unterdatenblattname, falls hier Veränderungen zu den Default-Werten von Access vorgenommen wurden.

Noch ein Wort zu der Methode Refresh der TableDefs-Auflistung. Sie aktualisiert den Katalog der Tabellen der aktuellen Datenbank. Haben Sie etwa gerade eine neue Tabelle angelegt, so ist nicht sichergestellt, dass diese in der TableDefs-Auflistung sofort erscheint. Deshalb ist es ratsam, diese Methode vor allen weiteren TableDef-Operationen einmal auszuführen.

Felder einer Tabelle ermitteln

Zu den Eigenschaften eines TableDef-Objekts gehört auch die Auflistung Fields, die deren Felder beherbergt. Also ist abermals eine Schleife gefragt und in Listing 4 realisiert. Ein DAO.Field-Objekt hat selbst wieder Standardeigenschaften, die wie in der Prozedur ReadTableDefFields der Beispieldatenbank (hier nicht abgebildet) länglich ausgegeben werden können. Einfacher ist da abermals die Properties-Auflistung des Field-Objekts, welche im Listing in einer inneren Schleife durchlaufen wird.

Sub ReadTableDefFields2(Optional sTable As String = "tblStammdaten")

     Dim tdf As DAO.TableDef

     Dim fld As DAO.Field

     Dim prp As DAO.Property

     

     With DBEngine(0)(0)

         .TableDefs.Refresh

         Set tdf = .TableDefs(sTable)

     End With

     On Error Resume Next

     For Each fld In tdf.Fields

         Debug.Print fld.Name

         For Each prp In fld.Properties

             Debug.Print , prp.Name, TypeName(prp.Value), prp.Value

         Next prp

     Next fld

End Sub

Listing 4: Felder einer Tabelle über des TableDef-Objekt auslesen

Analog der Code zum Ermitteln der Indizes einer Tabelle über die Indexes-Auflistung des TableDef-Objekts (siehe Listing 5).

Sub ReadTableDefIndexes2(Optional sTable As String = "tblStammdaten")

     Dim tdf As DAO.TableDef

     Dim idx As DAO.Index

     Dim prp As DAO.Property

     

     Set tdf = DBEngine(0)(0).TableDefs(sTable)

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!