Home > Artikel > Ausgabe 4/2017 > Tabellen erstellen mit SQL und DDL

Tabellen erstellen mit SQL und DDL

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

Tabellen erstellen Sie normalerweise über die Entwurfsansicht für Tabellen. Dort fügen Sie Felder hinzu, legen Datentypen fest und wählen das Primärschlüsselfeld aus. Was aber, wenn Sie einmal eine Änderung an einer Tabelle vornehmen müssen, ohne dass Sie Zugriff auf die Datenbank haben – beispielsweise, weil die Datenbank schon beim Kunden in Betrieb ist? Oder Sie sogar komplette neue Tabellen anlegen wollen? Dann können Sie dies auch per Code erledigen. Dieser Artikel zeigt, wie das per Data Definition Language (DDL) gelingt – dem Teil von SQL, der sich um das Definieren der Objekte der Datenbank kümmert.

Beispieldatenbank

Die Beispiele dieses Artikels finden Sie in der Datenbank 1704_TabellenErstellenSQL.accdb.

Was ist DDL?

Access-SQL besteht aus zwei Teilen: DML und DDL. DML ist die Data Manipulation Language, mit der Sie Daten auswählen und ändern können. Die Auswahl erfolgt dabei in der Regel durch die SELECT-Anweisung, das Ändern durch die INSERT INTO-Anweisung zum Anlegen von Datensätzen, die UPDATE-Anweisung zum Ändern vorhandener Daten und die DELETE-Anweisung zum Löschen von Daten.

Die DDL, also die Data Definition Language, dient dazu, die Tabellen, Felder, Indizes, Fremdschlüssel und so weiter anzulegen, zu ändern oder zu löschen. In diesem Artikel schauen wir uns den DDL-Teil von SQL an und werden beispielsweise neue Tabellen anlegen, Tabellen löschen, Felder hinzufügen, ändern und löschen oder die Indizes und die Beziehungen zwischen den Tabellen bearbeiten.

Parallel schauen wir uns an, wie sich die jeweiligen Anweisungen auf den Entwurf der entsprechenden Tabelle auswirken.

Anzulegende Tabellen

Um in der Entwurfsansicht eine neue Tabelle anzulegen, betätigen Sie den Ribbon-Eintrag Erstellen|Tabellen|Tabellenentwurf. Es erscheint dann der Entwurf der neuen, noch leeren Tabelle. Hier fügen Sie nun die gewünschten Felder hinzu und speichern die Tabelle unter dem gewünschten Namen, beispielsweise tblKunden. Das Ergebnis sieht dann etwa wie in Bild 1 aus. Diese Tabelle wollen wir nun komplett per VBA mit den entsprechenden SQL-Befehlen erstellen. Außerdem wollen wir eine weitere Tabelle namens tblAnreden hinzufügen, welche die beiden Felder AnredeID (als Primärschlüsselfeld) sowie Anrede enthält. Die Tabelle tblKunden soll dann über das Fremdschlüsselfeld AnredeID mit dem Primärschlüsselfeld der Tabelle tblAnreden verknüpft werden. Die Beziehung soll mit referenzieller Integrität definiert werden, damit in das Fremdschlüsselfeld AnredeID der Tabelle tblKunden nur Werte eingegeben werden können, die bereits im gleichnamigen Primärschlüsselfeld der Tabelle tblAnreden vorhanden sind.

Diese Tabelle wollen wir mit VBA und SQL nachbauen.

Bild 1: Diese Tabelle wollen wir mit VBA und SQL nachbauen.

Die Beziehung soll anschließend die Eigenschaften wie in Bild 2 aufweisen.

Beziehung zwischen den Tabellen tblKunden und tblAnreden

Bild 2: Beziehung zwischen den Tabellen tblKunden und tblAnreden

Die Primärschlüsselfelder der beiden Tabellen tblKunden und tblAnreden sollen außerdem als Autowert-Felder definiert werden.

Ausführen von DDL-Befehlen

Um einen DDL-Befehl auszuführen, benötigen Sie wenige Zeilen VBA-Code. Die einzige Methode, die wir anwenden, heißt Execute und gehört zum Database-Objekt der DAO-Bibliothek (Microsoft DAO 3.6 Object Library, in neueren Access-Versionen ACE, Microsoft Office x.0 Access database engine Object Library). Für dieses deklarieren wir zuvor eine entsprechende Objektvariable:

