Zugriff auf Server über ODBC, Teil II

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

Username or Email

Passwort

Angemeldet bleiben