Unter dem SQL-Datenbankschema verstehen wir eine Reihe von SQL-Anweisungen, mit denen Sie Tabellen oder auch eine komplette Datenbank erstellen können. Dazu gehören beispielsweise die CREATE TABLE- oder CREATE INDEX-Anweisungen. Da man diese Befehle eher selten einsetzt, kann eine Lösung zum automatischen Erstellen des Schemas einer oder mehrerer Tabellen nicht schaden. Der vorliegende Beitrag zeigt, wie es funktioniert.
Access macht es dem Entwickler leicht: Mit der Tabellenentwurfs-Ansicht, dem Indizes-Fenster und dem Beziehungen-Fenster bietet es uns drei Möglichkeiten, Tabellen und deren Beziehungen ganz einfach anzulegen. Im Tabellenentwurfsfenster erfassen wir die grundlegenden Daten wie die Felder mit ihren Datentypen und weitere Eigenschaften, im Indizes-Fenster verwalten wir einfache und zusammengesetzte Indizes und das Beziehungen-Fenster dient schließlich dazu, die Beziehungen zwischen den Tabellen anzulegen.
Im Hintergrund rödelt Access ganz ordentlich und stellt die SQL-Anweisungen zusammen, welche die von uns angelegten Tabellen und Felder und auch änderungen an bestehenden Elementen zur Ausführung an die JET-Engine weitergeben.
Solche Ausdrücke können Sie mit der Lösung des vorliegenden Beitrags selbst erstellen: Sie brauchen einfach nur das Formular aus Bild 1 zu öffnen und die gewünschte Tabelle auszuwählen, schon erscheinen die SQL-Ausdrücke zum Erstellen dieser Tabellen mit den gebräuchlichsten Eigenschaften im Formular.
Bild 1: Durch Auswählen einer Tabelle erhalten Sie den SQL-Ausdruck, um diese zu erstellen.
Grundlagen
SQL verwenden Sie ja normalerweise, um Abfragen für die Auswahl von Daten zu definieren oder auch zum Ausführen von Aktionsabfragen. Dieser Teil von SQL nennt sich Data Manipulation Language (DML). Für den vorliegenden Beitrag brauchen wir allerdings eine andere SQL-Teilmenge, nämlich die Data Definition Language (DDL). Sie enthält Befehle wie die oben bereits erwähnten CREATE TABLE…– oder CREATE INDEX…-Anweisungen. Mit ein wenig Einarbeitung können Sie mit diesen Befehlen Tabellen und Beziehungen sicher genauso schnell, wenn nicht noch schneller als mit den Elementen der Benutzeroberfläche erstellen.
Da die meisten von uns bequem sind, wird das aber kaum jemand tun. Dennoch kommt es vor, dass man eine oder mehrere SQL-Anweisungen zum Anlegen von Tabellen benötigt – beispielsweise, wenn Sie eine Tabelle per VBA-Code erstellen möchten. Dabei kann es sich um eine temporäre Tabelle handeln, die nur kurzzeitig benötigt und dann wieder gelöscht wird, zum Beispiel um zu importierende Daten vor dem Weiterverarbeiten zwischenzulagern. Oder Sie bauen einen Assistenten oder ein Tool, das per Mausklick Tabellen in Ihren Datenbanken anlegen soll. Dies ist zum Beispiel äußerst praktisch, wenn Ihre Datenbankanwendung immer wiederkehrerende Tabellen wie etwa eine Anreden-Tabelle enthalten soll.
Ziel der nachfolgend vorgestellten Prozeduren ist es, auf Basis bestehender Tabellen SQL-Abfragen zu erzeugen, mit denen Sie die Tabellen nachbauen können. Für eine einfache Tabelle mit einem Primärschlüsselfeld namens AnredeID und einem weiteren Textfeld namens Anrede mit einer Feldgröße von 255 Zeichen benötigen Sie beispielsweise die folgenden beiden Zeilen:
CREATE TABLE tblAnreden([AnredeID] COUNTER, [Anrede] CHAR(255)); CREATE UNIQUE INDEX [PrimaryKey] ON [tblAnreden]([AnredeID]) WITH PRIMARY DISALLOW NULL;
Das Ganze ließe sich auch mit einer einzigen Zeile erledigen, und zwar so:
CREATE TABLE tblAnreden([AnredeID] COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, [Anrede] CHAR(255));
Warum wir in der nachfolgend beschriebenen Lösung die erste Variante verwenden, erfahren Sie weiter unten. Eine ganze Menge Informationen zu den Befehlen der Data Definition Language erhalten Sie im Beitrag Datenbanken und Tabellen per SQL anpassen (Shortlink 609).
DAO als Werkzeug zum Auslesen von Tabellen-, Feld- und Beziehungsinformationen
Bevor wir uns um das Erstellen der SQL-Anweisungen kümmern können, brauchen wir Möglichkeiten, die Struktur der Tabellen, Felder und Beziehungen auszulesen.
Wie aber soll dies gelingen – welche Bibliothek bietet Zugriff auf den Entwurf einer Datenbank Es handelt sich um die DAO-Bibliothek, die Sie für den Zugriff auf die Daten einer Datenbank sicher schon Hunderte Male genutzt haben. Die für uns interessante Struktur basiert auf dem Database-Objekt, das Sie normalerweise als Ausgangspunkt für die OpenRecordset– oder die Execute-Methode verwenden. In diesem Fall greifen wir auf dessen TableDefs-Auflistung zu, die den Zugriff auf alle Tabellen der Datenbank erlaubt. Ein TableDef-Objekt wiederum enthält unter anderem die Fields-Auflistung, die wiederum die Field-Objekte und somit die Informationen über die Felder der entsprechenden Tabelle liefert. Außerdem stellt das TableDef-Objekt die Indexes-Auflistung bereit, mit der wir die Indizes der angegebenen Tabelle auslesen können.
Etwas weiter oben stellt das Database-Objekt neben der TableDefs-Auflistung auch noch die Relations-Auflistung bereit. Diese wiederum enthält die Relation-Objekte mit allen Informationen zu den im Beziehungen-Fenster angelegten Beziehungen.
Tabellendefinition auslesen
Den Start macht die Funktion GetCreateTableString (s. Listing 1). Sie erwartet ein TableDef-Objekt mit einem Verweis auf die auszuwertende Tabelle und liefert eine Zeichenkette mit dem SQL-Ausdruck zum Erstellen der entsprechenden Tabelle zurück. Diese Zeichenkette enthält die CREATE TABLE-Anweisung mit der Angabe des Namens der zu erstellenden Tabelle, die Feldliste sowie eventuell nötige CONSTRAINT-Elemente zur Definition von Fremdschlüsselfeldern.
Listing 1: Zusammenstellen der SQL-Anweisung zum Erstellen einer Tabelle
Public Function GetCreateTableString(tdf As DAO.TableDef) As String
Dim db As DAO.Database
Dim fld As DAO.Field
Dim idx As DAO.Index
Dim rel As DAO.Relation
Dim strTable As String
Dim strSQL As String
Dim strSQLField As String
Dim strFieldType As String
Dim strConstraints As String
Set db = CurrentDb
strTable = tdf.Name
strSQL = "CREATE TABLE " & strTable & "("
For Each fld In tdf.Fields
strSQLField = strSQLField & "[" & fld.Name & "]"
strFieldType = GetFieldType(fld)
strSQLField = strSQLField & " " & strFieldType & ", "
Next fld
strSQL = strSQL & strSQLField
For Each idx In tdf.Indexes
If idx.Foreign Then
For Each rel In db.Relations
If rel.ForeignTable = tdf.Name And idx.Fields(0).Name = rel.Fields(0).Name Then
strConstraints = strConstraints & "CONSTRAINT "
strConstraints = strConstraints & "[" & idx.Name & "] FOREIGN KEY ("
strConstraints = strConstraints & "[" & rel.Fields(0).Name & "]"
strConstraints = strConstraints & ") REFERENCES ["
strConstraints = strConstraints & rel.Table & "]"
If (rel.Attributes And dbRelationUpdateCascade) = dbRelationUpdateCascade Then
strConstraints = strConstraints & " ON UPDATE CASCADE"
End If
If (rel.Attributes And dbRelationDeleteCascade) = dbRelationDeleteCascade Then
strConstraints = strConstraints & " ON DELETE CASCADE"
End If
strConstraints = strConstraints & ", "
End If
Next rel
End If
Next idx
strSQL = strSQL & strConstraints
If Right(Trim(strSQL), 1) = "," Then
strSQL = Left(strSQL, Len(Trim(strSQL)) - 1)
End If
strSQL = strSQL & ");"
GetCreateTableString = strSQL
End Function
Um einen solchen Ausdruck zu erstellen, verwendet die Prozedur eine String-Variable namens strSQL, die nach und nach erweitert wird. Gleich zu Beginn schreibt die Prozedur den Ausdruck CREATE TABLE gefolgt von dem im Parameter angegebenen Tabellennamen und einer öffnenden Klammer in diese Variable. Die öffnende Klammer gibt den Startschuss für die folgende Feldliste ab. Diese stellt die Prozedur innerhalb einer For Each-Schleife zusammen, die alle Field-Elemente des TableDef-Objekts auf Basis der gewünschten Tabelle durchläuft. Darin bedienen wir uns einer Hilfsvariablen namens strSQLField, welche die Feldliste aufnimmt. Zu jedem Element der Feldliste gehört der Name der Tabelle, der vorsichtshalber in eckigen Klammern eingefasst wird – auf diese Weise verhindern wir, dass Feldnamen mit Sonderzeichen zu Fehlern beim Ausführen der SQL-Anweisungen führen. Hinter dem Feldnamen folgt der Datentyp. Diesen ermittelt eine separate Funktion namens GetFieldType (s. Listing 2) – mehr dazu im Anschluss an die Beschreibung der aktuellen Funktion. Vorerst reicht uns die Information, dass GetFieldType beispielsweise für ein Währungsfeld den Ausdruck MONEY zurückliefert.
Listing 2: Diese Funktion liefert eine SQL-kompatible Zeichenkette für den Datentyp eines Felds zurück.
Public Function GetFieldType(fld As DAO.Field) As String
Dim strFieldType As String
Select Case fld.Type
Case dbLong
If (fld.Attributes And dbAutoIncrField) = 0 Then
strFieldType = "INTEGER"
Else
strFieldType = "COUNTER"
End If
Case dbText
strFieldType = "TEXT"
strFieldType = strFieldType & "(" & fld.Size & ")"
Case dbCurrency
strFieldType = "MONEY"
Case dbInteger
strFieldType = "SMALLINT"
Case dbBoolean
strFieldType = "BIT"
Case dbSingle
strFieldType = "REAL"
Case dbDate
strFieldType = "DATETIME"
Case dbLongBinary
strFieldType = "IMAGE"
Case dbMemo
strFieldType = "LONGTEXT"
Case dbByte
strFieldType = "BYTE"
Case dbSingle
strFieldType = "SINGLE"
Case dbDouble
strFieldType = "DOUBLE"
Case dbBinary
strFieldType = "BINARY"
Case dbLongBinary
strFieldType = "OLE OBJECT"
Case dbGUID
strFieldType = "GUID"
Case Else
strFieldType = fld.Type
Stop
Debug.Print fld.Type
End Select
GetFieldType = strFieldType
End Function
Mit dem Feldnamen und dem Datentyp stellt unsere For Each-Schleife also Ausdrücke der Form
[AnredeID] COUNTER, [Anrede] CHAR(255),
zusammen. Um das angehängte Komma kümmert sich die Prozedur später.
Der folgende Block enthält eine weitere For Each-Schleife, welche diesmal alle Index-Elemente der aktuellen Tabelle durchläuft. Das Index-Element enthält eine Reihe verschiedener Eigenschaften, welche die Art des Indexes festlegen: Foreign, IgnoreNulls, Primary, Required und Unique. Die Funktion GetCreateTableString kümmert sich nur um die Foreign-Indizes – alle übrigen werden in einer separaten Funktion behandelt.
Hat die Foreign-Eigenschaft eines Index-Elements den Wert True, handelt es sich um einen Fremdschlüsselindex, für den es eine Beziehung zu einer anderen Tabelle gibt. Die Beziehungen einer Datenbank werden über die Relations-Auflistung des Database-Objekts verfügbar gemacht. Wenn die Funktion GetCreateTableString eine Tabelle mit einem Foreign-Index entdeckt, durchsucht sie die Relations-Auflistung nach einem Relation-Element, dessen Eigenschaft ForeignTable den Namen der aktuellen Tabelle enthält und deren Feld mit dem Feld des aktuellen Indexes übereinstimmt. Hat sie ein passendes Relation-Element gefunden, stellen die folgenden Zeilen zum Beispiel diesen Ausdruck zusammen:
CONSTRAINT [tblKategorientblArtikel] FOREIGN KEY ([KategorieID]) REFERENCES [tblKategorien] ON UPDATE CASCADE ON DELETE CASCADE,
Ob der Ausdruck ON UPDATE CASCADE (Aktualisierungsweitergabe) oder ON DELETE CASCADE (Löschweitergabe) eingefügt wird, hängt davon ab, ob der Benutzer die entsprechenden Eigenschaften im Dialog Beziehungen bearbeiten eingestellt hat. Dies ist zum Beispiel in Bild 2 der Fall.
Bild 2: Beziehungseigenschaften mit Lösch- und Aktualisierungsweitergabe
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