Zur Hauptseite ... Zum Onlinearchiv ... Zum Abonnement ... Zum Newsletter ... Zu den Tools ... Zum Impressum ... Zum Login ...

Gedrucktes Heft

Diesen Beitrag finden Sie in Ausgabe 3/2007.

Unser Angebot für Sie!

Lesen Sie diesen Beitrag und 500 andere sofort im Onlinearchiv, und erhalten Sie alle zwei Monate brandheißes Access-Know-how auf 72 gedruckten Seiten! Plus attraktive Präsente, zum Beispiel das bald erscheinende Buch 'Access 2010 - Das Grundlagenbuch für Entwickler'!

Diesen Beitrag twittern

Zusammenfassung

Lernen Sie unbekannte, weil schlecht dokumentierte
Jet 4-SQL-Funktionen kennen.

Techniken

Datenmodellierung, VBA, SQL

Voraussetzungen

Access 2000 oder höher

Beispieldatei

-

Shortlink

460

Jet-SQL-Stiefkinder

Karl Donaubauer, Wien

Der SQL-Dialekt von Access bietet einige Möglichkeiten, die in der Entwicklergemeinde nur wenig beachtet werden. Der Hauptgrund dafür ist die schlechte Dokumentation dieser Features durch Microsoft. Einige dieser »geheimen« Funktionen können in der Praxis durchaus nützlich sein.

SQL-Änderungen in Jet 4

Jet-SQL hat sich über die fast 15 Jahre ihres Bestehens relativ wenig verändert. Die stärksten Änderungen und Erweiterungen des Sprachumfangs hat Microsoft 1999 mit dem Erscheinen von Access 2000, also mit Jet 4, vorgenommen. Access 2000 war bekanntlich die Version mit den meisten Neuerungen in der Access-Geschichte.

Unter anderem wurde mit den Access Projekten (ADP) und der Einführung von ADO die Verbindung zum hauseigenen SQL Server verstärkt. Die meisten Änderungen in Jet-SQL dienten demselben Ziel.

Sie sollten den SQL-Dialekt von Access näher an den ANSI 92-Standard heranführen und vor allem kompatibler mit dem SQL Server machen. Microsoft wollte damit in erster Linie den Umstieg oder das spätere Upsizing auf ein SQL Server-Backend erleichtern.

Aus diesem Grund und wohl auch, weil Microsoft damals die Verwendung von ADO vorantreiben wollte, lassen sich viele der Neuerungen nur über den OLE-DB-Provider und ADO aufrufen, nicht über DAO oder im Abfrageentwurfsfenster von Access.

Dennoch können sie natürlich auch in reinen Access/Jet-Applikationen verwendet werden. Sie brauchen zur Ausführung des SQL-Statements immer dann einen Verweis auf die ADO-Bibliothek, wenn es sich auf eine andere als die aktuelle Datenbank bezieht, um ein neues Connection-Objekt instanzieren zu können.

Dazu aktivieren Sie in der VBE unter Extras/Verweise den Eintrag Microsoft ActiveX Data Objects 2.x Library. Das x kann sich je nach installierter Access- oder MDAC-Version unterscheiden, spielt hier aber keine Rolle. Der Code für die Ausführung des SQL-Statements sieht dann zum Beispiel so aus:

Dim cnn As New ADODB.Connection

Dim strSQL As String

'für die aktuelle Datenbank

'Set cnn = Application.CurrentProject.Connection

'für eine andere Datenbank

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Pfad\MeineExterne.mdb;"

strSQL = "HierDasSQLStatement"

cnn.Execute strSQL

cnn.Close

Set cnn = Nothing

ALTER COLUMN

Der wichtigste neue Befehl in Jet 4, der inzwischen auch die größte Bekanntheit erlangt hat, ist ALTER COLUMN. Ich erwähne ihn hier, weil ich durchaus noch oft DAO-Code sehe, der Tabellenfelder ändert, und sich durch einen einfachen und effizienten ALTER COLUMN-Befehl ersetzen lässt. Es geht dabei meist um die Änderung des Datentyps eines Feldes oder um die Änderung der Feldgröße.

Bis Access 97 musste man dazu per DAO-Code das alte Feld umbenennen, ein neues mit dem gewünschten Datentyp oder der gewünschten Feldgröße erzeugen, die Daten in das neue Feld übernehmen und abschließend das alte Feld löschen.