Dim db as DAO.Database

Mit der folgenden Anweisung füllen wir die Objektvariable, wobei die Funktion CurrentDb einen Verweis auf das aktuelle Database-Objekt liefert:

Set db = CurrentDb

Schließlich rufen wir die Methode Execute dieses Objekts auf:

db.Execute "CREATE TABLE ...", dbFailOnError

Wichtig ist hier, dass Sie neben der auszuführenden SQL-Anweisung als ersten Parameter noch einen zweiten Parameter übergeben, welcher die Konstante dbFailOnError enthält. Nur so löst eine fehlerhafte SQL-Anweisung auch einen Laufzeitfehler aus.

Diese Anweisung definieren Sie beispielsweise in einem Standardmodul, das Sie im VBA-Editor (zu öffnen mit Alt + F11) mit dem Menübefehl Einfügen|Modul hinzufügen. Hier können Sie die nachfolgend vorgestellten Anweisungen in jeweils eigenen Prozeduren hinzufügen. Zum testweisen Ausführen positionieren Sie die Einfügemarke irgendwo innerhalb der auszuführenden Prozedur und betätigen die Taste F5. Die Inhalte der Prozeduren können Sie natürlich auch etwa in die Prozeduren einfügen, die durch die Schaltflächen eines Formulars ausgelöst werden. Im Rahmen dieses Artikels wollen wir uns jedoch auf die Tests vom VBA-Editor aus beschränken.

Anlegen der Tabelle tblAnreden

Als Erstes legen wir die Tabelle tblAnreden an. Diese Tabelle hat nur zwei Felder, nämlich AnredeID und Anrede. Schon der erste Versuch, die Tabelle einfach mit der Anweisung CREATE TABLE tblAnreden anzulegen, führt zu einem Fehler. Aber nicht etwa, weil wir keine Felder angegeben haben, sondern weil die Tabelle schlicht und einfach bereits vorhanden ist – wir haben sie ja zum Veranschaulichen der üblicherweise verwendeten Vorgehensweise bereits über die Benutzeroberfläche angelegt (siehe Bild 3).

Fehler beim Versuch, eine bereits vorhandene Tabelle anzulegen

Bild 3: Fehler beim Versuch, eine bereits vorhandene Tabelle anzulegen

Tabelle löschen

Wir kümmern uns also bereits vor dem ersten Anlegen einer Tabelle per DDL um das Löschen einer bereits vorhandenen Tabelle gleichen Namens. Das ist zugegebenermaßen auch viel leichter, als die Tabelle zu erstellen und sieht wie folgt aus:

db.Execute "DROP TABLE tblAnreden", dbFailOnError

Dummerweise führt dies gleich zum nächsten Fehler (siehe Bild 4). Wir müssen uns nun allerdings nicht um das Löschen der Beziehung kümmern, sondern wir entfernen gleich die komplette Tabelle tblKunden, bevor wir tblAnreden löschen:

Die Beziehung verhindert das Löschen der Tabelle tblAnreden.

Bild 4: Die Beziehung verhindert das Löschen der Tabelle tblAnreden.

db.Execute "DROP TABLE tblKunden", dbFailOnError

db.Execute "DROP TABLE tblAnreden", dbFailOnError

Danach können wir dann die Tabelle tblAnreden anlegen. Das klappt sogar ohne Angabe eines einzigen Feldes:

db.Execute "CREATE TABLE tblAnreden", dbFailOnError

Die Abfrage erscheint dann auch im Navigationsbereich, allerdings führt der Versuch, die Tabelle zu öffnen, zu dem Fehler aus Bild 5. Wir behalten für die folgenden Beispiele, die wir in der Prozedur tblAnredenAnlegen entwickeln, die beiden Anweisungen zum Löschen der Tabellen tblKunden und tblAnreden bei – so löschen wir gleich immer die zuvor angelegten Exemplare der beiden Tabellen.

Tabellen ohne Felder können nicht geöffnet werden.

Bild 5: Tabellen ohne Felder können nicht geöffnet werden.

Der nächste Versuch soll die beiden Felder AnredeID und Anrede mit den jeweiligen Datentypen zur Tabelle tblAnreden hinzufügen. Dabei haben wir vor den beiden Anweisungen zum Löschen der Tabellen tblKunden und tblAnreden die Fehlerbehandlung deaktiviert, da diese beim Versuch, eine nicht vorhandene Tabelle zu löschen, einen Fehler auslösen würden:

