Access und eingebundene ODBC-Tabellen

Veröffentlicht: 15. Jun 2000 | Aktualisiert: 14. Jun 2004

Von Marco Gerlach

Auf dieser Seite

Diesen Artikel können Sie hier lesen dank freundlicher Unterstützung der Zeitschrift:

Microsoft Access bietet neben der eigenen Jet-Datenbank auch die Möglichkeit, es als Frontend für einen SQL-Datenbankserver einzusetzen. Dazu können die Tabellen, die sich auf dem SQL Server befinden, in die Access-MDB über den ODBC-Treiber eingebunden werden. Doch davon auszugehen, dass eine bestehende Access-Anwendung durch simples Übertragen der Daten und anschließendes Einbinden der Tabellen problemlos in eine Client/Server-Anwendung portiert werden kann, ist leider zu kurz gedacht. Wo Fallstricke lauern und welche Aspekte berücksichtigt werden müssen, soll dieser Artikel erläutern.

In Microsoft Access lassen sich Tabellen unterschiedlichster Herkunft einbinden. Neben den fest angegebenen Formaten wie Excel, Text oder dBase gibt es im entsprechenden Auswahldialog auch den Punkt "ODBC". Wird dieser aktiviert, so öffnet sich ein weiteres Fenster, in dem eine Datenquelle ausgewählt werden kann, die auf dem Rechner lokal eingerichtet wurde. ODBC-Datenquellen lassen sich für alle Server einrichten, für die ein ODBC-Treiber installiert ist. Üblicherweise handelt es sich dabei um SQL Server-Treiber, auf die sich auch dieser Artikel beschränkt.
Wenn in diesem Artikel von SQL Servern die Rede ist, sind damit relationale Datenbanksysteme verschiedenster Hersteller gemeint, also etwa auch Sybase SQL oder Oracle. Beziehen sich Aussagen direkt auf den Microsoft SQL Server, so wird das ausdrücklich erwähnt.
Beim Einbinden einer Tabelle in Microsoft Access findet eine intensive Kommunikation zwischen Access und dem ODBC-Treiber statt. Neben den Auskünften über die Tabelle, die einzubinden ist, über ihre Felder und Indizes, werden auch allgemeine Informationen über die Fähigkeiten des Treibers und des SQL Servers übermittelt. Dazu sendet Access definierte Befehle an den Treiber, woraufhin dieser dann seine Leistungsmerkmale zurückgibt. Für das Zusammenspiel von Access und dem SQL Server sind also nicht nur dessen Möglichkeiten von Bedeutung, sondern ebenso die Implementierung des Treibers.
Während der ODBC-Treiber von Microsoft den eigenen Server optimal unterstützt, existieren beispielsweise für Oracle oder IBM DB/2 neben den Microsoft Varianten zusätzliche Third-Party-Treiber, die oftmals eine deutlich bessere Integration des ODBC-Standards aufweisen.

Die Zusammenarbeit von Jet und dem SQL Server
Hat man Tabellen vom SQL Server eingebunden, so werden die meisten Abfragen wie bekannt über die Access-interne Jet-Datenbankengine abgesetzt. Die Jet-Engine entscheidet für jede Abfrage, welche Teile der Abfrage an den SQL Server weitergereicht werden können und welche Teile lokal ausgeführt werden müssen. Nur wer diesen Gedanken konsequent beachtet, kann eine performante und sinnvolle Client/Server-Anwendung erstellen.
Das Problem sei an einem Beispiel erläutert: Jet gestattet es, innerhalb von Abfragen Access Basic zu verwenden. So ist folgendes SQL-Statement erlaubt:

SELECT * FROM Kunden WHERE InStr(1,Name,'Sch')<>0

Es liefert alle Kunden, deren Name mit "Sch" beginnt, also z.B. Schulz, Schneider, Schröder usw. Was geschieht nun, wenn diese Abfrage auf einer eingebundenen Tabelle ausgeführt wird?
Der SQL Server kennt den Befehl InStr nicht. Er muss also alle Datensätze der Tabelle an Access übergeben, damit dort für jeden Satz die Gültigkeit überprüft werden kann. Das bedeutet, dass je nach Datenbestand einige hunderttausend Datensätze über das Netz gehen und dabei die EDV-Infrastruktur des Unternehmens belasten.
Eine bessere Lösung dieser Aufgabe bestände in folgender Anweisung:

SELECT * FROM Kunden WHERE Name LIKE 'SCH*'

Diese Abfrage wird von Jet in die ANSI-SQL-Syntax umgesetzt und folgendermaßen an den SQL Server übermittelt:

SELECT * FROM Kunden WHERE Name LIKE 'Sch%'