Ab Access 2000 geht das alles mit einem schlichten SQL-Befehl. Folgendes Code-Beispiel ändert die Feldgröße eines Textfeldes in einer Tabelle der aktuellen Datenbank auf 100 (in einer Zeile):

Currentdb.Execute "ALTER TABLE Tabelle ALTER COLUMN Feld VARCHAR(100)"

Bei der üblichen Backend/Frontend-Aufteilung muss das Execute natürlich in der externen Backend-Datenbank ausgeführt werden. Der Code im Frontend lautet dann:

Dim db As DAO.Database

Set db = DBEngine.Workspaces(0).OpenDatabase("c:\Pfad\Andere.mdb")

db.Execute "ALTER TABLE Tabelle " _
& "ALTER COLUMN Feld VARCHAR(100)"

ALTER COLUMN funktioniert also mit DAO. Sie können auch den bloßen SQL-Befehl in die SQL-Ansicht einer Abfrage schreiben, also (in einer Zeile)

ALTER TABLE Tabelle ALTER COLUMN Feld
VARCHAR(100)

dort ausführen und auch als DDL-Abfrage (Data Definition Language) zur späteren Verwendung speichern.

Autowerte

In der Theorie sollten Autowerte nur für Mechanismen verwendet werden, die der Anwender nie zu sehen bekommt – vor allem als Primärschlüssel von Tabellen, wenn sich keine anderen Felder dafür anbieten. Für sichtbare Zählungen sollte man hingegen selbst verwaltete Zahlenfelder verwenden. Daher sollte der konkrete Wert eines Autowertfeldes eigentlich nie eine Rolle spielen. In der Praxis jedoch verwenden viele Anwender Autowertfelder als sichtbaren Zähler.

Erfahrene Entwickler verwenden Autowertfelder zwar meistens richtig. Oft möchten sie aber einen bestimmten Startwert für Tests einstellen oder bei der Erstinstallation beim Kunden den Autowert wieder mit 0 beginnen lassen – etwa, um später auf den ersten Blick zu sehen, ob sich in einer Tabelle viel tut.

Bis zum Service Pack 4 von Jet 4 konnte man alle Autowerte durch einfaches Komprimieren der Datenbank auf den niedrigsten freien Wert zurücksetzen. Seit dem SP 4 funktioniert das nicht mehr.

Microsoft liefert dazu den KnowledgeBase-Artikel 287756, der einen umständlichen Workaround mit viel Code anbietet. Wesentlich einfacher ist das Einstellen des Startwertes per SQL:

ALTER TABLE Tabelle ALTER COLUMN MeinAutowertFeld COUNTER (1)

Damit beginnt der Autowert in der Tabelle wieder bei 1 zu zählen.

Neben dem Startwert lässt sich mit einem zweiten Parameter auch die Schrittweite des Feldes einstellen:

ALTER TABLE Tabelle ALTER COLUMN MeinAutowertFeld COUNTER (100,3)

Der nächste Autowert der Tabelle lautet 100, dann folgen 103, 106 und so weiter. Diese Option wird man in der Praxis wohl nur selten einsetzen.

Synonyme

Statt COUNTER wären für den Datentyp Autowert im vorigen Beispiel ebenso gut die Synonyme AUTOINCREMENT oder IDENTITY einsetzbar. Generell gibt es seit Jet 4 für jeden Datentyp mehrere Synonyme, von denen mindestens eines aus dem Sprachumfang von T-SQL stammt. Also wieder der Versuch, den Umstieg auf den Microsoft SQL Server zu erleichtern.

Bei diesen Synonymen wurde sogar mehr Wert auf diesen Aspekt gelegt als auf den ANSI SQL Standard. So gibt es beispielsweise für den Datentyp OLE-Objekt vier mögliche Varianten: OLEOBJECT, IMAGE, LONGBINARY, GENERAL. Sie können also zum Beispiel ein OLE-Objekt-Feld wie folgt zu einer Tabelle hinzufügen:

ALTER TABLE Tabelle ADD COLUMN NeuesFeld IMAGE

IMAGE ist T-SQL. Im ANSI Standard heißt der Typ hingegen BLOB.

Eine Auflistung der Synonyme samt Vergleich mit ANSI und T-SQL gibt es in allen Onlinehilfen ab Access 2000, Suchbegriff ANSI SQL-Datentypen. Manche der Synonyme sind nur per OLE DB/ADO einsetzbar.

