Home > Artikel > Ausgabe 8/2016 > Zugriff auf Server über ODBC, Teil II

Zugriff auf Server über ODBC, Teil II

Achtung: Sie sind nicht angemeldet. Wenn Sie Abonnent sind und sich anmelden, lesen Sie den kompletten Artikel, laden das PDF herunter oder probieren die Beispieldatenbank aus (sofern vorhanden).

Das manuelle Verknüpfen einer SQL-Server-Tabelle in eine Access-Datenbank über den ODBC-Administrator von Windows ist die eine Sache, das programmgesteuerte Beleben der Verbindung und das Neuverknüpfen über VBA eine andere! Zeigte die Juli-Ausgabe von ACCESS BASICS die Grundlagen zum Umgang mit ODBC-Backends, so erfahren Sie hier mehr über die Ansprache von ODBC-Verbindungen durch VBA-Programmierung.

Beispieldatenbank

Die Beispiele dieses Artikels finden Sie in der Datenbank 1608_ODBC2.zip.

ODBC-Tabellen neu verknüpfen

Die Aufteilung einer Datenbank in Logik und Daten kennen Sie bereits von reinen Access-Datenbanken: Das Frontend enthält nur die Oberfläche, also Formulare und Berichte, sowie den VBA-Code, und außerdem Abfragen. Das Backend ist eine separate Access-Datei, welche ausschließlich Tabellen beherbergt, welche in das Frontend verknüpft werden. Ebenso verhält es sich mit dem Gespann Access und SQL-Server, nur dass sich hier die Daten aus einem DBMS-Server speisen und die Tabellen über ODBC-Verknüpfungen in das Frontend gelangen.

Beim Verschieben eines Access-Backends etwa auf eine andere Maschine stimmen dann die Tabellenverknüpfung nicht mehr und müssen beim Start des Frontends angepasst werden, da Access in der Verknüpfung grundsätzlich absolute statische Pfade abspeichert. Ähnlich sieht es bei ODBC-Verknüpfungen aus. Der Ort des SQL-Servers oder der Tabellen auf ihm kann sich ändern, was eine Anpassung der Verknüpfungen im Frontend nach sich ziehen muss.

Egal, ob beim Access- oder ODBC-Backend, das Neuverknüpfen der Tabellen sollte ein Vorgang sein, der weitgehend automatisiert und möglichst ohne manuelle Interaktion vonstattengeht. Hier kommen Sie um VBA-Routinen nicht herum, die Sie etwa in Ausgabe 06/2011 Tabellenverknüpfungen pflegen finden.

Bei ODBC-Verknüpfungen verwenden Sie Code, der ganz ähnlich gestaltet ist. Eine Tabelle wird über ein DAO-TableDef-Objekt repräsentiert und dessen Eigenschaft Connect enthält die relevanten Informationen zum Backend-Ort. Verwenden Sie etwa diese VBA-Zeilen, um die Connect-Eigenschaft für die Tabelle tblAdressen auszulesen:

Set dbs = CurrentDb

dbs.TableDefs("tblAdressen").Connect

Befände sich die Tabelle in einem Access-Backend, dann könnte das Ergebnis so aussehen:

;DATABASE=c:\users\fritz\test.accdb

Das erste Zeichen des Connect-Strings ist ein Semikolon, was darauf hinweist, dass vor ihm noch Anderes stehen könnte. Der Leer-String aber sagt Access, dass es sich hier um eine Access-Datei handeln muss, deren Pfad schließlich im Parameter DATABASE folgt.

Greift die Verknüpfung hingegen auf eine ODBC-Tabelle zu, so sieht das Ergebnis anders aus:

ODBC;DSN=Access Basics Demo;Driver={SQLite3 ODBC Driver};Database=c:\users\fritz\adressen.sqlite;FKSupport=1; usw...

Hier steht an erster Stelle der Ausdruck ODBC, was Access darauf hinweist, die Tabelle über einen ODBC-Treiber zu finden. Die weiteren Angaben hierzu folgen im Anschluss.

Die eine Möglichkeit besteht dabei im Zugriff auf eine DSN, wie sie im ersten Teil des Beitrags manuell über den ODBC-Administrator angelegt wurde. Hinter dem Parameternamen DSN folgt dann der Name der angelegten DSN – hier: Access Basics Demo. Das würde an sich bereits ausreichen, denn in der Benutzer-DSN, welche ihrerseits alle Informationen in der Registry ablegt, stehen schon alle Angaben zu Treiber, Datenbankort und Zugriffsoptionen.

