Access, MySQL und Datenzugriff

Sie haben Ihr Access-Backend auf einen MySQL-Server migriert, die notwendigen Anpassungen vorgenommen und die Tabellen über ODBC in Ihr Frontend verknüpft. Mit Unterstützung der beiden vorausgegangenen Beiträge dieser Serie (Shortlink 544 und 588) sollte das gelungen sein. Schauen wir nun, was es mit dem Zugriff auf die Tabellen auf sich hat und was es dabei zu beachten gilt.

Ein Hemmschuh, wenn es an die Migration einer Access-Datenbank mit File-Backend auf einen SQL-Server gehen soll, ist der vermeintliche Aufwand, der durch Anpassungen des Frontends zu erwarten ist. Hier erwartet man größere Umbauarbeiten am VBA-Code oder etwa spezielle Zugriffsroutinen in einem ungewohnten SQL-Dialekt.

Wie die beiden vorherigen Beiträge dieser Reihe gezeigt haben, ist es aber kein Hexenwerk, die Tabellen auf den Server zu kopieren und dann per ODBC-Datenquelle in das Frontend zu verlinken. Tatsächlich ist damit bereits der wesentliche Teil einer Migration erledigt. Im Normalfall sollte die Datenbank dann genau so funktionieren, wie vorher mit dem File-Backend – irgendwelche änderungen am Code oder an Formularen oder Berichten sind nicht erforderlich.

ODBC-verknüpfte Tabellen

Nun hört man gelegentlich Unkenrufe, dass mit verlinkten SQL-Servertabellen noch kein nennenswerter Performancegewinn zu erwarten sei, weil Access die Daten dann genauso en bloc auf die Client-Seite holen muss, um sie daraufhin lokal zu verarbeiten.

Selbst wenn dies zuträfe, würde durch die wegfallenden Sperrmechanismen, die einen erheblichen negativen Einfluss auf die Performance in Mehrbenutzerumgebung bei Bindung an ein File-Backend haben, die Zugriffsgeschwindigkeit deutlich steigen.

Aber die Annahme ist ohnehin völlig falsch: Access holt sich im Normalfall nur das Ergebnis einer Abfrage oder Indizes vom Server ab, nicht den kompletten Inhalt einer Tabelle. Dabei spielt es keine Rolle, ob eine Tabelle in der Datenblattansicht geöffnet oder eine Abfrage ausgeführt wird. In beiden Fällen wird JET ein SELECT-Statement an den Server absetzen.

Es ist dabei intelligent genug, nicht den ganzen Inhalt zu holen, sondern nur den Teil, der wirklich erforderlich ist. Der Nachweis dafür kann mit verschiedenen Analysemethoden erbracht werden, die Sie später unter Zugriff loggen kennenlernen werden.

An dieser Stelle soll an einem Beispiel schon mal vorgegriffen werden, was abläuft, wenn Sie ein an die Tabelle tblKunden gebundenes Formular (frmKunden2) der Beispieldatenbank öffnen.

JET fragt dann im ersten Schritt zunächst einmal ausschließlich nach den Primärschlüsseln der Tabelle:

SELECT 'tblkunden'.'KundeID' FROM 'tblkunden'

Als Ergebnis ermittelt es eine 1 für die ID des ersten Kundendatensatzes. Bis hierhin beschränkt sich also der Austausch mit dem Server auf wenige Bytes für die Indizes der Tabelle. Nach Kenntnis des ersten Primärschlüsselwerts fragt es die restlichen Felder des zur ID passenden Datensatzes ab:

SELECT 'KundeID', 'KundenCode', 'Firma',
'Kontaktperson', 'Position', 'Strasse', 'Ort', 'Region', 'PLZ', 'Land', 'Telefon', 'Telefax', 'TS' FROM 'tblkunden'
WHERE 'KundeID' = 1

Und mit dem Ergebnis dieser Abfrage füllt es dann auch schon die Felder des Formulars. Der Umfang des gesamten Traffics für diesen Vorgang dürfte nicht viel mehr als einige Hundert Bytes betragen. Erst dann, wenn Sie auf den nächsten Datensatz schalten, werden zusätzliche Daten geholt. Dabei denkt sich JET wohl: “Okay, da sollen wahrscheinlich noch mehr Datensätze angezeigt werden …”, und schickt dem Server eine Anfrage für gleich zehn weitere Datensätze:

