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

Gedrucktes Heft

Diesen Beitrag finden Sie in Ausgabe 2/2002.

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

Von Access zum SQL-Server: Der Einstieg

Autor: Klaus Giesen, Wuppertal

Obwohl Access nicht nur ein komfortables, sondern auch ein leistungsfähiges Datenbanksystem ist, kann es vorkommen, dass Sie an seine Leistungsgrenzen stoßen. Diese Situation wird zwar nur bei einem Einsatz von Access in einer Netzwerkumgebung auftreten, doch da gerade Access sehr häufig auch in einer solchen Umgebung eingesetzt wird, ist auch diese Thematik für einen größeren Personenkreis relevant. Glücklicherweise stellt Ihnen Access 2000 zur Lösung einer solchen Problematik eine verblüffend einfache und obendrein auch noch preiswerte Lösung zur Verfügung: Die so genannte MSDE, die ein zwar abgespeckter, aber trotzdem vollwertiger und leistungsfähiger SQL-Server ist.

Dieser Beitrag soll zeigen, wie Sie eine Access-Datenbank auf den SQL-Server bzw. die MSDE portieren. Es geht dabei lediglich um einen Einstieg in die Welt der SQL-Server. Andererseits ist dieser Lösungsansatz sicherlich für einen Großteil der Anforderungen vollkommen ausreichend.

Hintergründe und Begriffe

Bei der Portierung einer Access-Datenbank auf die MSDE gibt es verschiedene Regeln zu beachten, die mit unterschiedlichen Datentypen, eingebauten Funktionen und der unterschiedlichen Funktionalität zusammenhängen. Außerdem verfügt ein Access-Projekt bzw. die MSDE über Objekte, die eine Access-MDB gar nicht kennt.

Aus Platzgründen wird nur auf die wichtigsten Unterschiede eingegangen. Einzelheiten entnehmen Sie der Dokumentation zur MSDE.

Gegenüberstellung von
Datenbankobjekten

Zunächst eine Gegenüberstellung von Datenbankobjekten, die Ihnen in einem Access-Projekt bzw. in einer Access-MDB begegnen können (s. Tabelle 1).

Access-MDB

Access-Projekt

Tabelle

Tabelle

Auswahlabfrage

Sicht (View)

Aktions- bzw. Parameterabfrage

Gespeicherte Prozedur

Beziehung

Datenbankdiagramm

Formular

Formular

Bericht

Bericht

Datenzugriffsseite

Datenzugriffsseite

Makro

Makro

Modul

Modul

Tab. 1: Gegenüberstellung von Datenbankobjekten

Die typischen MSDE-Objekte werden weiter unten erläutert.

Gegenüberstellung von Datentypen

In Access stehen neun Datentypen zur Verfügung, wobei beim Datentyp Zahl über die Eigenschaft Feldgröße weitere Einstellungen vorgenommen werden können. Die MSDE kennt einige weitere Datentypen (s. Tabelle 2).

Hyperlink-Felder aus Access werden in den Datentyp NTEXT umgewandelt und verlieren ihre Funktionalität.

MSDE

Access

VARCHAR, NVARCHAR

Text

CHAR, NCHAR

Text

TEXT, NTEXT

Memo

TINYINT

Zahl (Byte)

SMALLINT

Zahl (Integer)

INT

Zahl, AutoWert (Long Integer)

REAL

Zahl (Single)

FLOAT

Zahl (Double)

UNIQUEIDENTIFIER

Zahl (Replikations-ID)

BIT

Ja/Nein

MONEY, SMALLMONEY

Währung

DATETIME, SMALLDATETIME

Datum/Zeit

IMAGE

OLE-Objekt

keine Entsprechung

Hyperlink

TIMESTAMP

keine Entsprechung

Tab. 2: Gegenüberstellung der Datentypen

Weiterhin erlaubt die MSDE noch benutzerdefinierte Datentypen, für die es bei Access kein Gegenstück gibt.

