Beim Debuggen von VBA-Code, der Anweisungen für den Zugriff auf die Daten der Anwendung per DAO enthält, benötigt man immer wieder mal Detailinformationen. Welche Felder enthält die Tabelle noch Welchen Wert enthalten die Felder des aktuellen Datensatzes Wie viele Datensätze liefert das aktuelle Recordset Die notwendigen Informationen kann man sich mithilfe entsprechender Ausdrücke über das Direktfenster holen, aber meist kostet dies unnötig Zeit. Warum also nicht einfach eine kleine Hilfsklasse programmieren, die solche Fragen sehr einfach beantwortet Dieser Beitrag zeigt, wie es gelingt.
Die Idee für diesen Betrag stammt aus meinen alltäglichen Bedürfnissen. Erstens habe ich wirklich oft den Bedarf, mal eben die Werte der Felder eines gerade in Bearbeitung befindlichen Datensatzes auszugeben oder auch nur dessen Feldnamen, zweitens kamen mit der Zeit noch weitere Ideen hinzu – abgeleitet zum Beispiel von MySQL, wo es einen einfachen Befehl etwa zur Anzeige aller Tabellen (ShowTables) oder zur Ausgabe der Beschreibung einer Tabelle gibt (Describe
Also habe ich eine Klasse entwickelt, die solche Fragen beantwortet. In den folgenden Abschnitten schauen wir uns an, wie die Klasse aufgebaut ist und welche Fragen sie beantworten kann.
Alle Tabellen ausgeben
Den Befehl ShowTables unter MySQL fand ich schon immer praktisch. Gerade wenn man nicht so oft mit einer Datenbank arbeitet, merkt man sich nicht unbedingt alle Tabellennamen – und zum Nachschlagen eines Tabellennamens immer vom VBA-Editor zum Access-Fenster zu wechseln macht auch keinen Spaß, vor allem, wenn der Navigationsbereich einige hundert Elemente enthält, durch die man sich erst mal zu den Tabellen durchkämpfen muss.
Also entwickeln wir zunächst einmal die Methode ShowTables, die eine Liste aller Tabellen der Datenbank in alphabetischer Reihenfolge im Direktfenster ausgeben soll.
Die Ausgabe könnte etwa wie in Bild 1 aussehen, sobald man den Befehl ShowTables dort eingibt und die Eingabetaste betätigt.
Bild 1: Ausgabe der Tabellen einer Datenbank mit Detailinformationen
Dafür benötigen wir natürlich eine entsprechende Prozedur, die Sie in Listing 1 finden. Die Prozedur verwendet eine Enumeration, die im gleichen Modul wie folgt deklariert wird:
Public Sub ShowTables(Optional intType As TableType, _ Optional bolSystem As Boolean = True, Optional bolHidden As Boolean = True) Dim db As DAO.Database, rst As DAO.Recordset Dim strType As String, strTable As String Dim intLen As Integer Dim strSystem As String, strHidden As String Dim bolShow As Boolean If intType = 0 Then intType = dbLocal + dbODBC + dbLinked Set db = CurrentDb Set rst = db.OpenRecordset("SELECT Name, Type FROM MSysObjects " _ "WHERE Type IN (1,4,6) ORDER BY Name", dbOpenDynaset) intLen = DMax("Len(Name)", "MSysObjects") Debug.Print Debug.Print "Table" & Space(intLen - 4) & "Type" Debug.Print String(intLen + 25, "-") Do While Not rst.EOF strTable = rst!Name & Space(intLen - Len(rst!Name)) bolShow = True strSystem = "" If (db.TableDefs(rst!Name).Attributes And dbSystemObject) Then strSystem = "|System" If bolSystem = False Then bolShow = False End If strHidden = "" If (db.TableDefs(rst!Name).Attributes And dbHiddenObject) Then strHidden = "|Hidden" If bolHidden = False Then bolShow = False End If If bolShow = True Then Select Case rst!Type Case 1 If (intType And dbLocal) = dbLocal Then strType = "Local Table" Debug.Print strTable & " " & strType & strSystem & strHidden End If Case 4 If (intType And dbODBC) = dbODBC Then strType = "ODBC Table" Debug.Print strTable & " " & strType & strSystem & strHidden End If Case 6 If (intType And dbLinked) = dbLinked Then strType = "Linked Table" Debug.Print strTable & " " & strType & strSystem & strHidden End If End Select End If rst.MoveNext Loop End Sub
Listing 1: Ausgabe der Tabellen einer Datenbank im Direktfenster des VBA-Editors
Public Enum TableType
dbLocal = 1
dbODBC = 2
dbLinked = 4
End Enum
Die Enumeration stellt die Werte für den Parameter der Prozedur ShowTables bereit. Sie sollen mit der Prozedur nämlich nicht nur alle Tabellen ausgeben können, sondern auch nur alle lokalen Tabellen, alle ODBC-Tabellen oder auch nur die verknüpften Tabellen.
Um nur die lokalen Tabellen auszugeben, geben Sie etwa den folgenden Befehl im Direktfenster ein:
ShowTables dbLocal
Aber es gibt noch weitere Möglichkeiten, die auszugebenden Tabellen einzuschränken: Mit dem zweiten Parameter namens bolSystem legen Sie fest, ob Systemtabellen ausgegeben werden sollen. Der dritte Parameter bolHidden gibt schließlich an, ob auch versteckte Dateien in der Liste erscheinen sollen. Da beide Parameter standardmäßig auf den Wert True eingestellt sind, müssten Sie etwa den folgenden Aufruf verwenden, um nur die lokalen Tabellen ohne versteckte Tabellen und Systemtabellen auszugeben:
ShowTables dbLocal, False, False
Die Prozedur prüft zunächst, ob der Benutzer eines der Enumeration-Elemente zur Einschränkung des Tabellentyps angegeben hat. Falls nicht, ist intType = 0 und wir können intType per logischer Und-Verknüpfung auf alle Typen einstellen (dbLocal + dbODBC + dbLinked).
Danach öffnet die Prozedur ein Recordset mit allen Tabellen auf Basis der Systemtabelle MSysObjects. Man könnte zwar auch die TableDefs-Auflistung verwenden, um auf die Tabellen zuzugreifen, aber mit MSysObjects können wir die Tabellen direkt alphabetisch sortieren.
Die Abfrage enthält direkt einen Filter nach den entsprechenden Objekttypen – hier 1 für lokale Tabellen, 4 für ODBC-Tabellen und 6 für eingebundene Tabellen.
Damit wir eine saubere Darstellung in zwei Spalten erreichen, benötigen wir noch die maximale Länge der Tabellennamen. Diese holt die Prozedur mit einer DMax-Abfrage über den Ausdruck Len(Name) für die Tabelle MSysObjects.
Danach bildet die Prozedur die Spaltenüberschriften im Direktfenster ab, wobei nach einer Leerzeile die beiden überschriften Table und Type folgen. Den Abstand dazwischen liefert die Space-Funktion, die den Text Type so platziert, dass er knapp rechts neben dem längsten Tabellennamen landet. Auch die Anzahl der als Linie dienenden Minuszeichen wird anhand der Länge des längsten Tabellennamens ermittelt.
Danach steigt die Prozedur in eine Do While-Schleife über alle Datensätze des Recordsets rst ein. Darin ergänzt sie den Inhalt des Feldes Name des Recordsets um die entsprechende Menge Leerzeichen, damit alle Tabellennamen die Länge des längsten Namens haben, und speichert das Ergebnis in der Variablen strTable.
Dann stellt sie die Variable bolShow auf True ein. Diese legt fest, ob die Tabelle aus dem aktuellen Datensatz ausgegeben wird, und kann in den folgenden Prüfungen noch auf False eingestellt werden.
Zunächst jedoch kümmern wir uns darum, ob es sich um eine Systemtabelle handelt. Zunächst gehen wir nicht davon aus und setzen die Variable strSystem auf eine leere Zeichenkette. Sollte die Tabelle sich allerdings als Systemtabelle herausstellen, was die Prozedur durch den Vergleich der Eigenschaft Attributes des entsprechenden Eintrags der Auflistung TableDefs mit der Konstanten dbSystemObject herausfindet, erhält strSystem den Wert |System. Dieser wird später an einen Ausdruck angehängt, der den Typ der Tabelle charakterisiert.
Sollte der Benutzer für den Parameter bolSystem jedoch den Wert False übergeben haben, was bedeutet, dass er keine Systemtabellen in der Ausgabeliste wünscht, stellt die Prozedur nun die Variable bolShow ebenfalls auf False ein.
Danach folgt das gleiche Spiel für versteckte Tabellen. Diesmal wird die Variable strHidden gegebenenfalls mit dem Wert |Hidden gefüllt, falls es sich bei der Tabelle um eine versteckte Tabelle handelt, und die Variable bolShow erhält den Wert False, wenn der Parameter bolHidden auch diesen Wert enthält.
Schließlich entscheidet der Wert von bolShow darüber, ob die Tabelle in der Ausgabe landet. Hat die Variable den Wert False, geschieht nichts weiter, anderenfalls werden die verschiedenen Tabellentypen anhand des Wertes des Feldes Type des Recordsets entweder ausgegeben oder nicht.
Schauen wir uns das für den ersten Case-Zweig der Select Case-Anweisung an: Hat rst!Type den Wert 1, was auf eine lokale Tabelle hindeutet, prüft die Prozedur durch eine logische Konjunktion, ob der mit intType übergebene Wert die Konstante dbLocal enthält (mehr über logische Konjunktionen erfahren Sie im Beitrag Rund um Binärzahlen, www.access-im-unternehmen.de/556).
In diesem Fall erhält die Variable strType den Wert Local Table. Außerdem gibt eine Debug.Print-Anweisung den Namen der Tabelle (zuzüglich der weiter oben hinzugefügten Leerzeichen) und die Inhalte der Variablen strType, strSystem und strHidden aus. Letztere könnten beispielsweise den Wert |Local Table|System|Hidden liefern, wenn es sich um eine lokale, versteckte Systemtabelle handelt.
Die beiden übrigen Case-Zweige erledigen dies ähnlich für ODBC-Tabellen und verknüpfte Tabellen.
Auf diese Weise gibt die Prozedur Informationen über alle Tabellen im Direktfenster aus.
Tabellenbeschreibungen
Die zweite Prozedur soll ähnliche Informationen liefern wie die Anweisung Describe
Bild 2: Beschreibung einer Tabelle unter MySQL
Das können wir allerdings auch: Die nachfolgend beschriebene Prozedur namens Describe liefert etwa die Ausgabe aus Bild 3.
Bild 3: Baugleiche Beschreibung im Direktbereich des VBA-Editors
Den ersten Teil der Prozedur, die diese Ausgabe liefert, finden Sie in Listing 2. Die Prozedur nimmt mit dem Parameter strTable den Namen der zu untersuchenden Tabelle entgegen. Sie füllt die Variable db mit einem Verweis auf die aktuelle Datenbank. Die Auflistung TableDefs dieses Objekts liefert dann mit dem Namen der Tabelle als Parameter ein TableDef-Objekt, das die Beschreibung der Tabelle sowie Auflistungen etwa der Tabellenfelder liefert. Dieses referenzieren wir mit der Variablen tdf.
Public Sub Describe(strTable As String) Dim db As DAO.Database Dim tdf As DAO.TableDef Dim fld As DAO.Field Dim intLen As Integer, intLenDefault As Integer Dim strType As String, strNull As String Dim idx As DAO.Index Dim fldidx As DAO.Field Dim strIndex As String, strExtra As String Set db = Currentdb Set tdf = db.TableDefs(strTable) For Each fld In tdf.Fields If Len(fld.Name) > intLen Then intLen = Len(fld.Name) End If If Len(fld.DefaultValue) > intLenDefault Then intLenDefault = Len(fld.DefaultValue) End If Next fld intLen = intLen + 2 If intLen < 16 Then intLen = 16 End If intLenDefault = intLenDefault + 2 If intLenDefault < 7 Then intLenDefault = 7 End If Debug.Print "Table" & Space(intLen - 5) _ & "| Type | NULL | Key | DEFAULT" & Space(intLenDefault - 7) _ & " | EXTRA" Debug.Print "-----" & String(intLen - 5, "-") & "+---------------+------+-----+-" _ & String(intLenDefault - 7, "-") & "--------+------------------" For Each fld In tdf.Fields strType = "" Select Case fld.Type Case dbAttachment strType = "ATTACHMENT" Case dbBoolean strType = "BOOLEAN" Case dbCurrency strType = "CURRENCY" Case dbDate strType = "DATE" Case dbDouble strType = "DOUBLE" Case dbGUID strType = "GUID" Case dbInteger strType = "INTEGER"
Listing 2: Ausgabe der Felder einer Tabelle im Direktfenster des VBA-Editors (Teil I)
Zeichenlänge von Feldnamen und Standardwerten bestimmen
Im ersten Schritt durchlaufen wir alle Felder der Tabelle in einer For Each-Schleife über die Auflsitung Fields. Das jeweils aktuelle Element landet in der Variablen fld.
Hier nehmen wir erstmal grundlegende Informationen auf, damit wir später die Breite der Spalten optimal auslegen können.
Die erste Information ist die Anzahl der Zeichen des längsten Feldnamens der Tabelle. Innerhalb der For Each-Schleife prüft die Prozedur jeweils, ob die Länge des Feldnamens größer als die bereits in der Variablen intLen gespeicherte Länge ist.
Falls ja, landet die Länge des aktuellen Feldnamens in dieser Variablen. Nach dem Durchlaufen der Schleife enthält intLen somit die Anzahl der Zeichen des längsten Feldnamens.
Auf ähnliche Weise ermittelt die Prozedur anschließend die Länge des längsten Ausdrucks für die Eigenschaft DefaultValue (zu deutsch: Standardwert) und speichert diese in der Variablen intLenDefault.
Danach wird intLen um zwei erhöht, weil wir auf jeder Seite ein Zeichen Platz einräumen möchten.
Sollte das Feld mit dem längsten Feldnamen weniger als 16 Zeichen aufweisen, stellen wir intLen auf 16 ein.
Das Gleiche erledigt die Prozedur für die Variable intLenDefault, nur dass hier die Mindestzeichenlänge sieben beträgt.
Damit können wir nun bereits die Spaltenüberschriften im Direktfenster ausgeben. Die Ausgabe besteht zunächst aus der Spaltenüberschrift Table, gefolgt von einer Reihe von Leerzeichen. Die Anzahl richtet sich nach der Anzahl Zeichen des längsten Feldnamens, die Space-Funktion sorgt für die Ausgabe der entsprechenden Leerzeichen. Dann folgen die Spaltenüberschriften Type, NULL, KEY, DEFAULT und EXTRA, jeweils durch das Pipe-Zeichen (|) voneinander getrennt. Auch hinter der Spalte DEFAULT fügt die Anweisung einige Leerzeichen ein – hier in Abhängigkeit vom Wert der Variablen intLenDefault.
Danach folgt die Trennlinie zwischen den Spaltenüberschriften und den eigentlichen Werten. Dabei berücksichtigen wir wieder die variable Breite der Spalten für die Feldnamen und die Standardwerte, diesmal durch den Aufruf der String-Funktion, welche die benötigte Anzahl Minus-Zeichen liefert. Die übergänge von einem Spaltenkopf zum nächsten sind mit Plus-Zeichen versehen.
Felddatentypen bestimmen
Anschließend folgt eine weitere For Each-Schleife über alle Felder des TableDef-Objekts. Diesmal enthält diese einige Anweisungen zum Ermitteln der Feldeigenschaften sowie die Ausgabe der Informationen im Direktfenster.
Die erste Anweisung innerhalb der Schleife leert die Variable strType. Diese nimmt gleich den Felddatentyp des aktuellen Feldes auf. Diesen ermittelt die Prozedur in einer Select Case-Bedingung. Je nach dem Wert der Eigenschaft Type des Field-Objekts trägt die Prozedur Werte wie ATTACHMENT, BOOLEAN oder CURRENCY in strType ein. Dies setzt sich im zweiten Teil der Prozedur, den Sie in Listing 3 finden, fort.
Case dbLong strType = "LONG" Case dbLongBinary strType = "LONGBINARY" Case dbMemo strType = "MEMO" Case dbSingle strType = "SINGLE" Case dbText strType = "TEXT" Case Else Debug.Print fld.Type End Select strType = strType & Space(13 - Len(strType)) If fld.Required Then strNull = "NO " Else strNull = "YES " End If strIndex = " " For Each idx In tdf.Indexes For Each fldidx In idx.Fields If fldidx.Name = fld.Name Then If idx.Primary Then strIndex = "PRI" Else If idx.Unique Then strIndex = "UNI" End If End If End If Next fldidx Next idx strExtra = "" If (fld.Attributes And dbAutoIncrField) = dbAutoIncrField Then strExtra = strExtra & ", AUTOINCREMENT" End If If Len(strExtra) > 0 Then strExtra = Mid(strExtra, 3) End If Debug.Print fld.Name & Space(intLen - Len(fld.Name)) & "| " & strType _ & " | " & strNull & " | " & strIndex & " | " & fld.DefaultValue _ & Space(intLenDefault - Len(fld.DefaultValue)) & " | " & strExtra Next fld Debug.Print "-----" & String(intLen - 5, "-") & "+---------------+------+-----+-" _ & String(intLenDefault - 7, "-") & "--------+------------------" Debug.Print tdf.Fields.Count & " Fields" End Sub
Listing 3: Ausgabe der Felder einer Tabelle im Direktfenster des VBA-Editors (Teil II)
Anschließend füllt die Prozedur den Inhalt von strType so mit Leerzeichen auf, dass die Gesamtlänge der enthaltenen Zeichenkette 13 beträgt.
Nun folgt die Spalte, die angibt, ob das Feld Null-Werte enthalten darf oder nicht. Dies ermittelt die Prozedur mit der Eigenschaft Required. Hat diese den Wert True, erhält strNull den Wert NO, anderenfalls den Wert YES. NO enthält noch ein folgendes Leerzeichen, damit beide Werte drei Zeichen lang sind.
Danach folgen die Indizes, in diesem Fall kümmern wir uns allerdings lediglich um Primärschlüssel und eindeutige Indizes. Der entsprechende Ausdruck soll in der Variablen strIndex landen, die vorab mit drei Leerzeichen gefüllt wird. Warum nicht einfach mit einer leeren Zeichenkette Weil es auch sein kann, dass kein Index für dieses Feld festgelegt wurde – und dann benötigen wir eine entsprechend lange Zeichenkette, damit die Spalte die gewünschte Breite einhält.
Um die Indizes und deren Typ zu ermitteln, durchläuft die Prozedur zunächst die Indexes-Auflistung des jeweiligen TableDef-Objekts. Jedes Index-Objekt enthält wiederum eine Auflistung der Felder, die zum Index gehören. Diese durchlaufen wir ebenfalls. Innerhalb der inneren For Each-Schleife vergleicht die Prozedur dann den Namen des Feldes des Indexes mit dem Namen des aktuellen Tabellenfeldes. Stimmen beide überein, haben wir einen Index gefunden, der sich auf das aktuelle Tabellenfeld bezieht. Nun wollen wir noch herausfinden, um welche Art von Index es sich handelt. Hat die Eigenschaft Primary des Index-Objekts den Wert True, handelt es sich um einen Primärschlüssel.
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