Der SQL Server filtert die entsprechenden Datensätze und liefert nur noch diese zurück.
Man muss sich vor Augen halten, dass der Flaschenhals in der Anwendungsperformance heutzutage in den seltensten Fällen beim Server oder beim Client liegt, sondern meistens das Netz den Engpass darstellt. Gerade wenn man an Filialen denkt, die über eine ISDN-Leitung an die Zentrale angeschlossen sind, wird deutlich, dass auf jedes Byte geachtet werden muss, welches über das Netz geht. (Aus diesem Grund sollte die Abfrage aus unserem Beispiel auch nicht SELECT * lauten, sondern nur genau die Felder abfragen, die tatsächlich im Zusammenhang mit der Anwendung benötigt werden.)

Protokollierung und Tuning
Woran lässt sich erkennen, welche SQL-Befehle von Access lokal ausgewertet und welche an den SQL Server gesendet werden? - Um Gewissheit zu erlangen, lässt man während eines Testlaufs ein ODBC-Protokoll laufen. Dieses kann über den ODBC-Administrator aus der Systemsteuerung aktiviert werden. Aber Vorsicht: Das Protokoll wird sehr schnell sehr umfangreich. Auch die Performance der Anwendung leidet unter der Aufzeichnung. Allerdings legt das Protokoll sämtliche Kommunikation zwischen Access und dem ODBC-Treiber offen - alle SQL-Anweisungen inbegriffen.

Im Anschluss an die Protokollierung, die eine überaus genaue Analyse erlaubt, kann man seine Abfragen ganz gezielt auf Performance trimmen, wenn man einige Grundregeln beachtet. Die wichtigste wurde bereits angesprochen: Verwenden Sie nur solche Funktionen, die der SQL Server direkt auswerten kann. So sind Berechnungen der Form SELECT Preis * Menge AS Gesamtpreis kein Problem - das InStr aus dem obigen Beispiel gilt es jedoch tunlichst zu vermeiden.

Als zweites Problem sollen LEFT JOINS angesprochen werden. Hier hängt es stark vom ODBC-Treiber und vom SQL Server ab, ob man Abfragen der Form

SELECT * FROM Kunden LEFT JOIN Rechnungen

verwenden kann, um alle Kunden und dazu die Rechnungen, wenn solche existieren, zu erhalten. Beim oben angesprochenen Einbindungsprozess befragt Access den ODBC-Treiber unter anderem, ob er die ANSI-Syntax von Outer Joins beherrscht. Da z.B. Oracle-Datenbanken diesen Standard nicht unterstützen, wird der Treiber hier also mit 'Nein' antworten. Jet muss in diesem Fall die Verknüpfung lokal vornehmen.

JET umgehen
Dass Access die SQL-Befehle über Jet analysiert und Teile davon an den SQL Server übermittelt, andere hingegen selbst ausführt, ist auf den ersten Blick sehr bequem - kann man so doch die gewohnte SQL-Syntax beibehalten, ohne dass man sich für jeden SQL Server in neue Spezifikationen einzuarbeiten braucht.
Natürlich beruht diese von Jet durchgeführte Analyse auf vorgeschriebenen Regeln und damit auf Standardfällen (allerdings auf sehr weitreichenden). Um dem Anwendungsentwickler jedoch die Möglichkeit zu geben, seine Befehle direkt an den SQL Server zu übermitteln, wurden die Pass-Through-Queries geschaffen. In diesen lassen sich SQL-Anweisungen ablegen, die direkt an den ODBC-Treiber und damit an den Server übermittelt werden. Jet verändert hier weder die Syntax noch führt es Teile der Abfrage selbst aus. Über diesen Weg lassen sich also z.B. auch mit Oracle LEFT JOINS ausführen; dazu gibt man in der Pass-Through-Abfrage die Oracle-eigene Syntax an.
Auf eine Besonderheit von Jet soll noch hingewiesen werden: UPDATE oder DELETE-Befehle, die sich auf mehrere Datensätze auf dem SQL Server beziehen, sollten immer als Pass-Through-Queries definiert werden, da ansonsten eine einfache Abfrage, die die Preise aller Artikel um 10% erhöht

UPDATE Artikel SET Preis = Preis * 1.1"

von JET SQL zerlegt und auf jeden Datensatz einzeln ausgeführt würde. Statt also einen Befehl an den SQL Server zu übermitteln, würde JET gegebenenfalls Tausende Anweisungen der Form

UPDATE ARTIKEL SET Preis = Preis *1.1 WHERE Artikelnummer = xxx

absetzen, in denen jeweils die Artikelnummer hochgezählt wird. Gleiches gilt für Lösch-Abfragen.