Public Sub tblAnredenAnlegen()

Dim db As DAO.Database

Set db = CurrentDb

On Error Resume Next

db.Execute "DROP TABLE tblKunden", dbFailOnError

db.Execute "DROP TABLE tblAnreden", dbFailOnError

On Error GoTo 0

db.Execute "CREATE TABLE tblAnreden( AnredeID INTEGER, Anrede TEXT(50))", dbFailOnError

End Sub

Das Ergebnis sehen Sie in Bild 6. Die beiden Felder werden mit den gewünschten Datentypen angelegt, allerdings natürlich noch ohne Primärschlüsselfeld und Autowert-Eigenschaft. Dies können wir nun nachträglich erledigen oder aber durch erneutes Löschen und Anlegen der Tabelle. Zunächst werfen wir jedoch noch einen Blick auf die CREATE TABLE-Anweisung:

Die Tabelle tblAnreden mit den beiden Feldern AnredeID und Anrede – allerdings noch ohne Primärschlüssel- und Autowert.

Bild 6: Die Tabelle tblAnreden mit den beiden Feldern AnredeID und Anrede – allerdings noch ohne Primärschlüssel- und Autowert.

CREATE TABLE tblAnreden(

AnredeID INTEGER,

Anrede TEXT(50)

)

Hier wird der grundsätzliche Aufbau schnell deutlich: Nach dem Tabellennamen folgt in Klammern eine komma-separierte Liste der anzulegenden Felder, wobei nach dem Feldnamen jeweils der Datentyp angegeben wird. Wir wollen die gängigen Datentypen abbilden, also diejenigen, die im Tabellenentwurf zur Auswahl stehen (siehe Bild 7).

Datentypen für die Felder einer Access-Tabelle

Bild 7: Datentypen für die Felder einer Access-Tabelle

Tabelle trotz Löschen noch vorhanden?

Es kann sein, dass trotz des Löschens einer Tabelle eine Fehlermeldung beim erneuten Erstellen einer Tabelle erscheint (siehe Bild 8). Dies ist dann der Fall, wenn die Tabelle beim Versuch, diese zu löschen, geöffnet ist – egal, ob in der Datenblatt- oder Entwurfsansicht. Sie müssen die Tabelle dann zunächst schließen. Auch dies könnten Sie noch in Form einer VBA-Anweisung zur Prozedur hinzufügen:

Fehler bei bereits vorhandener Tabelle

Bild 8: Fehler bei bereits vorhandener Tabelle

DoCmd.Close acTable, "tblAnreden"

Datentypen unter Access-SQL

Die SQL-Namen für die verschiedenen Datentypen des Tabellenentwurfs lauten wir folgt:

  • Kurzer Text: TEXT(n) mit n als Anzahl der Zeichen (bis zu 255)
  • Langer Text: LONGTEXT
  • Zahl: Feldgröße Single: SINGLE, Feldgröße Double: DOUBLE, Feldgröße Integer: SMALLINT, Feldgröße Long Integer: INTEGER
  • Große Ganzzahl: DECIMAL (nur per ADODB – siehe unten)
  • Datum/Uhrzeit: DATETIME
  • Währung: MONEY, CURRENCY
  • Autowert: COUNTER
  • Ja/Nein: BIT
  • OLE-Objekt: IMAGE

Anreden mit Primärschlüssel

Um der Tabelle tblAnreden gleich einen Primärschlüssel für das Feld AnredeID zuzuweisen, verwenden Sie die folgende Anweisung als Parameter der Execute-Methode:

CREATE TABLE tblAnreden(

AnredeID INTEGER CONSTRAINT PK PRIMARY KEY,

Anrede TEXT(50)

)

Das Ergebnis finden Sie in Bild 9. Für das Feld AnredeID wird nun das Primärschlüssel-Symbol angezeigt.

AnredeID mit Primärschlüssel

Bild 9: AnredeID mit Primärschlüssel

Anreden mit Autowert

Wenn Sie statt eines einfachen Primärschlüsselfeldes ein Autowert-Feld einrichten wollen, also eines, bei dem beim Anlegen eines neuen Datensatzes automatisch ein noch nicht vergebener Zahlenwert für das Feld festgelegt wird, brauchen Sie einfach nur den Datentyp von INTEGER auf COUNTER zu ändern (Ergebnis siehe Bild 10):