Die Alternative wäre der Verzicht auf eine zuvor angelegte DSN. Dann entfällt der DSN-Parameter im Connect-String und alle Angaben zur ODBC-Verbindung müssen stattdessen in ihm angegeben werden. Das wäre der Teil ab Driver...:

ODBC;Driver={SQLite3 ODBC Driver}; Database=c:\users\fritz\adressen.sqlite; FKSupport=1;...usw.

Die aufeinanderfolgenden Parameternamen, wie Database, FKSupport, et cetera, sind keine, die Access selbst kennt! Sie unterscheiden sich je nach gewähltem ODBC-Treiber, der sie seinerseits kennt. Nur der SQLite-Treiber etwa weiß, dass Database der Ort der SQLite-Datei ist, die er laden soll, und dass FKSupport die Option zur Unterstützung von Fremdschlüsseln ist.

Wie kommen Sie an diese Parameternamen und deren Bedeutung? Hier bleibt Ihnen nur die Dokumentation zum Treiber auf dessen Herstellerseite oder die Recherche etwa auf connectionstrings.com, einer Seite, die zu allen gängigen DBMS die ODBC-Connect-Strings mehr oder weniger kommentiert auflistet.

Stimmen Parameternamen oder -werte nicht, so ignorieren ODBC-Treiber diese in der Regel. Soweit möglich, kommt dennoch eine Verbindung zustande und der Treiber setzt Default-Werte ein.

Fehlen relevante Angaben, so öffnet sich der jeweilige Treiber-Dialog des ODBC-Administrators und verlangt nach manueller Eingabe. Unter Umständen meldet Access aber auch einfach: Kann keine ODBC-Verbindung herstellen, oder ähnlich.

Im Falle des SQLite-Treibers und dem nebenstehenden Connect-String kommt es zur Fehlermeldung -7778 mit dem Text: Reservierter Fehler; es gibt keine Beschreibung für diesen Fehler. Microsoft macht wenig erhellende Angaben zu diesem Fehler. Er kann verschiedene Ursachen haben, die in Authentifizierungsproblemen begründet scheinen. Wie auch immer, ohne Angabe einer DSN funktioniert ein SQLite-Connect-String nicht.

Glücklicherweise legt der ODBC-Treiber für SQLite bei Installation automatisch eine System-DSN an, die immer den Namen SQLIte3 Datasource aufweist. Diese DSN enthält lediglich Angaben zum Treiber, nicht aber zum Ort einer SQLite-Datenbankdatei. Tatsächlich kann man nun im Connect-String beides kombinieren: den DSN-Namen und die Optionsparameter im Anschluss! Demgemäß hat der Connect-String für SQLite immer diesen Anfang:

ODBC;DSN=SQLite3 Datasource; Driver={SQLite3 ODBC Driver};...

Im Folgenden ergänzen Sie die weiteren Angaben, wovon der Ort der Datenbank mit Database die wichtigste ist.

Mit diesem Wissen im Gepäck können Sie eine ODBC-Tabellenverknüpfung nun per VBA ändern, indem Sie die Connect-Eigenschaft des zugehörigen TableDef-Objekts neu einstellen. Listing 1 zeigt eine Routine aus der Beispieldatenbank, die das demonstriert. In der String-Variablen sConnect wird der Connect-String zusammengebaut. Er besteht aus dem erwähnten Beginn für den SQLite-Treiber und allerlei Optionsparametern, auf deren Bedeutung wir hier nicht weiter eingehen. Zwischendrin wird der Pfad zur SQLite-Datei über den Parameter Database eingebracht, wobei sich dieser aus dem Verzeichnis des Frontends (CurrentProject.Path) und der SQLite-Datei adressenneu.sqlite bildet.

Sub ChangeConnectTable(Optional ViaConnect As Boolean = True)

     Dim sConnect As String

     Dim dbs As Database

     Dim tdf As TableDef

     Dim prp As DAO.Property

     

     sConnect = "ODBC;DSN=SQLite3 Datasource;" & _

                "Driver={SQLite3 ODBC Driver};" & _

                "Database=" & CurrentProject.Path & "\adressenneu.sqlite;" & _

                "LongNames=0;ShortNames=0;NoWCHAR=0;PWD=Admin;FKSupport=1;" & _

                "Timeout=500;NoTXN=0;OEMCP=1;BigInt=0;JDConv=0;StepAPI=0;" & _

                "NoCreat=1;SyncPragma=FULL;"

     Debug.Print sConnect

     If ViaConnect Then

         Set dbs = CurrentDb

         Set tdf = dbs.TableDefs("tblAdressen")

         tdf.Connect = sConnect

         tdf.RefreshLink

         dbs.TableDefs.Refresh

         Set tdf = Nothing

     Else

         DoCmd.TransferDatabase acLink, "ODBC Database", sConnect, acTable, _

             "tblAdressen", "tblAdressen"

         DoCmd.DeleteObject acTable, "tblAdressen"

         DoCmd.Rename "tblAdressen", acTable, "tblAdressen1"

     End If