Datentypen von Feldern
SQL Server stellen für Felder im allgemeinen andere Datentypen zur Verfügung als Access. Beim Einbinden einer SQL-Tabelle muss Jet daher eine Zuordnung des SQL Server-Datentyps zum Access-Datentyp vornehmen. Für Strings, Memos und Ganzzahlen (Integer, Long) ist diese Zuordnung relativ einfach und sicher; größere Probleme ergeben sich hingegen bei Fließkomma- und Festkommazahlen.
Fließkommazahlen werden nach bestimmten mathematischen Modellen abgelegt. Diese können sich zwischen verschiedenen Datenbankkernen unterscheiden. Eine Fließkommazahl ist nicht nach dem herkömmlichen Sinn genau - jeder Wert stellt immer nur eine Näherung dar. So kann es vorkommen, dass etwa die Summe von 1,5 und 1,5 nicht 3,0 ergibt, sondern etwa 2,999999999. Jeder, der sich bereits mit diesem Zahlenformat auseinandergesetzt hat, kennt diese Phänomene.
In Zusammenhang mit eingebundenen Tabellen verschärfen sich jedoch die Probleme. So kann man nun nicht mehr davon ausgehen, dass die Zahl, wie wir sie in Access kennen, genau so auf dem SQL Server abgelegt ist. Die Abfrage

SELECT * FROM Artikel WHERE Preis=1,59

findet möglicherweise keinen Artikel, weil Ungenauigkeiten in den Zahlenformaten aufgetreten sind. Fazit: Gleitkommazahlen dürfen nur mit größter Vorsicht eingesetzt und nicht in Abfragen auf Gleichheit einbezogen werden!
Um die Probleme mit Gleitkommazahlen zu umgehen, wurden die Festkomma-Datentypen eingeführt. Der in Access verwendete Spezialfall ist der Währungsdatentyp. SQL Server bieten hier weitaus flexiblere Typen. So lässt sich meist jede Kombination der Anzahl von Vor- und Nachkommastellen in einem gewissen Rahmen definieren.
Da Jet nur den Währungstyp als Festkommazahl kennt, müssen die SQL Server-Typen zugeordnet werden. Dabei wählt Jet bei kleineren Zahlen ohne Komma-Werte Integer oder Long, bei Zahlen mit Nachkommastellen und bis zu 15 Vorkommastellen Double und bei allem, was darüber hinausgeht, Text der Länge 255. Handelt es sich um einem Microsoft SQL Server, so werden dort als Währung definierte Felder in Access in Currency umgesetzt. Die Sonderbehandlung für den Microsoft SQL Server ist festverdrahtet und hat damit schon so manch anderen Datenbankhersteller zur Verzweiflung getrieben.
Verdeutlichen wir die Behandlung von Festkommazahlen: Unter Microsoft SQL kann man gut mit dem Währungstyp leben. Bei jedem anderen SQL Server sind keine Zahlen einsetzbar, bei denen mehr als 15 Vorkommastellen angegeben werden. Alle Zahlen bis zu 15 Stellen werden als Double interpretiert und sind demzufolge mit den angesprochenen Rundungsungenauigkeiten behaftet.

Indexvergabe auf Tabellen
Indizes auf SQL Servern dienen wie unter Jet dazu, die Geschwindigkeit von Abfragen zu erhöhen. Bis auf eine Ausnahme können auf dem SQL Server beliebig Indizes vergeben werden. Jet erkennt sie zwar nicht und arbeitet auch nicht mit ihnen - der SQL Server wird dadurch aber optimiert. Die Ausnahme bildet der Primärschlüssel.
Access benötigt für jede Tabelle einen Primärschlüssel, um den Datensatz aktualisieren zu können. Verfügt eine Tabelle über mehrere Indizes, so kann Jet nicht erkennen, welcher davon der Primärschlüssel ist. Jet nimmt dann den ersten eindeutigen Schlüssel, den der ODBC-Treiber liefert, als Primärschlüssel an. Das geht meistens gut, aber es kann nicht schaden, wenn man den Primärschlüssel so benennt, dass sein Name alphabetisch vor den Sekundärschlüsseln kommt.
Die angesprochenen Rundungsdifferenzen von Gleitkommazahlen sind auch bei Primärschlüsseln zu beachten. Es hat sich herausgestellt, dass es höchst problematisch ist, eine solche Zahl in den Primärschlüssel aufzunehmen. Hier sollte besser ein zusätzlicher Autowert eingefügt werden, um Zugriffsprobleme zu vermeiden.

