Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.
Die einfache Migration der Tabellen einer Datenbank von Access zum SQL Server ist meist schnell erledigt. Der SQL Server Migration Assistant (SSMA) leistet gute Arbeit und schnell landen alle Tabellen in der SQL Server-Datenbank – samt Erstellung entsprechender Tabellenverknüpfungen im Access-Frontend. Damit lässt sich erst einmal arbeiten, da die Verknüpfungen Lesen und Schreiben der Daten wie gewohnt zulassen. Früher oder später wird man jedoch auf Abfragen im Access-Frontend stoßen, die schlicht zu langsam sind. Und hier kann der SQL Server sein wahres Potential ausspielen: Zum Beispiel, indem wir dort eine Stored Procedure (Gespeicherte Abfrage) anlegen, welche die Abfrage für uns direkt auf dem SQL Server ausführt, was viel schneller gehen wird. Und genau das ist Thema dieses Beitrags: Wie bekommen wir eine mehr oder weniger komplizierte Abfrage als Stored Procedure zum SQL Server und rufen diese von der Access-Datenbank aus auf?
Der SQL Server hat gegenüber einer Access-Datenbank eine besondere Stärke: Im Vergleich zu der Fileserver-Datenbank von Access kann der SQL Server Abfragen direkt auf dem Server ausführen und nur die Daten ausliefern, die tatsächlich benötigt werden.
Bei Verwendung einer aufgeteilten Access-Datenbank werden immer alle Daten auf den anfragenden Rechner übertragen und dort ausgewertet. Was schneller geht, liegt auf der Hand, vor allem, je mehr Daten von der Abfrage betroffen sind.
Deshalb schauen wir uns in diesem Beitrag im Detail an, wie wir eine reine Access-Abfrage zum SQL Server transferieren und dort eine Stored Procedure daraus erzeugen.
Voraussetzungen
Damit wir in diesem Artikel reibungslos starten können, setzen wir voraus, dass die grundlegenden Migrationsarbeiten schon erledigt sind – also dass die Tabellen der Datenbank bereits auf dem SQL Server liegen und vom Frontend aus per ODBC-Tabellenverknüpfung gelesen und geschrieben werden können.
Vor- und Nachteile von Stored Procedures
Kurz die wichtigsten Gründe für oder gegen gespeicherte Prozeduren: Gespeicherte Prozeduren können genau wie Access-Abfragen mit Parametern und allen anderen dort genutzten Funktionen verwendet werden und sie sind sehr schnell. Der Preis, den man bei Verwendung gespeicherter Prozeduren bezahlt, ist die fehlende Möglichkeit zum Ändern der Daten. Wenn wir eine gespeicherte Prozedur zum Lesen von Daten nutzen, um eine Access-Abfrage zu ersetzen, die auf Basis von per ODBC verknüpften Tabellen funktioniert, können wir die gelieferten Daten nicht direkt ändern beziehungsweise neue Datensätze anlegen oder vorhandene Datensätze löschen.
Das ist aber in vielen Fällen auch nicht nötig – wir können mit gespeicherten Prozeduren erst einmal Daten etwa für Übersichten mit allen Datensätzen einer Tabelle holen, diese in einem Formular anzeigen und in einem weiteren Formular den zu bearbeitenden Datensatz erneut einlesen – diesmal auf Basis der per ODBC verknüpften Tabelle.
Beispielabfrage aus der Praxis
Anlass für diesen Beitrag war eine Abfrage, mit der ich die Kunden ermittle, die in einem bestimmten Zeitraum ein oder mehrere bestimmte Produkte erworben haben und für die ich noch weitere Kriterien festlegen wollte.
Die Abfrage habe ich bisher in Access auf die ODBC-Tabellenverknüpfungen angewendet. Die Abfrage lautet beispielsweise:
SELECT DISTINCT KundeID, AnredeID, Nachname, Vorname, EMail, Land, Land_Rechnung FROM tblKunden WHERE KundeID IN ( SELECT tblAbonnements.KundeID FROM tblAbonnements WHERE tblAbonnements.ProduktID IN (146) AND Startdatum >= #2024/01/01 00:00:00# AND Startdatum <= #2024/02/01 00:00:00#)
AND KundeID NOT IN (
SELECT tblAbonnements.KundeID
FROM tblAbonnements
WHERE tblAbonnements.ProduktID IN (96, 97)AND tblKunden.NLAbgemeldetAm IS NULL AND ( tblKunden.Land = ''Deutschland'' OR tblKunden.Land_Rechnung = ''Deutschland'')
Ich benötige also alle Kunden, denen über die Tabelle tblAbonnements das Abonnement mit der Nummer 146 zugeteilt wurde und deren Startdatum in einem bestimmten Datumsbereich liegt.
Zusätzlich soll der Kunde nicht die Produkte 96 und 97 gebucht haben. Außerdem sollte der Kunde sich noch nicht vom Newsletter abgemeldet haben und entweder die Liefer- oder die Rechnungsadresse sollte in Deutschland liegen.
Diese Abfrage wird zur Laufzeit per VBA zusammengestellt. Die notwendigen Parameter ermittle ich mit dem Formular aus Bild 1. Hier lasse ich mir zuerst alle Produkte anzeigen, die einen bestimmten Namen haben, übertrage dieses Produkt in die Liste oben rechts und füge gegebenenfalls noch Produkte in die Liste der nicht bestellten Produkte hinzu. Darunter gebe ich das Bestelldatum ein und das Land des Kunden. Außerdem kann ich noch angeben, ob Kunden ausgeschlossen werden sollen, die den Newsletter abgemeldet haben.
Bild 1: Formular zum Zusammenstellen der Abfrage
Diese Parameter unter VBA zu einer funktionierenden Access-Abfrage zusammenzustellen ist eine Fleißarbeit, aber für jeden Programmierer zu bewerkstelligen. Der Code enthält eine Reihe von If…Then-Bedingungen, die prüfen, ob eine bestimmte Option einen Wert enthält und fügt die entsprechenden Kriterien zur Abfrage hinzu.
Der Weg zur Stored Procedure
Um diese Abfrage in eine gespeicherte Prozedur umzuwandeln und diese zu nutzen, sind einige Schritte nötig:
- Übertragen einer Version der Abfrage mit möglichst allen Kriterien in die Zwischenablage
- Einfügen des SQL-Codes in eine Stored Procedure
- Beheben der ersten auftretenden Syntaxfehler
- Ausprobieren, ob die Performance tatsächlich besser ist als in der Access-Abfrage
- Schrittweises Einführen von Parametern und Aufruf mit oder ohne die jeweiligen Parameter
- Wenn alle Parameter abgebildet sind, den Aufruf der gespeicherten Prozedur in der Access-Datenbank realisieren.
Anlegen der Stored Procedure
Die gespeicherte Prozedur legen wir im SQL Server Management Studio über den Kontextmenü-Befehl Neu|Gespeicherte Prozedur… des Eintrags Programmierbarkeit|Gespeicherte Prozeduren der jeweiligen Datenbank (siehe Bild 2).
Bild 2: Hinzufügen einer neuen gespeicherten Prozedur
Damit erhalten wir eine Vorlage für eine neue gespeicherte Prozedur (siehe Bild 3, hier mit bereits entfernten Kommentaren). Was wir hier sehen, ist übrigens nicht die gespeicherte Prozedur selbst, sondern der Code, mit dem wir diese anlegen.
Bild 3: Vorlage für eine neue gespeicherte Prozedur
Die gespeicherte Prozedur selbst bekommen wir nie zu sehen – wir können uns später nur den Code ausgeben lassen, mit dem wir die gespeicherte Prozedur erneut anlegen oder ändern können.
Haben wir den Code zum Erstellen der gespeicherten Prozedur einmal ausgeführt und wollen diese nun ändern, müssen wir den Befehl CREATE PROCEDURE durch ALTER PROCEDURE ändern.
Doch eins nach dem anderen. Erst einmal ändern wir den Namen der gespeicherten Prozedur, indem wir
Die Parameter löschen wir zuerst einmal, dann fügen wir statt der vorhandenen SELECT-Anweisung unsere Abfrage aus der Access-Datenbank ein. Hier erhalten wir gleich markierte Stellen mit Fehlern (siehe Bild 4).
Bild 4: Fehler im ersten Entwurf
Ich habe mich erst um den zweiten Fehler kümmern wollen, stellte aber dann fest, dass es sich nur um einen Folgefehler des ersten Fehlers handelte. Es empfiehlt sich also, Fehler in T-SQL von oben nach unten zu korrigieren, um solche Probleme zu verhindern.
Das Datum weist offensichtlich ein ungültiges Format auf. Wenn wir es in ”2024-01-01” und ”2024-02-01” ändern, verschwinden alle Fehler.
Erstmaliges Erstellen der gespeicherten Prozedur
Nun erstellen wir die Prozedur durch Betätigen der Taste F5 oder mit dem Menübefehl Abfrage|Ausführen.
Dies liefert die Meldung, dass die Befehle erfolgreich ausgeführt wurden und nach einer Aktualisierung des Punktes Gespeicherte Prozeduren im Objekt-Explorer finden wir die neue gespeicherte Prozedur dort vor.
Ausführen der gespeicherten Prozedur
Um die gespeicherte Prozedur auszuführen, können wir ein neues Abfragefenster öffnen oder wir geben den Aufruf der Abfrage einfach in das bestehende Abfragefenster ein, in dem wir die gespeicherte Prozedur erstellt haben. Der Aufruf enthält den Befehl EXEC und den Namen der auszuführenden gespeicherten Prozedur:
EXEC spGetCustomersWithSpecificProducts
Wenn wir diesen Aufruf im gleichen Abfragefenster wir die CREATE PROCEDURE anlegen, müssen wir diesen markieren und dann die Taste F5 betätigen. Wenn wir zuvor nicht markieren, werden alle Befehle im Abfragefenster ausgeführt, also auch die CREATE-Methode. Dies führt zu einem Fehler, weil wir die gespeicherte Prozedur bereits erstellt haben.
Führen wir die gespeicherte Prozedur jedoch separat aus, erhalten wir die gewünschten Datensätze (siehe Bild 5).
Bild 5: Abfrageergebnis
Anpassen der gespeicherten Prozedur
Wenn die die gespeicherte Prozedur nun anpassen wollen, können wir direkt in dem Abfragefenster bleiben, in dem wir auch den CREATE PROCEDURE-Befehl aufgerufen haben. Diesen ersetzen wir einfach durch den ALTER PROCEDURE-Befehl. Damit ändern wir die bestehende gespeicherte Prozedur wie in der Anweisung angegeben.
Wenn wir nach dem Ändern immer gleich testen wollen, wie das aktuelle Ergebnis aussieht, können wir die beiden Anweisungen auch direkt untereinander platzieren. Wir erstellen dann zuerst die neue Version der Abfrage und führen diese dann direkt aus.
Tipp: IntelliSense-Cache aktualisieren
Manchmal markiert der SQL Server den Namen einer bereits angelegten gespeicherten Prozedur oder von anderen Elementen. Dann ist möglicherweise der IntelliSense-Cache noch nicht aktualisiert. Das können wir auf zwei Arten nachholen:
- Tastenkombination Strg + Umschalt + R drücken
- Menübefehl Bearbeiten|IntelliSense|Lokalen Cache aktualisieren betätigen
Abfrage von Access aus ausführen
Um die Abfrage in der jetzigen Form auszuführen, also ohne Parameter, führen wir die folgenden Schritte aus:
- Erstellen einer neuen Abfrage in der Entwurfsansicht
- Betätigen des Ribbonbefehls Abfrageentwurf|Abfragetyp|Pass-Through
- Einfügen des Aufrufs der Abfrage, also EXEC dbo.spGetCustomersWithSpecificProducts
- Eintragen der Verbindungszeichenfolge für die Eigenschaft ODBC-Verbindung.
Die Verbindungszeichenfolge kann in der Regel einer der per ODBC eingebundenen Tabellen entnommen werden – man braucht nur hinten den Parameter TABLE zu entfernen (siehe Bild 6). In unserem Fall lautet die Verbindungszeichenfolge mit dem allgemeinen SQL Server-Treiber so:
Bild 6: Abfrageentwurf für den Zugriff auf eine gespeicherte Prozedur
ODBC;DRIVER=SQL Server;SERVER=amvdesktop2019;Trusted_Connection=Yes;DATABASE=Kundenverwaltung
Wechseln wir nun in die Datenblattansicht der Abfrage, finden wir die gewünschten Datensätze in Millisekunden wie in Bild 7 vor.
Bild 7: Ergebnis in Access in der Datenblattansicht der Abfrage
Parameter hinzufügen
Damit kommen wir zum nächsten Schritt. Bisher liefert die Abfrage die Daten für die Parameter beziehungsweise Kriterien, die wir der Beispielabfrage entnommen haben. Wir wollen die Parameter aber von Access aus ermitteln und an die gespeicherte Prozedur übermitteln, damit diese auf dem SQL Server ausgewertet werden können.
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
Testzugang
eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel
diesen und alle anderen Artikel mit dem Jahresabo