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.
Und schließlich haben Pass-Through-Abfragen noch ein gewichtiges Manko: Sie sind lediglich zum Lesen von Daten gedacht.
Das Ergebnis der Abfrage kann nicht editiert werden, auch wenn eine analoge JET-Abfrage aktualisierbar wäre. Sie können ein Formular zwar auf Grundlage einer PT-Abfrage erstellen, die Datensätze werden aber schreibgeschützt sein.
Datenzugriff per ADO
Man könnte alternativ auf die Idee kommen, auf eine ODBC-Tabelle ein ADO-Recordset zu öffnen und dieses einem Formular als Datenquelle zuzuweisen. Allerdings nur dann, wenn ein client-seitiger Cursor verwendet wird (CursorLocation-Eigenschaft), denn ein ADO-Recordset auf Basis einer Connection mit serverseitigem Cursor wird nicht als Formular-Recordset akzeptiert.
Als Provider ist dabei der Microsoft OLE DB-Provider for ODBC Drivers zu benutzen und der ConnectionString sieht ähnlich aus, wie der für Tabellen und Pass-Through-Abfragen:
Dim oCon As New ADODB.Connection
Dim rst As New ADODB.Recordset
oCon.Provider = "MSDASQL.1" 'ODBC-Provider
oCon.ConnectionString = _
& "Extended Properties=" _
& "'Driver={MySQL ODBC 3.51 Driver};" _
& "DATABASE=suedsturm;SERVER=localhost;" _
& "PORT=4040;" _
& "UID=root;PWD=adminpasswort;OPTION=43'"
oCon.CursorLocation = adUseClient
oCon.Open
rst.Open "SELECT * FROM tblkunden", oCon, _
adOpenDynamic, adLockOptimistic
Set Me.Recordset = rst
Bei der Inspektion der Anweisungen, die dann als Anfragen beim Server ankommen, wenn durch die Datensätze des Formulars gesteppt wird, tritt allerdings Ernüchterung ein: Für jeden Datensatz wird wiederholt das Statement SELECT * FROM tblkunden abgesetzt. Das ist performancetechnisch natürlich außerordentlich suboptimal. Da ist die Methode mit Bindung des Formulars an eine verknüpfte Tabelle schon wesentlich effizienter. Ein Code wie in Listing 1 sollte also nicht zur Anwendung kommen.
Aber auch abseits von Formularen ist ein ADO-Recordset nicht die erste Wahl, wenn es um den Datenzugriff auf einen MySQL-Server geht. Ohne hier ins Detail zu gehen, lässt sich sagen, dass der Zugriff dabei intern recht umständlich vonstatten geht. Das gilt für lesende wie schreibende Zugriffe gleichermaßen. Offenbar hat Microsoft ADO nur für Zugriffe auf den MS SQL Server und dessen Provider optimiert, was etwa bei Access-Projekten (ADP) zum Tragen kommt.
Für ODBC-Verbindungen bringt ADO eher Nachteile und sollte vermieden werden, falls nicht ausgewählte Gründe dafür sprechen.
Datenzugriff per DAO
Wenn Operationen auf die Daten über DAO-Recordsets auf die eingebundenen Tabellen ausgeführt werden, dann greifen weitgehend die gleichen optimierenden Mechanismen, wie bei gebundenen Formularen. Es kann daher identischer Code eingesetzt werden, wie bei einem Access-Backend. Anpassungen sind dabei nicht erforderlich.
Es gelten allerdings auch die gleichen Beschränkungen für SQL-Statements, wie für normale Abfragen: Sobald etwa VBA-Ausdrücke enthalten sind, dann wird JET genötigt, teilweise selbst Hand anzulegen und Berechnungen durchzuführen.
Umgehen können Sie das, indem ähnlich wie bei den Pass-Through-Abfragen direkt die Syntax des SQL-Servers verwendet wird. Wie aber kann man DAO sagen, dass es ein SQL-Statement nicht parsen, sondern direkt abschicken soll
Mit einem Database-Objekt und einem Recordset geht das nämlich nicht, weil ein Database-Objekt im Kontext der verknüpften Tabellen steht, die ja gerade umgangen werden sollen.
Für diesen Zweck gibt es in DAO statt einem Database-Objekt ein Connection-Objekt. Operationen über eine solche DAO.Connection laufen in den ominösen ODBCDirect-Arbeitsbereichen ab, die leider so selten in freier Wildbahn anzutreffen sind, dass Microsoft sich offenbar veranlasst sah, sie in Access 2007 nicht mehr zu unterstützen. Das ist meiner Meinung nach ein wichtiger Punkt, der leider gegen den Einsatz von Access 2007 bei ODBC-Anbindung spricht.
Ein ODBCDirect-Arbeitsbereich wird über ein Workspace-Objekt erstellt, dem ausdrücklich die Verwendung von ODBC über die Optionskonstante dbUseODBC mitgeteilt wird:
Dim wrk As Workspace
Set wrk = DBEngine.CreateWorkspace("", "", "", _
& dbUseODBC)
Auf dieses Workspace kann nun eine ODBC-Connection geöffnet werden:
Dim oCon As DAO.Connection
Dim strConnect As String
strConnect = "<ODBC-Verbindungszeichenfolge>"
Set oCon = wrk.OpenConnection("", , , strConnect)
Die Verbindungszeichenfolge ist wieder die gleiche, die auch zum Verknüpfen der Server-Tabellen verwendet wird. Das Connection-Objekt kennt ähnliche Methoden wie ein Database-Objekt, also etwa auch solche zum Öffnen einer Datensatzgruppe:
Dim rst As DAO.Recordset Set rst = oCon.OpenRecordset( _ "SELECT * FROM tblKunden WHERE " _ & "Kundencode='ALFKI'", _ dbOpenDynamic) Debug.Print rst!Kontaktperson
Was aber so ein Recordset von einem üblichen Dynaset unterscheidet: Es ist wieder nicht aktualisierbar, also schreibgeschützt, und eignet sich daher nur für lesenden Zugriff.
Methoden wie .AddNew oder .Edit werden demnach entsprechende Fehlermeldungen nach sich ziehen. Der Beweis dafür ist, dass die Eigenschaft rst.Updatable den Wert False zurückliefert.
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
den kompletten Artikel im PDF-Format mit Beispieldatenbank
diesen und alle anderen Artikel mit dem Jahresabo