Gegenüberstellung des
SQL-Befehlssatzes

Der SQL-Befehlssatz von Access und der MSDE bzw. dessen Implementierung ist in einigen Punkten doch recht unterschiedlich. Neben Zeichen und Operatoren (letzter Abschnitt) gibt es weitere wichtige Unterschiede:

Ausgabefelder

Access erlaubt in einer Abfrage mehrere Ausgabefelder mit gleichem Namen, die MSDE erlaubt dies in einem View nicht.

SQL-Element DISTINCTROW

Access unterstützt dieses Element und erlaubt darüber hinaus die Auswahl von eindeutigen Datensätzen. Die MSDE unterstützt diese Funktionen nicht.

Vergleich von

Access

MSDE

Datumsangaben

Leiterzeichen (#)

Einfaches Anführungszeichen (')

Zeichenfolge

Einfaches (') oder doppelte Anführungszeichen (")

Einfaches Anführungszeichen (')

Mod-Operator

Mod

Prozentzeichen (%)

Verkettung von Zeichenfolgen

Ampersand (&)

Pluszeichen (+)

Platzhalter für ein einzelnes Zeichen

Fragezeichen (?)

Unterstrich (_)

Platzhalter für keines oder mehrere Zeichen

Sternzeichen (*)

Prozentzeichen (%)

Konstante für True

Ja, Ein, Wahr, -1

1

Konstante für False

Nein, Aus, Falsch, 0

0

Tab. 3: Vergleich von Trennzeichen und Operatoren

SQL-Befehle COMMIT UND LOCK

Diese SQL-Befehle für Zugriffsrechte werden von Access nicht unterstützt, können dort aber mithilfe von VBA nachgebildet werden.

VBA-Anweisungen in SQL

Access erlaubt die Verwendung von beliebigen SQL-Anweisungen. Diese nicht unerhebliche Funktionserweiterung steht für die MSDE nicht zur Verfügung.

Trennzeichen und Operatoren

Die MSDE und Access verwenden unterschiedliche Trennzeichen, Operatoren, Platzhalterzeichen in der WHERE-Klausel eines SQL-Strings und Zeichen für Datums- bzw. Textangaben.

Die Unterschiede werden in Tabelle 3 gegenübergestellt.

Abb. 1: Datenbankdiagramm

Abb. 2: Formular-Steuerelement als Kriterium in einer Access-Auswahlabfrage

Spezielle MSDE-Objekte

In den folgenden Abschnitten lernen Sie einige typische MSDE-Objekte und ihre wichtigsten Eigenschaften kennen.

Datenbankdiagramme

Datenbankdiagramme entsprechen im Prinzip dem Beziehungsfenster von Access.

Nur können dort neben dem reinen Festlegen und Bearbeiten von Beziehungen beispielsweise auch Tabellen- und Feldeigenschaften sowie Indizes angelegt bzw. bearbeitet werden (s. Abb. 1).

Views

Views entsprechen den Auswahlabfragen in Access - mit einigen wichtigen Unterschieden:

  • Die Verwendung der ORDER BY-Klausel ist nicht möglich. Eine gewünschte Sortierung muss mithilfe einer gespeicherten Prozedur realisiert werden.
  • Views dürfen keine Parameter enthalten. Auch hier muss eine gespeicherte Prozedur verwendet werden, falls Parameter übergeben werden müssen.
  • Views können keine Verweise auf Formulare oder Steuerelemente in Formularen enthalten.
  • Sie können beispielsweise keine Kriterien in Sichten verwenden, wie Sie das bisher in Access-Abfragen getan haben - z. B. wie in der in Abb. 2 dargestellten Abfrage.

    Gespeicherte Prozeduren

    Gespeicherte Prozeduren (englisch Stored Procedures) sind vorkompilierter Code, der serverseitig ausgeführt wird (s. Abb. 3).

    Abb. 3: Gespeicherte Prozedur

    Eine gespeicherte Prozedur kann neben SQL-Befehlen auch aus Kommandos zur Programmsteuerung (Schleifen, Verzweigungen, Fehlerbehandlungsroutinen usw.) bestehen.

    Alter TRIGGER "tblBuch_DTrig" ON tblBuch FOR DELETE 

    AS

    SET NOCOUNT ON

    /* LÖSCHWEITERGABE AN 'tblBuchAutor' */

    DELETE tblBuchAutor FROM deleted, tblBuchAutor 

    WHERE deleted.BuchID = tblBuchAutor.BuchID

    Quellcode 1

    Bei der MSDE findet hierfür die schon erwähnte Programmiersprache Transact-SQL Verwendung. Der Einsatz von gespeicherten Prozeduren hat folgende Vorteile:

  • Weil SQL-Befehle und Programmlogik direkt auf dem Server abgearbeitet werden, lässt sich die Netzwerkbelastung entsprechend reduzieren.
  • Transact-SQL erlaubt durch seinen Sprachumfang auch komplexe Programmabläufe.
  • Gespeicherte Prozeduren können durch die Vergabe von Ausführungsberechtigungen in das Sicherheitskonzept der MSDE integriert werden.
  • Trigger

    Trigger sind eine Sonderform von gespeicherten Prozeduren. Sie sind immer an Tabellen gebunden und werden ausgeführt, wenn das Ereignis eintritt, für das sie programmiert worden sind.

    Trigger können für Einfüge-, Lösch- und Aktualisierungsaktionen verwendet werden. Man spricht deshalb auch von Insert-, Delete- und Update-Triggern.

    Ein typischer Einsatzbereich für Trigger ist die von Access her bekannte Lösch- bzw. Aktualisierungsweitergabe an eine Detailtabelle.

    Die Umsetzung der Löschweitergabe durch einen Trigger schaut beispielsweise wie in Quellcode 1 aus.

    Der Upsizing-Assistent

    Das Upsizing einer Access-Datenbank wird - wie schon erwähnt - in der Regel dann notwendig, wenn die Anwendung zu groß geworden ist und/oder zu viele Anwender gleichzeitig auf die Anwendung zugreifen. Beides äußert sich durch identische Symptome:

    Bei der Arbeit mit der Anwendung wird keine zufriedenstellende Geschwindigkeit mehr gewährleistet.

    Der einfachste Weg bei der Umstellung einer Jet-Datenbank auf die MSDE oder die Vollversion des SQL-Servers ist der Einsatz des Upsizing-Assistenten von Access 2000.

    Dieser Assistent kann viel, aber beileibe nicht alles. Daraus ergibt sich zweierlei:

  • Der Assistent erspart in jedem Fall eine ganze Menge Handarbeit.
  • Sie müssen andererseits damit rechnen, an einigen Punkten nachbessern zu müssen.
  • Um zu zeigen, wie Sie eine Datenbank für den Einsatz in einem größeren Netzwerk fit machen, verwenden wir eine Bücher-Datenbank. Sie finden die Datenbank auf der Heft-CD-ROM.

    Neue SQL-Server-Datenbank
    erstellen

    Stellen Sie als Erstes sicher, dass die MSDE gestartet ist.

    Öffnen Sie dann die Datenbank, die auf die MSDE portiert werden soll. Wählen Sie anschließend aus dem Menü Extras den Befehl Datenbank-Dienstprogramme ( Upsizing-Assistent aus.

    Abb. 4: Das Dialogfenster Upsizing-Assistant I

    Abb. 5: Das Dialogfenster Upsizing-Assistent II

    Im ersten Dialogfenster des Assistenten (s. Abb. 4) wählen Sie aus, ob Sie eine vorhandene SQL-Server-Datenbank verwenden oder eine neue erstellen wollen. Im Normalfall werden Sie die zweite Option verwenden.

    Verbindungs- und
    Anmeldungsdaten

    Im nächsten Schritt des Assistenten (s. Abb. 5) legen Sie den Namen des SQL-Servers, die Anmeldungs-ID, ein Kennwort und den Namen der neuen SQL-Server-Datenbank fest.

    Falls die MSDE auf einem Windows NT- oder Windows-2000-System läuft, können Sie die Anmeldungsinformationen von Windows benutzen.

    Anderenfalls ist für die MSDE immer der Benutzer sa (system administrator) eingerichtet, der mit allen Rechten ausgestattet ist. Schon deshalb ist es auf jeden Fall empfehlenswert, ein Anmeldekennwort zu verwenden.

    Wählen Sie im nächsten Schritt die Tabellen aus, die auf die MSDE portiert werden sollen.

    Tabellenattribute auswählen und
    Beziehungstyp
    festlegen

    Im nächsten Schritt des Assistenten (s. Abb. 6) wird Ihnen ein Dialogfenster mit verschiedenen aktivierten Kontrollkästchen angeboten. Da es sich hierbei um wichtige Tabellenattribute handelt, ist es sinnvoll, die einzelnen Punkte Schritt für Schritt zu betrachten.

    Indizes

    Der Assistent versucht, Indizes für die MSDE zu erstellen, bei denen die vorhandenen Access-Indizes abgebildet werden.

    Das heißt, jeder Primärschlüssel in Access wird auch als Primärschlüssel in der SQL-Server-Tabelle festgelegt. Entsprechendes gilt natürlich auch für die Fremdschlüssel.

    Abb. 6: Das Dialogfenster Upsizing-Assistent III

    Abb. 7: Festlegung der Upsizing-Methode

    Hinweis

    Bei SQL-Server-Indizes kann im Gegensatz zu Access keine auf- bzw. absteigende Sortierung festgelegt werden. (

    Gültigkeitsregeln

    Falls das Kontrollkästchen Gültigkeitsregeln aktiviert ist, setzt der Assistent vorhandene Gültigkeitsregeln in Trigger auf dem SQL-Server um - zumindest in der Theorie.

    In der Praxis ist es durchaus üblich, dass dies nicht funktioniert. Dann muss die Umsetzung anschließend manuell erfolgen - oder Sie verzichten auf die Gültigkeitsregeln.

    Standardwerte

    Der Assistent versucht, in Access definierte Standardwerte in die SQL-Server-Datenbank zu übernehmen.

    Tabellenbeziehung

    Tabellenbeziehungen können entweder über DRI (Declared Reference Integrity) oder mithilfe von Triggern umgestellt werden.

    Die Auswahl DRI ist dann sinnvoll, wenn die Access-Beziehungen keine Aktualisierungs- bzw. Löschweitergaben besitzen. Die DRI des SQL-Servers besitzt diese Eigenschaften nicht.

    Falls die Access-Beziehungen jedoch Aktualisierungs- bzw. Löschweitergaben besitzen, muss die Option Mit Triggern ausgewählt werden.

    Der SQL-Server kann Aktualisierungs- bzw. Löschweitergaben nur über Trigger realisieren. Dabei erhalten alle Detailtabellen Insert-Trigger, während die Mastertabellen Delete-Trigger erhalten.

    Timestamp-Felder

    Die MSDE speichert in Timestamp-Feldern die letzte Aktualisierung eines Datensatzes.

    Diese Auswahl ist sinnvoll. Damit kann beispielsweise in einer Mehrbenutzerumgebung genau festgestellt werden, wann zuletzt auf den Datensatz zugegriffen wurde.

    Abb. 8: Der Berichts-Snapshot

    Tabellenstruktur ohne Daten

    Die Auswahl dieser Option bietet sich beispielsweise für den Fall an, wenn - bei einem größeren Datenbestand - nur festgestellt werden soll, inwieweit das Upsizing problemlos funktioniert.

    Festlegung der Upsizing-Methode

    In der Regel werden Sie bei der Festlegung der Upsizing-Methode (s. Abb. 7) die letzte Option Eine neue Access-Client/Server-Anwendung erstellen auswählen.

    Der Assistent legt in diesem Fall ein neues Access-Projekt an, dessen Namen Sie frei festlegen können.

    Die Standardbenennung ist der Datenbankname mit den angehängten Buchstaben CS.

    Falls Sie das Kontrollkästchen Kennwort und Benutzer-ID speichern aktivieren, brauchen sich die Benutzer nicht bei der MSDE anzumelden.

    Im Grunde genommen sind damit alle Einstellungen und Auswahlmöglichkeiten abgeschlossen und Sie können schon jetzt auf die Schaltfläche Fertig stellen klicken.

    Das Upsizing-Protokoll

    Nach der Umwandlung - die etwas dauern kann - wird ein Protokoll in Form eines Berichts-Snapshots angezeigt, der auch gespeichert werden kann (s. Abb. 8).

    Dieser Bericht enthält eine detaillierte Beschreibung aller umgewandelten Datenbankobjekte und zeigt vor allem auch eventuell aufgetretene Fehler auf.

    Das neue Access-Projekt

    Das neue Access-Projekt zeigt im Datenbankfenster die Objekte des Access-Projekts an.

    Bevor auf einige wichtige Punkte eingegangen wird, soll zuerst einmal die eventuell notwendige Nacharbeit erledigt werden.

    Notwendige Nacharbeit

    In der Tabelle tblBuch ist im Feld txtKauf die Gültigkeitsregel

    >=Jahr(Jetzt())

    hinterlegt, die beim Upsizing jedoch nicht in einen Trigger umgewandelt worden ist. Wahrscheinlich konnte der Assistent nichts mit den Accessfunktionen Year() und Now() anfangen, die der SQL-Server so nicht kennt.

    Die passenden Transact-SQL-Funktionen heißen DATEPART und GETDATE. Mit deren Hilfe muss nun ein passender Trigger geschrieben werden.

  • Markieren Sie im Datenbankfenster die Tabelle tblBuch, drücken Sie die rechte Maustaste und wählen Sie aus dem Kontextmenü den Befehl Trigger aus.
  • Create Trigger "tblBuch_Trigger_Ueberpruefe_Jahr"

    /* Gültigkeitsregel für das Feld txtKauf */

    On dbo.tblBuch

    For Insert, Update

    As

        IF (SELECT COUNT(*) FROM inserted 

        WHERE txtKauf <= DATEPART(yyyy,GetDate())) = 0

            BEGIN

                RAISERROR 555555 'Das Jahr des Kaufs kann 
                    nur das aktuelle  oder ein früheres Jahr 
                    sein!'

                ROLLBACK TRANSACTION

            END

    Quellcode 2

    Abb. 9: Das Dialogfenster Trigger für Tabellen

    Abb. 10: Editorfenster zur Erstellung eines Triggers

  • Klicken Sie im Dialogfenster Trigger für Tabellen: tblBuch auf die Schaltfläche Neu (s. Abb. 9). Daraufhin wird ein Editorfenster angezeigt, das die Grundstruktur eines Triggers enthält (s. Abb. 10).
  • Geben Sie die gespeicherte Prozedur aus Quellcode 2 ein und speichern Sie den Trigger.
  • Damit sind Gültigkeitsregel und -meldung wieder vorhanden.

    Tabellen

    Die im Datenbankfenster angezeigten Tabellen befinden sich, ohne dass dies besonders gekennzeichnet ist, auf dem SQL- Server.

    Entwurfsansicht

    Die Tabellen können, wie Sie es von Access her gewohnt sind, in der Entwurfsansicht geöffnet und bearbeitet werden.

    Die Darstellung und die Einstellmöglichkeiten entsprechen denen des SQL-Servers. Desgleichen stehen die entsprechenden Datentypen zur Verfügung, so wie sie der Upsizing-Assistent umgesetzt hat (s. Abb. 11).

    Umsetzung der referentiellen Integrität

    Die Umsetzung der referentiellen Integrität auf dem SQL- Server soll am Beispiel einer Aktualisierungsweitergabe verdeutlicht werden.

    Markieren Sie wieder die Tabelle tblBuch im Datenbankfenster und öffnen Sie über das Kontextmenü mit dem Befehl Trigger das Dialogfenster Trigger für Tabellen.

    Wählen Sie aus dem Listenfeld Triggername den Trigger tblBuch_Utrig und klicken Sie auf die Schaltfläche Bearbeiten. Im Editorfenster wird der vom Assistenten erstellte Update-Trigger angezeigt (s. Abb. 12). Der Code beginnt mit dem Schlüsselwort Alter, mit dem die Änderung eines Triggers gekennzeichnet wird.

    Abb. 11: Tabelle in der Entwurfsansicht

    Hinweis

    Mit den Schlüsselwörtern deleted und inserted werden temporäre Tabellen auf dem SQL-Server bezeichnet. (

    Das Upsizing von Access-Abfragen

    Abb. 12: Update-Trigger im Editorfenster

    Alter PROCEDURE selfrmAutor

    AS

    SELECT * FROM "selfrmAutorView"

    ORDER BY "selfrmAutorView".txtNachname, 
        "selfrmAutorView".txtVorname

    Quellcode 3

    Der Upsizing-Assistent hat die Abfragen aus der Access-Datenbank auf Views und gespeicherte Prozeduren verteilt.

    Sortierung

    Sortierte Abfragen wurden dabei in einen View und eine zusätzlich gespeicherte Prozedur umgesetzt, weil eine direkte Sortierung von Views nicht möglich ist.

    Als typisches Beispiel dafür kann die Datenherkunft für das Formular frmAutor herangezogen werden (s. Abb. 13).

    Im Access-Projekt ist die gespeicherte Prozedur selfrmAutor die Datenherkunft des Formulars frmAutor (s. Quellcode 3).

    Dort wird der View selfrmAutor nach Nachnamen und Vornamen aufsteigend sortiert.

    Das Präfix ut_

    Das Präfix ut_ bei Views und gespeicherten Prozeduren kennzeichnet Access-Abfragen, die als Datenherkunft für Steuerelemente in Formularen und Berichten Verwendung finden.

    Die Anfügeabfrage aus Access

    Die Anfügeabfrage aus Access ist vom Assistenten in die folgende gespeicherte Prozedur umgesetzt worden:

    Alter PROCEDURE insAlteTitel

    AS

    INSERT INTO tblBuch (txtTitle) 

    SELECT tblAlteTitel.txtTitelAlt

    FROM tblAlteTitel

    Formulare

    Abb. 13: View in der Entwurfsansicht

    Abb. 14: Einstellung der maximal im Formular angezeigten Datensätze

    Über Formulare in Access-Projekten gäbe es eine Menge zu sagen. In diesem Beitrag soll jedoch lediglich auf einen signifikanten Unterschied zu normalen Access-Formularen hingewiesen werden: die Einschränkung der angezeigten Datensätze im Formular durch die Eigenschaft MaxRecords (s. Abb. 14).

    In SQL-Server-Datenbanken können durchaus mehrere 100.000 Datensätze gespeichert sein. Falls diese beim Öffnen eines Formulars alle angezeigt und über das Netzwerk transportiert werden, ist das keine gute Lösung. Um diesen Prozess steuern zu können, gibt es die oben erwähnte Eigenschaft MaxRecords.

    Die Einstellung wird entweder im Eigenschaftsfenster oder über den Schieberegler vorgenommen, der mit einem Klick auf die entsprechende Schaltfläche neben den Navigationsschaltflächen geöffnet wird.

    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.