Standardwerte

Bis Access 97 kann man Standardwerte für Tabellenfelder nur per DAO-Code einstellen. Ab Access 2000 gibt es das SQL-Schlüsselwort DEFAULT. Es ist nur per OLE DB/ADO einsetzbar.

Sie können ein entsprechendes SQL-Statement also wie weiter oben beschrieben aufrufen oder, falls es um die aktuelle Datenbank geht, auch in einer verkürzten ADO-Schreibweise:

CurrentProject.Connection.Execute _

"ALTER TABLE Tabelle " _
& "ALTER COLUMN Feld VARCHAR DEFAULT Hallo"

Das setzt den Standardwert des Feldes auf den Text Hallo. Anführungszeichen oder Hochkommas sind dabei nicht anzuführen. Hochkommas würden als Teil des Standardwertes angesehen und angezeigt. Wenn Sie versuchen, das SQL-Statement im Abfrageentwurf auszuführen oder mit der DAO-Entsprechung der Kurzschreibweise in der folgenden Anweisung, erhalten Sie den Syntaxfehler aus Abb. 1:

Currentdb.Execute _

"ALTER TABLE Tabelle " _
& "ALTER COLUMN Feld VARCHAR DEFAULT Hallo"

Abbildung1.tif

Abb. 1: Viele Jet 4 SQL-Schlüsselwörter funktionieren nur per ADO

Da die Microsoft-Dokumentation mangelhaft und in machen Fällen diesbezüglich sogar fehlerhaft ist, sollten Sie bei Verwendung der neuen Schlüsselwörter besser selbst testen, ob sie in einer Abfrage oder per DAO ausführbar sind oder nur per ADO.

Referentielle Integrität

Ebenfalls nur mit ADO können Sie per SQL die referentielle Integrität (RI) samt Aktualisierungs- und Löschweitergabe einstellen.

Angenommen, Sie haben die zwei Tabellen tblKunden und tblBestellungen und möchten nachträglich eine Beziehung inklusive referentieller Integrität, Aktualisierungs- und Löschweitergabe für diese beiden Felder setzen. Dann können Sie die folgenden SQL-Anweisung verwenden:

ALTER TABLE tblBestellungen

ADD CONSTRAINT FK_tblBestellungen

FOREIGN KEY (KundenID) REFERENCES

tblKunden (KundenID)

ON UPDATE CASCADE

ON DELETE CASCADE

Check Constraints

Ein sehr interessanter Ansatz in Jet 4-SQL sind Check Constraints. „Ansatz“ deshalb, weil sie leider nicht wirklich komplett implementiert wurden.

Access bietet ja seit jeher die Möglichkeit, Gültigkeitsregeln und dazu passende Gültigkeitsmeldungen auf Tabellenebene zu verwenden. Check Constraints sind zunächst nichts anderes als diese Gültigkeitsregeln.

Ein einfaches Beispiel: Es gibt eine Tabelle tblRechnungen mit einem Feld Betrag. Sie möchten verhindern, dass ein negativer Betrag eingegeben wird.

Das SQL-Statement dafür, das wieder nur per ADO funktioniert, sieht so aus:

ALTER TABLE tblRechnungen

ADD CONSTRAINT CheckBetrag

CHECK (Betrag >= 0)

Beim Versuch, einen negativen Betrag einzugeben, erhält man die auch bei Gültigkeitsregeln übliche Meldung (Abb. 2).

Abbildung2.tif

Abb. 2: Gültigkeitsmeldung bei Check Constraints

Die große Schwäche von Check Constraints ist, dass man diese Meldung nicht wie bei einer klassischen Gültigkeitsregel ändern kann. Deshalb sind Check Constraints nur beschränkt praxistauglich.

Sie sind an der Oberfläche nicht sichtbar, sondern nur per VBA zu erstellen und zu löschen. Das Löschen funktioniert dabei mit DROP:

ALTER TABLE tblRechnungen

DROP CONSTRAINT CheckBetrag

Eine weitere scheinbare Einschränkung ist, dass der Name des Constraints, im Beispiel CheckBetrag, datenbankweit einmalig sein muss. Hier wird es nun interessant, denn diese Einmaligkeit ist deshalb nötig, weil man Check Constraints tabellenübergreifend einsetzen kann (s. Abb. 3).