AnredeID mit Primärschlüssel und Autowert

Bild 10: AnredeID mit Primärschlüssel und Autowert

CREATE TABLE tblAnreden(

AnredeID COUNTER CONSTRAINT PK PRIMARY KEY,

Anrede TEXT(50)

)

Feld nachträglich hinzufügen

Wenn Sie sich nach dem Anlegen einer Tabelle entscheiden, ein neues Feld hinzuzufügen, haben Sie zwei Möglichkeiten: Entweder, Sie erstellen die Tabelle neu und berücksichtigen diesmal das fehlende Feld. Das ist jedoch in den meisten Fällen nicht möglich, weil die Tabelle bereits Datensätze enthält. Also gehen wir ein wenig feiner vor und fügen nur das fehlende Feld hinzu. In diesem Fall wollen wir die Tabelle tblAnreden mit einem weiteren Feld namens Briefanrede ausstatten. Das erledigen mit der folgenden SQL-Anweisung:

ALTER TABLE tblAnreden

ADD COLUMN Briefanrede TEXT(50)

Wir verwenden also die ALTER TABLE-Anweisung unter Angabe der Tabelle und fügen das neue Feld mit den Schlüsselwörtern ADD COLUMN hinzu. Der Rest besteht aus dem Feldnamen und dem Datentyp.

Feld wieder löschen

Wenn Sie später feststellen, dass Sie das Feld doch nicht benötigen sollten, können Sie es mit dem DROP COLUMN-Schlüsselwort wieder löschen. Dieses setzen Sie im Kontext der ALTER TABLE-Anweisung ab. Mit der folgenden Anweisung würden Sie beispielsweise das Feld Briefanrede wieder aus der Tabelle tblAnreden löschen:

ALTER TABLE tblAnreden DROP COLUMN Briefanrede

Feld ändern

Sie können auch die Eigenschaften eines Feldes ändern, ohne gleich die Tabelle oder auch nur das Feld neu anzulegen. Wenn Sie etwa die maximale Länge der Inhalte für das Feld Briefanrede von 50 auf 255 ändern wollen, verwenden Sie die folgende SQL-Anweisung:

ALTER TABLE tblAnreden

ALTER COLUMN Briefanrede TEXT(255)

Wie Sie sehen, nutzen wir hier das Schlüsselwort ALTER gleich zwei Mal – einmal für die Festlegung der Tabelle und für das zu ändernde Feld.

Feld löschen

Zum Löschen eines Feldes verwenden Sie die ALTER TABLE-Anweisung mit dem DROP COLUMN-Schlüsselwort, beispielsweise wie folgt:

ALTER TABLE tblAnreden DROP COLUMN Briefanrede

Feldname ändern

Um einen Feldnamen zu ändern, müssen Sie das Feld löschen und neu erstellen.

Etwas komplizierter wird es, wenn das Feld bereits Daten enthält. Dann erstellen Sie das Feld erst unter dem neuen Namen neu, kopieren die Inhalte vom alten in das neue Feld und löschen dann das alte Feld.

Kurzgefasst sieht das etwa so aus:

ALTER TABLE tblAnreden ADD Adressanrede Text(50)

UPDATE tblAnreden SET Adressanrede = Anrede

ALTER TABLE tblAnreden DROP COLUMN Anrede

Eingabe erforderlich/Feld soll nicht Null sein

Nun stellen wir fest, dass die Felder Anrede und Briefanrede nicht den Wert Null enthalten dürfen. Wenn Sie die Tabelle neu erstellen, benötigen Sie einfach nur zwei zusätzliche Schlüsselwörter, nämlich NOT NULL, die Sie hinter dem Datentyp des betroffenen Feldes hinzufügen:

CREATE TABLE tblAnreden(

AnredeID COUNTER CONSTRAINT PK PRIMARY KEY,

Anrede TEXT(50) NOT NULL,

Briefanrede TEXT(50)

)

Zur Kontrolle schauen wir uns den Entwurf der so erstellten Tabelle an, welche für die Eigenschaft Eingabe erforderlich des Feldes Anrede den Wert Ja enthält, für das Feld Briefanrede jedoch nicht (siehe Bild 11).

Eingabe erforderlich auf Ja einstellen

Bild 11: Eingabe erforderlich auf Ja einstellen

Feld mit eindeutigem Index definieren

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!