SELECT 'KundeID', 'KundenCode', 'Firma',
'Kontaktperson', 'Position', 'Strasse', 'Ort', 'Region', 'PLZ', 'Land', 'Telefon', 'Telefax', 'TS' FROM 'tblkunden'
WHERE 'KundeID' = 2 OR 'KundeID' = 3 OR 'KundeID' = 4 OR 'KundeID' = 5 OR 'KundeID' = 6 OR 'KundeID' = 7 OR 'KundeID' = 8 OR 'KundeID' = 9 OR 'KundeID' = 10 OR 'KundeID' = 11

Übrigens geschieht dasselbe auch, wenn Sie gar nichts tun und das Formular auf dem ersten Datensatz stehen lassen: Nach etwa einer Minute holt JET sich automatisch zehn neue Datensätze im Hintergrund, weil es offenbar gerade nichts Besseres zu tun hat.

Das Spiel wiederholt sich regelmäßig, bis alle Kundendatensätze in den Cache der Client-Seite gezogen wurden, was im Beispielformular (90 Kundendatensätze) nach etwa neun Minuten der Fall ist. Wenn Sie dann durch alle Datensätze des Formulars steppen, muss JET gar keine Datensätze mehr beim Server anfragen, weil sie sich bereits im lokalen Cache befinden.

Öffnen Sie die Tabelle tblKunden in der Datenblattansicht, dann reicht ein Datensatz zur initialen Anzeige natürlich nicht aus. In diesem Fall fragt JET genau so viele Datensätze beim Server ab, wie zur Anzeige auf dem Bildschirm benötigt werden – nicht mehr und nicht weniger.

Erst ein Scrollen nach unten holt tatsächlich weitere Daten. Dabei ergehen die Anfragen immer in Zehnerblöcken, wie im oben dargestellten SQL-Statement, über OR-verkettete Kriterien.

Man sieht hier also, dass JET durchaus intelligent und resourcenschonend mit dem SQL-Server umgeht. ähnlich geht es auch bei Abfragen vor.

JET versucht hier, einen möglichst großen Teil des SQL-Ausdrucks direkt an den Server zu schicken, wobei es das Statement teilweise umformuliert.

Eine Abfrage der Artikel der Tabelle tblArtikel könnte etwa so aussehen:

SELECT "ART." & [Artikelname] AS Artikel,
tblartikel.ArtikelID
FROM tblartikel
WHERE (((tblartikel.ArtikelID)>20))
ORDER BY "ART." & [Artikelname];

JET macht daraus zwei Schritte:

SELECT 'Artikelname', 'tblartikel'. 'ArtikelID'
FROM 'tblartikel' WHERE ('ArtikelID' > 20 )

Abgefragt wird hier also nicht nur der Primärschlüssel ArtikelID, sondern auch das Textfeld Artikelname. Der Grund ist die Sortierung nach dem Namen, die auf dieses Feld angewandt werden soll.

Das erledigt JET erst anschließend und teilt sich die Arbeit in diesem Fall mit dem Server, obwohl auch dieser durchaus sortieren könnte. Dass die ORDER-Anweisung nicht mit an den Server geschickt wird, liegt an der VBA-Verknüpfung des Feldinhalts mit dem gewählten Präfix ART:. Das Einschränkungskriterium ArtikelID>20 hingegen darf der Server selbst verarbeiten.

Im zweiten Schritt werden die ersten zehn Datensätze der nun von JET sortierten Artikel-IDs abgefragt:

SELECT 'ArtikelID','Artikelname','TS' FROM 'tblartikel'
WHERE 'ArtikelID' = 40 OR 'ArtikelID' = 60 OR 'ArtikelID' = 39 OR 'ArtikelID' = 48 OR
'ArtikelID' = 38 OR 'ArtikelID' = 58 OR
'ArtikelID' = 52 OR 'ArtikelID' = 71 OR
'ArtikelID' = 33 OR 'ArtikelID' = 56

Damit können die ersten zehn Zeilen des Datenblatts gefüllt werden. Für die darauf folgenden Zeilen läuft dann ein analoges Prozedere ab.