End Sub

Listing 1: Neuverknüpfen einer ODBC-Tabelle über eine VBA-Prozedur

Im weiteren Verlauf – der Zweig nach If ViaConnect – setzt die Prozedur die Objektvariable tdf auf das TableDef-Objekt zur Tabelle tblAdressen. Deren Connect-Eigenschaft wird nun mit dem neu gebildeten String aus sConnect bestückt. RefreshLink ist eine Methode, die Access anweist, die Connect-Eigenschaft auszuwerten und die Verbindung zum Backend neu herzustellen. TableDefs.Refresh führt sicherheitshalber dazu, dass die Tabelle auch im Navigationsbereich korrekt dargestellt wird. Nach Durchlaufen des Codes ohne Fehlermeldung können Sie die Tabelle als Datenblatt öffnen und haben damit die Daten aus adressenneu.sqlite vor sich.

ViaConnect als optionaler Parameter ist in der Routine standardmäßig auf True gesetzt. Er bedeutet, dass zum Neuverknüpfen das TableDef-Objekt und seine Connect-Eigenschaft zum Einsatz kommen sollen. Es gibt jedoch noch eine alternative Methode.

TransferDatabase

Diese Methode des DoCmd-Objekts vollbringt das gleiche, wie die Änderung von TableDef.Connect. Ist in der Routine aus Listing 1 der Parameter ViaConnect auf False gesetzt, so wird der zweite Teil des Bedingungszweigs angesprungen. TransferDatabase dient entweder zum Importieren oder zum Verknüpfen von Fremdtabellen. Die Liste der Parameter und ihrer Bedeutung entnehmen Sie der Access-Hilfe, indem Sie die Methode im Code markieren und F1 drücken.

acLink sagt Access, dass die Tabelle verknüpft werden soll. Handelt es sich um die über ODBC angesprochene Tabelle eines SQL-Servers, so hat darauf als Typ der Ausdruck ODBC Database zu folgen. Anschließend übergeben Sie den gleichen Connect-String in sConnect, wie beim TableDef-Objekt. Zusätzlich definieren Sie mit acTable, dass es hier um eine Tabelle handelt, deren Namen auf dem SQL-Server (tblAdressen) sie als weiteren Parameter angeben. Schließlich können Sie mit dem letzten Parameter bestimmen, wie die Tabelle unter Access heißen soll. Dieser dann im Navigationsbereich angezeigte Name kann sich beliebig von dem auf dem SQL-Server unterscheiden!

Der Haken an der Geschichte: TransferDatabase modifiziert eine bestehende Tabellenverknüpfung nicht, sondern legt eine neue an! Da die Tabelle tblAdressen im Frontend bereits existiert, legt Access eigenmächtig einen anderen Namen an, nämlich tblAdressen1, statt die Verknüpfung zu überschreiben. Aus diesem Grund löscht die folgende Codezeile über die Methode DeleteObject von DoCmd die alte Tabellenverknüpfung und benennt nun die neue per Rename wieder in tblAdressen um.

Zum Ändern einer ODBC-Verknüpfung eignet sich also das TableDef-Objekt besser, zum neuen Anlegen einer Verknüpfung die Methode TransferDatabase.

ODBC-Tabellen

Mit den ODBC-verknüpften Tabellen können Sie im Frontend genauso arbeiten, wie mit normalen Access-Tabellen. Also etwa Formulare, Berichte und Abfragen auf ihnen basieren lassen. Das Anzeigen von Daten gelingt praktisch immer problemlos. Beim Ändern oder Hinzufügen von Datensätzen kann es hingegen manchmal zu Aktualisierungsfehlern kommen. Ob und wann dies geschieht, hängt vom verwendeten SQL-Server und dem Aufbau seiner Tabellen ab. Generell lässt sich sagen, dass diese Fehler reduziert werden, wenn jede Tabelle einen eindeutigen Primärschlüssel aufweist. Zusätzlich macht es sich gut, jeder Tabelle ein sogenanntes Timestamp-Feld zu spendieren. Diesen Datentyp kennen Access-Tabellen selbst nicht, jedoch fast alle anderen DBMS. Es handelt sich um einen speziellen Datumstyp, den der SQL-Server wie einen Autowert immer mit dem augenblicklichen Datum belegt, sobald ein Datensatz abgespeichert wird. Indizieren Sie das Feld auf dem SQL-Server besser auch noch eindeutig. Access interpretiert beim Verknüpfen der Tabelle diesen Typ korrekt als Datum. Verwenden Sie das Feld jedoch nicht aktiv in Formularen oder Abfragen!