Abbildung3.tif

Abb. 3: Meldung bei tabellenübergreifendem Constraint

Etwas, das Gültigkeitsregeln nicht leisten, und abgesehen von der referentiellen Integrität die einzige Möglichkeit in Access ist, Werte in einer Tabelle von Werten in einer anderen Tabelle abhängig zu machen.

Wieder ein einfaches Beispiel für die Möglichkeiten, die sich daraus ergeben: Sie haben eine Tabelle tblKunden, in der sich unter anderem ein Feld KundenLimit befindet, das den Kreditrahmen des Kunden darstellt. Sie möchten nun ein Höchstlimit für alle Kunden fix vorgeben. Deshalb haben Sie eine eigene Tabelle tblKundenLimit mit dem Feld HoechstLimit, das sie auf 10.000 gesetzt haben. Einen Check Constraint, der diese Grenze in der Kundentabelle durchsetzt, können Sie wie folgt erzeugen:

CurrentProject.Connection.Execute

"ALTER TABLE tblKunden " _
& "ADD CONSTRAINT CheckLimit " _

& "CHECK(KundenLimit <= " _
& "(SELECT HoechstLimit " _
& "FROM tblKundenLimit))"

Versucht nun jemand, bei einem Kunden ein höheres Limit als 10.000 einzugeben, so lässt Access das nicht zu und quittiert den Versuch mit der für Gültigkeitsregeln üblichen Meldung (s. Abb. 3).

Check Constraints erlauben also tabellenübergreifende Gültigkeitsregeln. Da Microsoft leider keine Möglichkeit zur Gestaltung der Gültigkeitsmeldung eingebaut hat, kann man sie eventuell in manchen Fällen als Sicherheitsnetz einziehen, für den Fall, dass in Formularen programmierte Geschäftsregeln nicht greifen.

Die Stärke einer Regel auf Tabellenebene ist ja, dass sie weder in Formularen, noch Abfragen oder durch Eingabe in die betroffene Tabelle umgangen werden kann.

Mein Wunsch als Datenbankentwickler wäre, dass Microsoft Features wie Check Constraints weiterentwickeln würde, um Jet/ACE als ernsthafte Datenbank-Engine aufzuwerten. Leider konzentriert man sich in Redmond in den letzten Jahren mehr auf die Oberflächenkosmetik.

Noch vieles mehr

Mit Jet 4 wurden zahllose weitere neue SQL-Befehle eingeführt, von denen die meisten aber zu Recht ein Schattendasein führen.

Sie hatten schon wegen der anderweitig guten Programmierbarkeit, vor allem durch DAO, keine Chance, sich durchzusetzen. Ein paar seien dennoch stichwortartig genannt, um den Umfang der Änderungen anzudeuten:

  • Erstellen von Abfragen mit CREATE VIEW, Löschen mit DROP VIEW.
  • Erstellen von Stored Procedures beziehungsweise Parameterabfragen mit CREATE PRODECURE, Ausführung und Parameterübergabe mit EXECUTE, Löschen mit DROP PROCEDURE.
  • Steuern von Transaktionen mit BEGIN TRANSACTION, COMMIT [TRANSACTION], ROLLBACK [TRANSACTION], wie zuvor nur per DAO- oder ADO-Code möglich.
  • Steuern des Access-Sicherheitssystems mit Befehlen wie CREATE USER, CREATE GROUP, ADD USER, ALTER USER, ALTER DATABASE PASSWORD, DROP USER, GRANT und REVOKE.

Die einzige brauchbare, wenn auch lückenhafte Dokumentation von Microsoft für die Spracherweiterungen in Jet 4-SQL sind drei MSDN-Whitepapers. Ihre URL ändert sich immer wieder einmal.

Derzeit finden Sie sie unter http://msdn2.microsoft.com/en-us/library/aa139974(office.10).aspx.

Die Whitepapers heißen:

  • Fundamental Microsoft Jet SQL for Access 2000
  • Intermediate Microsoft Jet SQL for Access 2000
  • Advanced Microsoft Jet SQL for Access 2000

Kompletten Artikel lesen?

Einfach für den Newsletter anmelden, dann lesen Sie schon in einer Minute den kompletten Artikel und erhalten die Beispieldatenbanken.

E-Mail:

© 2003-2015 André Minhorst Alle Rechte vorbehalten.