Ein Beispiel für eine Abfrage, die auf mehreren verknüpften Tabellen basiert, soll hier aus Platzgründen außen vor bleiben. Es sei aber verraten, dass JET auch dann den SQL-Ausdruck weitgehend an den Server übergibt und ihm das Joinen überlässt, statt selbst tätig zu werden.

Pass-Through-Abfragen

Es lässt sich also ableiten, dass allein verknüpfte ODBC-Tabellen und Abfragen auf diese einen erheblichen Performance-Zugewinn bringen.

Das letzte Beispiel zeigt aber auch, dass JET immer dann einschreiten muss, wenn der SQL-Ausdruck eine Syntax aufweist, die der MySQL-Server so nicht verstehen würde. Das kommt in erster Linie dann zum Tragen, wenn VBA-Funktionen im SQL-String zum Einsatz kommen.

Auch das Verknüpfungszeichen & versteht MySQL nicht als String-Verknüpfungsoperator, weshalb JET diesen Part übernehmen muss. Die besprochene Abfrage hätte unter MySQL nämlich diese Syntax:

SELECT CONCAT('ART.',Artikelname) AS Artikel,
tblartikel.ArtikelID
FROM tblartikel
WHERE tblartikel.ArtikelID>20
ORDER BY CONCAT('ART.',Artikelname);

Für die String-Verknüpfung gibt es hier eine eigene Funktion CONCAT, die eine beliebige Anzahl durch Kommata getrennter Strings als Parameter erwartet. Die Ausführung dieser Abfrage verläuft nochmal um Größenordnungen schneller als die vorherige Lösung.

Diese SQL-Anweisung können Sie aber nicht einfach in die SQL-Ansicht des Abfrageeditors eingeben. Access würde versuchen, sie als JET-Ausdruck zu parsen, und einen Fehler wegen unbekannter Funktion CONCAT melden.

Es muss Access schon ausdrücklich gesagt werden, dass es den Ausdruck unbesehen an den Server senden soll, um dann auf das Ergebnis zu warten. Sie erzwingen das, indem der Typ der Abfrage auf Pass-Through gestellt wird. Öffnen Sie dazu eine neue Abfrage, fügen keine Tabellen hinzu und betätigen den Menüpunkt Abfrage|SQL spezifisch|Pass-Through. In diesem Modus ist dann keine visuelle Gestaltung mehr möglich, sondern ausschließlich die Eingabe von SQL-Statements. Damit Access weiß, wohin es dieses Statement unter Umgehung der verknüpften Tabellen senden soll, muss außerdem eine ODBC-Verbindungszeichenfolge angegeben werden, die analog zum Connect-String der Tabellen lautet und etwa so aussehen könnte:

ODBC;Driver={MySQL ODBC 3.51 Driver};DATABASE=suedsturm;
SERVER=localhost ;PORT=3306; UID=;PWD=;OPTION=43

Die Eigenschaft Liefert Datensätze muss auf Ja stehen, sonst geschieht gar nichts (siehe Bild 1). Ein Nein ist hier nur angebracht, wenn es sich beim SQL-Ausdruck um eine Aktionsabfrage handelt.

Bild 1: Pass-Through-Abfrage und ODBC-Verbindungsparameter

Solche Pass-Through-Abfragen sind sehr schnell, haben aber drei Haken. Einmal nötigen sie Sie dazu, sich mit der speziellen MySQL-Syntax zu beschäftigen. Im MySQL-Handbuch [3] finden Sie diese erschöpfend beschrieben.

Der Sprachumfang ist nicht so groß, wie etwa beim T-SQL des Microsoft-SQL-Servers, verlangt aber doch einige Wochen Beschäftigung mit der Materie, wenn er beherrscht werden will. Sie brauchen sich das aber nicht auf einen Schlag anzueignen, sondern kommen mit gelegentlichem Suchen in der Hilfe und Experimenten ebenfalls nach und nach zum Ziel.

Ein anderer Punkt ist, dass Sie mit Pass-Through-Abfragen die Portierbarkeit der Datenbank zunichte machen. Während Sie bei einem Frontend mit ausschließlich verknüpften Tabellen ohne Weiteres etwa auf Oracle umstellen können, indem die Tabellen dorthin gezogen werden, steht bei Pass-Through-Abfragen immer erst eine Umstellung dieser Abfrage auf die spezifische Server-Syntax an.

Username or Email

Passwort

Angemeldet bleiben