PassThrough-Abfragen

Abfragen, die ODBC-verknüpfte Tabellen benutzen, lassen sich auf gewohnte Weise im Abfrageneditor anlegen und bearbeiten. Der manchmal geäußerte Einwand, dass damit die Performance des SQL-Server beeinträchtigt würde, weil Access erst alle Daten der Tabellen laden müsse, um sie anschließend zu filtern und zu verknüpfen, stimmt so nicht. Die Database Engine von Access ist intelligent genug, um intern die Abfrage so in SQL-Statements umzuwandeln, dass auch der SQL-Server diese versteht. Tatsächlich setzt Access bereits beim Öffnen eines Tabellendatenblatts ein SELECT-Statement an den SQL-Server ab, der dieses verarbeitet und das Ergebnis zurückschickt.

Nicht immer jedoch ist das Ergebnis optimal, weil andere DBMS vor allem mit Tabellenverknüpfungen in Abfragen (Joins) anders umgehen. Zudem kennen SQL-Server einen weitaus größeren Schatz an SQL-Anweisungen, von denen Access nur träumen kann. Diese können über den visuellen Abfrageneditor von Access nicht verwendet werden. Deshalb gibt es mit den sogenannten Pass-Through-Abfragen eine Möglichkeit, den Dialekt eines SQL-Servers direkt anzusprechen.

Um eine neue solche Abfrage anzulegen, klicken Sie im Ribbon unter Erstellen auf Abfrageentwurf. Fügen Sie keine Tabelle aus dem nun erscheinenden Dialog hinzu, sondern betätigen die Schaltfläche Schließen. Anschließend klicken Sie auf den Ribbon-Button Pass-Through unter dem Tab Abfragetyp. Automatisch schaltet sich dadurch der Editor in die SQL-Ansicht. Auch das Eigenschaftenblatt ändert seine Parameterliste. Der wichtigste Eintrag lautet hier ODBC-Verbindung, wobei hier bereits der String ODBC; voreingestellt ist.

Sie haben die Wahl, diesen String manuell zu ergänzen, oder über die Schaltfläche mit den Pünktchen, die erscheint, wenn das Eingabefeld den Fokus besitzt, eine DSN zu ermitteln. Sie öffnet den Windows ODBC-Administrator zur Auswahl oder zum Neuanlegen einer DSN. Da wir möglichst ohne eine DSN auskommen möchten, füllen wir das Feld mit der Angabe des gleichen Connect-Strings, der schon beim Neuverknüpfen der ODBC-Tabellen zum Einsatz kam.

Bild 1 zeigt exemplarisch eine Pass-Through-Abfrage mit ihren Eigenschaften. Das simple SQL-Statement, welches an die SQLite-Datenbank gesandt werden soll, lautet VACUUM. Das ist die Anweisung an den ODBC-Treiber, die SQLite-Datei zu komprimieren und aufzuräumen. Die Anweisung gleicht ziemlich genau der Access-Funktion Komprimieren und Reparieren.

Pass-Through-Abfrage zum Komprimieren des SQLite-Backends

Bild 1: Pass-Through-Abfrage zum Komprimieren des SQLite-Backends

In ODBC-Verbindung ist der Connect-String eingetragen, den wir früher bereits für die Tabellenverknüpfung ermittelten. Liefert Datensätze stellen Sie auf Nein ein, weil die Anweisung VACUUM ja kein Ergebnis zurückgibt. Wollten Sie eine Tabelle über ein SELECT-Statement abfragen, so müsste diese Position natürlich auf Ja stehen.

Bei den weiteren Parametern können Sie es getrost bei den voreingestellten Werten belassen. Meldungen protokollieren hat nur bei der Entwicklung zur Fehleranalyse eine Bedeutung. Die ODBC-Wartezeit ist, vor allem bei SQLite-Datenbanken, ohne Belang. Liefert der SQL-Server nach der angegebenen Zeit kein Ergebnis, so bricht Access die Ausführung der Abfrage ab. Max Datensätze begrenzt die Zahl der von einer SELECT-Abfrage zurückgelieferten Datensätze. Standardmäßig lädt Access alle.

