Language: Deutsch English















Last Update: 2017 - 03 - 20





Vorwort

Diesen Artikel habe ich kürzlich in meinen Archiven entdeckt. Er stammt bereits aus dem Jahr 2002 und war eigentlich für die 5. Ausgabe des Access Report vorgesehen, aber leider hat Karl den Access Report mit der 4. Ausgabe bis auf Weiteres eingestellt. (Siehe http://www.donkarl.com/Exkurse/Exkurse_old/Exkurs9_AREnde.htm)

Da ich es zu schade finde, den Artikel auf meiner Festplatte verschimmeln zu lassen, habe ich ihn noch ein wenig überarbeitet und stelle ihr jetzt hier online dem geneigten Leser zur Verfügung.

Der Artikel endet leider etwas abrupt, denn ursprünglich sollte mindestens noch ein weiterer Artikel zum Thema Access und Oracle folgen, der die konkrete Programmeriung eines Access-Frontends für eine Oracle-DB detaillierter behandelt, aber wegen der oben erwähnten Hintergründe ist es dazu bis heute noch nicht gekommen.

Ein Rendezvous mit dem Oracle - (Teil I)

In diesem Artikel möchte ich die elementaren Schritte zeigen, um eine effketive Client-Server-Anwendung mit MS Access und Oracle zu erstellen. Da der AR ein Access-Magazin ist, werde ich versuchen, die Oracle-spezifischen Themen auf ein Minimum zu reduzieren, so dass die beschriebenen Abläufe und Denkansätze zumindest grob auch auf die Zusammenarbeit von Access mit anderen Server-DBMS übertragbar sind.

Die Umsetzung der Abläufe in diesem Artikel habe ich mit Oracle 8.1.5 Enterprise Edition und Access XP SR1 auf Windows XP Professional vorgenommen.

Da Datenmodellierung im Allgemeinen und die Erstellung von Oracle-Datenbanken im speziellen nicht Thema dieses Artikel sein soll, verwende ich als Datenbankschema für dieses Sample einfach das bei jeder Oracle-Installation mitgelieferte SCOTT-Schema. Falls jemand direkt die Beispiele nachvollziehen möchte und dieses Schema nicht in der verwendeten Oracle-Instanz vorhanden ist, kann man das Schema einfach über das Script ORACLE_HOMERDBMSADMINSCOTT.sql erstellen.

ODBC-DSN für Oracle-Zugriff einrichten

Wenn man nicht sämtliche Datenzugriffe händisch ausprogrammieren möchte, wozu ich dann eher zu VB oder Delphi als Entwicklungsumgebung raten würde, bleibt für den Zugriff von Access auf Oracle-Daten nur der Weg, die Tabellen über ODBC einzubinden. Unabhängig davon, ob man den Oracle-ODBC-Treiber von Microsoft oder den von Oracle verwendett, erfordert dies die Installation des Oracle-Clients auf dem Rechner, auf dem die Access-Anwendung laufen soll.

Um von Access auf unsere Oracle-Daten zugreifen zu könenn, erstellen wir als erstes eine neue ODBC-Datenquelle mit dem "ODBC Data Source Administrator". Dieser ist in der Systemsteuerung (bei Win2K & XP per Default im Unterverzeichnis "Administrative Tools") zu finden. Ich erstelle dazu eine USER-DSN, die, im Gegensatz zu einer System-DSN, nur den aktuellen OS-Benutzer an diesem Rechner zur Verfügung steht. Ich verwende ausschließlich den ODBC-Treiber von Oracle (dieser ist besonders für eingebundene Tabellen in Access wesentlich performanter), dort reicht es aus den Service-Name der Oracle-Instanz (bei mir ist das "ORADB" ) und den Benutzernamen einzutragen und einen Namen für die Datenquelle zu vergeben (Ich trage hier SCOTT_ORADB ein). Bei allen weiteren Einstellungen kann man i.d.R. einfach die Default-Werte übernehmen.

Nun erstelle ich eine neue, leere Access-MDB, die unser Frontend für die Anwendung wird.Über die gerade erstellete ODBC-Datenquelle verknüpfe ich nun die Tabellen des Oracle-Schemas in die Anwendung. Dabei tritt das erste Problem auf. Die Tabellen BONUS und SALGRADE haben keinen Primärschlüssel, daher müssen wir jeweils selbst bestimmen, welche Spalte der Primary Key dieser Tabellen ist. Ich wähle nach eigenem Ermessen für BONUS die Spalte ENAME und für SALGADE die Spalte GRADE. Aber vorsicht, die Wahl einer Spalte, die keine eindeutigen Werte enthält, als Primärschlüssel der Tabelle in Access kann zur Folge haben, dass in Access in machen Datensätze völlig falsche Daten angezeigt werden. Die Tabellennamen werden in Access in dem Format SCHEMA_TABELLE angezeigt. Das ist ein wenig unschön, daher benenne ich alle Tabellen in Access einfach um und entferne den Schema-Name, so dass wird in Access die vier Tabellen BONUS, DEPT, EMP, und SALGRADE haben. Dieses Umbenennen wirkt sich nur auf die Tabellennamen in Access aus, aber nicht auf die tatsächlichen Tabellen in Oracle.

Bevor wir richtig loslegen können, sollte wir präventiv noch ein unangenehmes Problem beheben. Die Jet-Engine hat einen vorderfinierten Connection-Timeout für ODBC-Verbindungen. Wenn die Anwendung für den Zeitraum der als Connection-Timeout definiert wurde inaktiv ist, schließt die Jet-Engine automatisch die ODBC-Verbindung. Eigentlich ein sinnvolles Feature, leider können verschiede Fehlersituationen, bis zum Komplettabsturz von Access, entstehen, wenn die Verbindung nach einer solchen Ruhephase wieder aufgebaut werden soll. Einziger mir bisher bekannter Workaround für dieses Problem ist es, den Connection-Timeout der Jet-Engine für ODBC-Verbindungen komplett zu deaktivieren. Das erreicht man, indem man in der Registry den Wert für den ConnectionTimeout auf 0 setzt. Zu finden ist dieser Wert im Schlüssel HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesODBC.

NLS_LANG-Einstellung in der Registry editieren

Für den Sonderfall Oracle 8i und AccessXP gibt es endlich den Artikel Q282349 in der Microsoft Knowledgebase, der das Prolem und den o.g. Workaround beschreibt. Das Problem existiert aber mindestens seit Access 97 und ist keineswegs auf ODBC-Verknüpfungen zu Oracle beschränkt, sondern tritt bei anderen Server-DBMS genauso auf.

Wo wir gerade den Registryeditor geöffent haben, sollten wir uns gleich noch einem anderen Problem widmen. Oft gibt es Probleme in Access, wenn es um die Darstellung von Kommazahlen geht. Wenn der Oracle-Client für ein deutsches System konfiguriert ist, wird das Dezimaltrennzeichen über die ODBC-Schnittstelle nicht so übergeben, wie Access das erwartet. Um dieses Problem gleich zu umgehen navigieren wir jetzt in der Registry zu dem Schlüssel HKEY_LOCAL_MACHINESOFTWAREORACLEHOME0 (der Text "HOME0" steht hier für das aktuelle Oracle-Home und kann, je nach Installation auch anders heißen) und ändern dort den Wert für den Eintrag "NLS_LANG" auf "GERMAN_AMERICA.WE8ISO8859P1".

So, nun zurück zu Access. Die Tabellen für unsere Mini-Anwendung sind jetzt vorhanden und die vorhersehbaren Probleme aus dem Weg geräumt, nun könenn wir daran gehen, ein paar Abfragen zu erstellen. Das Naheliegendste erscheint mir erstmal eine Abfrage zu erstellen, die alle Employees (Tabelle EMP) mit dem Namen und Ort ihrer Abteilungen (Tabelle DEPT) zeigt. Die Abfrage ist in dem Access-Query-Editor schenll zusammengeklickt und hat folgendes SQL-Statement:


SELECT        EMP.*,
DEPT.DNAME, 
DEPT.LOC
FROM         DEPT 
 INNER JOIN EMP 
  ON DEPT.DEPTNO = EMP.DEPTNO;

Diese Abfrage speichere ich in Access unter dem Namen "qryEMP_DEPT"

Nicht ganz so naheliegend aber evtl. Für einen potentiellen Anwender ebenfalls interessant, könnte es sein, zu sehen, in welcher Gehaltsgruppe ein Mitarbeiter ist, wer sein Vorgesetzter ist und welcher Gehaltgruppe dieser zugeordnet ist. Diese Frage beantwortet das folgende SQL-Satement.

SELECT        e1.ENAME AS Mitarbeiter, 
sEMP.GRADE AS GehaltsgruppeMA, 
e2.ENAME AS Vorgesetzter, 
sMGR.GRADE AS GehaltsgruppeVorges
FROM (EMP AS e1 
 LEFT JOIN SALGRADE AS sEMP 
  ON        e1.SAL <= sEMP.HISAL 
  AND        e1.SAL >= sEMP.LOSAL) 
 LEFT JOIN (EMP AS e2 
   LEFT JOIN SALGRADE AS sMGR 
     ON        e2.SAL <= sMGR.HISAL 
         AND        e2.SAL >= sMGR.LOSAL) 
ON e1.MGR = e2.EMPNO;

Das Statement speichere ich als "qryGehaltsgruppe_EMP_MGR"

Für eine ernsthafte Anwendung würden wir zwar sicherlich noch weitere Abfragen benötigen , aber diese beiden reichen aus, um sich mal einem weit verbreitete Mythos zu widmen.

Tracing im ODBC Data Source Administrator aktivieren Oft wird behauptet, bei Tabellen, die über ODBC nach Access verknüpft sind, werden alle Datensaätze aus den Tabellen von Access abgerufen und dann clientseitig die Ergebnismenge ermittelt. Es ist eigentlich ganz einfach zu überprüfen, ob diese Aussage der Wahrheit entspricht. Dazu starte ich erneut den "ODBC Data Source Administrator", den wir schon beim Anlegen der Tabellen verwendet haben. Im Register "Tracing" kann man eine Protokoll-Funktion aktivieren, die sämtliche Kommunkation über ODBC in einer Datei protokolliert. Ich trage einen Namen für die Protokoll-Datei ein und starte die Protokoll-Funktion.

Jetzt versuche ich, mit aktivierten Tracing des "ODBC-Data Source Admin", folgendes SQL-Statement auf meine gespeicherten Abfragen in Access auszuführen.


SELECT        qryEMP_DEPT.*
FROM                 qryEMP_DEPT
WHERE         qryEMP_DEPT.LOC = 'DALLAS';

Die Ausführung der Abfrage dauert deutlich länger als vorher, da sehr umfangreiche Informationen in unsere Protokolldatei geschrieben werden. Allein für diese einzelne SQL-Statement werden ca. 50 KB an Log-Einträgen erzeugt. Nachdem die Abfrage beendet ist, schaue ich mir den Inhalt der Protokoll-Datei an. Die ersten Zeilen sehen wie folgt aus:

Scott_Oradb                e70-a34        ENTER SQLAllocEnv 
                        HENV *              0013DB54

Scott_Oradb                e70-a34        EXIT  SQLAllocEnv  with return code 0 (SQL_SUCCESS)
                        HENV *              0x0013DB54 ( 0x098a1540)

Hier wurde jeder Aufruf einer der ODBC-API-Funktion inklusiver der Parameter und Rückgabewerte protokolliert, ganz Links steht jeweils der Name der ODBC-Datenquelle. Solange man nicht irgenwelchen tückischen Bugs auf der Spur ist, oder einen selbstgeschriebenen ODBC-Treiber testet, sind diese Ausgaben uninteressant.

Ich suche jetzt in dem Logfile, ob ich dort SQL-Statements finde, die zu meiner Access-Abfrage passen. Hier haben ich doch schon das was ich suche:

Scott_Oradb                e70-a34        EXIT  SQLExecDirectW  with return code 0 (SQL_SUCCESS)
                        HSTMT               098A2858
                        WCHAR *             0x0C302250 [      -3] "SELECT "SCOTT1".
"DEPT"."DEPTNO","SCOTT1"."EMP"."EMPNO" FROM "SCOTT1"."EMP",
"SCOTT1"."DEPT" WHERE (("SCOTT1"."DEPT"."DEPTNO" = "SCOTT1".
"EMP"."DEPTNO" ) AND ("SCOTT1"."DEPT"."LOC" = 'DALLAS' ) )  0"

Anhand dieses Log-Eintrags ist klar zu sehen, dass unser SQL-Statement mit der Where-Condition über ODBC an den Oracle-Server weitergegeben wurde. Wenn dieses Statement auf dem Oracle-Server ausgeführt wird, werden nur die wenigen Datensätze an Access zurückgeliefert, die tatsächlich den Abfragekriterien entsprechen. Die pauschale Behauptung, Access würde alle Daten vom Server abrufen und selbst die Auswertung der Abfragen übernehmen ist also zumindest in diesem Fall nicht richtig.

Wir haben aber noch eine weitere, komplexere Abfrage in Access erstellt. Ich möchte jetzt wissen in Welcher Gehaltsgruppe der Mitarbeiter "Blake" und sein Vorgeseter ist. Also formuliere ich in Access folgendes SQL-Statement:

SELECT        qryGehaltsgruppe_EMP_MGR.*
FROM                qryGehaltsgruppe_EMP_MGR
WHERE                qryGehaltsgruppe_EMP_MGR.Mitarbeiter='BLAKE'; 

Die ODBC-Trace-Funkiton ist nach wie vor aktiviert, also brauche ich jetzt nur noch in der Log-Datei nach Einträgen zu suchen, die zu diesem Statement passen. Hier gibt es jetzt etwas mehr zu finden, als bei der vorigen Abfrage:

Scott_Oradb     d00-b14        EXIT  SQLExecDirectW  with return code 0 (SQL_SUCCESS)
                HSTMT               099A2858
                WCHAR *             0x0E8029A8 [      -3] "SELECT "LOSAL" 
,"HISAL" ,"sMGR"."GRADE" FROM "SCOTT1"."SALGRADE" "sMGR"  0"
                SDWORD                    -3

Scott_Oradb     d00-b14        EXIT  SQLExecDirectW  with return code 0 (SQL_SUCCESS)
                HSTMT               099A2B70
                WCHAR *             0x0E803208 [      -3] "SELECT "LOSAL" 
,"HISAL" ,"sEMP"."GRADE" FROM "SCOTT1"."SALGRADE" "sEMP"  0"

Scott_Oradb     d00-b14        EXIT  SQLExecDirectW  with return code 0 (SQL_SUCCESS)
                HSTMT               099A2E88
                WCHAR *             0x0E8022C0 [      -3] "SELECT "e1"."SAL",
"e1"."EMPNO","e2"."SAL","e2"."EMPNO" FROM {oj "SCOTT1"."EMP" "e1" 
LEFT OUTER JOIN "SCOTT1"."EMP" "e2" ON ("e1"."MGR" = "e2"."EMPNO" ) } 
WHERE ("e1"."ENAME" = 'BLAKE' )  0"

Das Ergebnis ist nun weniger erfreulich, als bei der vorigen Abfrage. Zwar wird der Self-Join der EMP-Tabelle inklusive des Kriteriums an den Server durchgereicht, aber die beiden etwas komplexeren Outer-Joins auf die SALGRADE-Tabelle konnte Access bzw. der ODBC-Treiber nicht sinnvoll übersetzen, daher werden dafür zweimal alle Datensätze dieser Tabelle zum Client abgerufen. Bei unseren wenigen Beispieldatensätze entsteht dadurch kein spürbarer Unterschied, aber in echten Anwendung mit großen Datenvolumen wird durch solch ungünstig übersetze SQL-Statements eine erhebliche Netzlast erzeugt und der Server durch massenhaft IO unnötig belastet. - Die Perfomance leidet natürlich massiv unter diesen ineffektiven Prozessen.

Ein einfacher Workaround, mit dem sich die Kommunikation zwischen Client und Server effektiver gestalten läßt und diese Problematik umgangen wird, existiert nicht. Es gibt zwei Wege um hier die Ausführung des Statements auf dem Server zu erzwingen. Zum einem könnte man eine Pass-Through-Abfrage verwenden. Diese müsste allerdings den vollständigen SQL-String, inkl. des Kriteriums für den jeweils gesuchten Mitarbeiter enthalten und bedeutet somit etwas höhenren Entwicklungsaufwand. Außerdem, und das ist in machen Fällen ein Auschlußkriterium, Pass-Through-Abfragen sind grundsätzlich nicht aktualisierbar.

Die aus meiner Sicht effektivere Lösung dieses Problem ist es, eine View mit dem gleichen SQL-Statement auf dem Oracle-Server zu erstellen, die unsere Access-Abfrage "qryGehaltsgruppe_EMP_MGR" ersetzt. Anstelle der Abfrage aus Access heraus, binden wir dann diese View in Access ein, wie eine Tabelle. Damit liegt die Auswertung des SQL-Statements der View ausschließlich in den Händen des Oracle-Servers und der Zugriff von Access aus auf diese View erfolgt wie auf eine Tabelle. Damit haben wir sichergestellt, dass die Auswertung der Abfrage ausschließlich auf dem Server erfolgt.

Soweit so gut. Wenn ihr die Abläufe in diesem Artikel bis hierhin nachvollzogen habt, habt ihr jetzt die grundlegende Basis um eine effektive Client-Server-Anwendung mit Accesss und Oracle zu erstellen.

Share this article: Share on Facebook Tweet Share on LinkedIn Share on XING

Abonniere meinen Newsletter

*

Ich werde Deine Email-Addresse niemals weitergeben. Du kannst den Newsletter jederzeit abbestellen.



© 1999 - 2017 by Philipp Stiefel