Multiuser-Unterstützung mit Timestamp-Spalten
Wird ein Datensatz einer eingebundenen Tabelle in Access geändert, so nimmt Jet die eingelesenen Daten, modifiziert den Inhalt der angesprochenen Spalte und schreibt nun den Wert zurück. Um beim Zurückschreiben Konflikte mit anderen SQL Server-Benutzern zu vermeiden, die möglicherweise in dieser Zeit den gleichen Datensatz geändert haben, muss Jet feststellen, ob der Datensatz noch in dem Zustand ist, in dem er gelesen wurde.
Trifft man keine weiteren Vorkehrungen, so überprüft Jet jede einzelne Spalte bis auf Memo-Felder durch einen ausführlichen SQL-Befehl. Das Ändern des Namens eines Kunden würde JET also in diesem Fall wie folgt durchführen, was sich über das ODBC-Protokoll nachweisen lässt:

UPDATE Kunden SET Name ='Mueller' WHERE Kundennummer='D10000' AND Name ='Müller' AND Ort ='Frankfurt' AND Anrede ='Herr' AND Plz='60437' AND Strasse ='Berner Str. 23' AND OffeneRechnungen=100.03" ...

Selbst wenn wir darüber hinwegsehen, dass diese Abfrage viel zu lang ist und eigentlich völlig überflüssig Werte abfragt, so ergeben sich zwei Fehlerpunkte: Zum einen wird nicht erkannt, wenn ein Bearbeiter ein Memo-Feld (zum Beispiel einen Kommentar zum Kunden) geändert hat, zum anderen werden Datensätze unter Umständen nicht wiedergefunden, wenn sich in ihnen ein Gleitkommawert befindet, da hier die oben angesprochenen Rundungsdifferenzen zum Tragen kommen. Für den Anwender stellt sich das dann so dar, dass er die Meldung erhält, dass der Datensatz von einem anderen Benutzer geändert sei und daher nicht aktualisiert werden kann. Eine Änderung ist folglich nicht möglich.
Bedeutet das nun, dass über Access keine Tabellen angesprochen werden können, die Gleitkommazahlen enthalten, oder - um noch einen Schritt weiterzugehen - die Festkommazahlen enthalten, die ja außer bei Microsoft auf Double uminterpretiert werden? Um es ganz direkt zu sagen: Für viele Datenbanksysteme muss die Antwort 'Ja' lauten. Nur wenn die Datenbank in Verbindung mit dem ODBC-Treiber einen Timestamp-Mechanismus zur Verfügung stellt, kann man guten Gewissens davon ausgehen, dass jeder Zugriff auf Zahlenfelder gelingen wird.
Beim Timestamp handelt es sich um eine eigene Spalte, die speziell dazu dient, Änderungen an einem Datensatz zu erkennen. Unter Sybase SQL Anywhere wird dort tatsächlich eine Zeit abgespeichert, unter Microsoft SQL Server handelt es sich um einen internen Datentyp, der von außen nicht sichtbar ist. Der Timestamp wird automatisch vom SQL Server gepflegt, wenn ein Datensatz geändert wird.
Jet fragt beim Einbinden einer Tabelle nach, ob ein Timestamp-Feld vorhanden ist. Wird dies vom ODBC-Treiber bestätigt, so wird ein UPDATE über den Primary Key und über die Timestamp-Spalte durchgeführt. Für unser Beispiel sähe das also folgendermaßen aus:

UPDATE Kunden SET Name ='Mueller' WHERE Kundennummer='D10000' AND Timestamp =xxx"

Wie man erkennt, wird das Feld OffeneRechnungen nicht mehr überprüft - eventuelle Rundungsungenauigkeiten sind also nicht mehr schädlich.
Um es noch einmal ausdrücklich zu unterstreichen: Das Timestamp-Feld sollte in keiner Tabelle fehlen! Bietet der SQL Server diese Möglichkeit nicht, so ist sein Einsatz in Zusammenarbeit mit Access nicht effizient möglich.

Fazit

Dieser Artikel sollte zeigen, dass das Einbinden von SQL Server-Tabellen in Microsoft Access nur auf den ersten Blick eine leichte Übung zu sein scheint. Um eine solche Client/Server-Anwendung sicher und performant zu machen, sind einige Vorüberlegungen und Vorkehrungen zu treffen. Es zeigt sich, dass die Wahl des SQL-Servers nicht nur nach der Leistungsfähigkeit des Datenbankkerns erfolgen darf, sondern dass auch dem Zusammenspiel mit Microsoft Access eine entscheidende Bedeutung zukommt. Hier spielen vor allem die Währungsformate und die Verfügbarkeit des Timestamp-Mechanismus eine wesentliche Rolle. Naturgemäß hat der Microsoft SQL Server hier seine Stärken. Bei jedem anderen Produkt sind intensive Voruntersuchungen vonnöten.

Für Fragen und Anregungen erreichen Sie Marco Gerlach unter MGerlach@Gerlach-Online.de.