Tragen Sie hier einen Wert ein, so kappt Access die gelieferte Ergebnisliste und ignoriert alles Weitere. Genauso gut, oder besser noch, können Sie den SQL-Server jedoch über ein LIMIT-Statement anweisen, die Datensätze zu begrenzen.

Führen Sie die Pass-Through-Abfrage aus, so geschieht scheinbar gar nichts. Ein Blick in den Explorer zeigt aber, dass sich die Größe der SQLite-Datei verringert hat, falls ihr Inhalt fragmentiert gewesen sein sollte.

Ein Beispiel für eine Pass-Through-Abfrage, kurz PT-Abfrage, die Ergebnisse direkt vom SQL-Server liefert, zeigt Bild 2. Im Eigenschaftenblatt ist wieder der bekannte ODBC-Connect-String eingetragen. Liefert Datensätze steht auf Ja. Der SQL-Ausdruck im Editor verknüpft die Tabellen tblAdressen, tblOrte und tblAnreden miteinander, um eine Liste von Kundenadressen zu ermitteln.

PT-Abfrage für das Kundendatenblatt

Bild 2: PT-Abfrage für das Kundendatenblatt

Das Ausführen der Abfrage zeigt diese Liste korrekt im Datenblatt. Wohlgemerkt: hier kommen keine verknüpften Tabellen ins Spiel! Der SQL-Server berechnet selbst die Abfrage und gibt nur die gewünschten Datensätze zurück. Diese Pass-Through-Abfrage ist in der Regel schneller, als eine identische, welche Sie auf die ODBC-verknüpften Tabellen losließen.

Alles neu verknüpfen

Haben Sie Pass-Through-Abfragen in Ihrem Frontend, so reicht beim Start gegebenenfalls das Neuverknüpfen der ODBC-Tabellen nicht aus, da ja auch diese Abfragen mit einem statischen Pfad zur SQLite-Datei oder zu einem SQL-Server belegt sind. Tatsächlich kennt auch das einer Abfrage zugehörige QueryDef-Objekt, wie eine Tabelle, die Eigenschaft Connect. Ihr kann man ebenfalls den Connect-String zuweisen und sie damit gleichsam neuverknüpfen.

Eine Routine, die alle ODBC-Tabellen und PT-Abfragen des Frontends auf einen Schlag neuverknüpft, zeigt Listing 2. Zunächst wird wieder der Connect-String zusammengebaut. Anschließend durchläuft eine For-Each-Schleife alle Tabellen der Datenbank über deren TableDef-Objekte. Nicht alle jedoch sind ODBC-verknüpft! Die Systemtabellen etwa sollen ja nicht verknüpft werden. Deshalb liest eine Zeile die Connect-Eigenschaft aus und bezieht die Tabelle nur dann in das Neuverknüpfen ein, wenn diese Eigenschaft den Teil-String sqlite enthält. Dies sollte für eine Identifikation ausreichen. Bei anderen DBMS-Systemen müssten Sie diese Identifikation entsprechend abändern.

Sub ReconnectTables()

     Dim sConnect As String

     Dim dbs As Database

     Dim tdf As TableDef

     Dim qdf As QueryDef

     Dim prp As DAO.Property

     

     sConnect = "ODBC;DSN=SQLite3 Datasource;" & _

                "Driver={SQLite3 ODBC Driver};" & _

                "Database=" & CurrentProject.Path & "\adressenneu.sqlite;" & _

                "FKSupport=1;NoTXN=1;OEMCP=1;NoCreat=1;SyncPragma=FULL;"

     Set dbs = CurrentDb

     For Each tdf In dbs.TableDefs

         If InStr(1, tdf.Connect, "sqlite", vbTextCompare) > 0 Then

             tdf.Connect = sConnect

             tdf.RefreshLink

         End If

     Next tdf

     For Each qdf In dbs.QueryDefs

         If InStr(1, qdf.Connect, "sqlite", vbTextCompare) > 0 Then

Sie haben das Ende des frei verfügbaren Teil dieses Artikels erreicht!

Wenn Sie mehr lesen und auf viele weitere Artikel zugreifen möchten, melden Sie sich als Abonnent unter Login an. Falls nicht, bestellen Sie doch einfach ein Jahresabonnement!