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.<\/p>\n
Die Beispiele dieses Artikels finden Sie in der Datenbank 1608_ODBC2.zip<\/b>.<\/p>\nODBC-Tabellen neu verknüpfen<\/h2>\n
Die Aufteilung einer Datenbank in Logik und Daten kennen Sie bereits von reinen Access-Datenbanken: Das Frontend<\/b> enthält nur die Oberfläche, also Formulare und Berichte, sowie den VBA-Code, und außerdem Abfragen. Das Backend<\/b> 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<\/b>, nur dass sich hier die Daten aus einem DBMS-Server<\/b> speisen und die Tabellen über ODBC-Verknüpfungen<\/b> in das Frontend gelangen.<\/p>\n
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<\/b> 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. <\/p>\n
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<\/b> Tabellenverknüpfungen pflegen<\/b> finden.<\/p>\n
Bei ODBC-Verknüpfungen verwenden Sie Code, der ganz ähnlich gestaltet ist. Eine Tabelle wird über ein DAO-TableDef<\/b>-Objekt repräsentiert und dessen Eigenschaft Connect<\/b> enthält die relevanten Informationen zum Backend-Ort. Verwenden Sie etwa diese VBA-Zeilen, um die Connect<\/b>-Eigenschaft für die Tabelle tblAdressen<\/b> auszulesen:<\/p>\n Befände sich die Tabelle in einem Access-Backend, dann könnte das Ergebnis so aussehen:<\/p>\n Das erste Zeichen des Connect<\/b>-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<\/b> folgt.<\/p>\n Greift die Verknüpfung hingegen auf eine ODBC-Tabelle<\/b> zu, so sieht das Ergebnis anders aus:<\/p>\n Hier steht an erster Stelle der Ausdruck ODBC<\/b>, was Access darauf hinweist, die Tabelle über einen ODBC-Treiber<\/b> zu finden. Die weiteren Angaben hierzu folgen im Anschluss.<\/p>\n Die eine Möglichkeit besteht dabei im Zugriff auf eine DSN<\/b>, wie sie im ersten Teil des Beitrags manuell über den ODBC-Administrator<\/b> angelegt wurde. Hinter dem Parameternamen DSN<\/b> folgt dann der Name der angelegten DSN<\/b> – hier: Access Basics Demo<\/b>. Das würde an sich bereits ausreichen, denn in der Benutzer-DSN<\/b>, welche ihrerseits alle Informationen in der Registry ablegt, stehen schon alle Angaben zu Treiber, Datenbankort und Zugriffsoptionen. <\/p>\n Die Alternative wäre der Verzicht auf eine zuvor angelegte DSN<\/b>. Dann entfällt der DSN-Parameter<\/b> im Connect<\/b>-String und alle Angaben zur ODBC-Verbindung müssen stattdessen in ihm angegeben werden. Das wäre der Teil ab Driver<\/b>…:<\/p>\n Die aufeinanderfolgenden Parameternamen, wie Database<\/b>, FKSupport<\/b>, et cetera, sind keine, die Access selbst kennt! Sie unterscheiden sich je nach gewähltem ODBC-Treiber<\/b>, der sie seinerseits kennt. Nur der SQLite<\/b>-Treiber etwa weiß, dass Database<\/b> der Ort der SQLite<\/b>-Datei ist, die er laden soll, und dass FKSupport<\/b> die Option zur Unterstützung von Fremdschlüsseln ist.<\/p>\n 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<\/b>, einer Seite, die zu allen gängigen DBMS<\/b> die ODBC-Connect-Strings<\/b> mehr oder weniger kommentiert auflistet.<\/p>\n 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<\/b> ein.<\/p>\n Fehlen relevante Angaben, so öffnet sich der jeweilige Treiber-Dialog des ODBC-Administrators<\/b> und verlangt nach manueller Eingabe. Unter Umständen meldet Access aber auch einfach: Kann keine ODBC-Verbindung herstellen<\/b>, oder ähnlich. <\/p>\n Im Falle des SQLite-Treibers<\/b> und dem nebenstehenden Connect<\/b>-String kommt es zur Fehlermeldung -7778<\/b> mit dem Text: Reservierter Fehler; es gibt keine Beschreibung für diesen Fehler<\/b>. 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.<\/p>\n Glücklicherweise legt der ODBC-Treiber für SQLite<\/b> bei Installation automatisch eine System-DSN<\/b> an, die immer den Namen SQLIte3 Datasource<\/b> aufweist. Diese DSN<\/b> enthält lediglich Angaben zum Treiber, nicht aber zum Ort einer SQLite<\/b>-Datenbankdatei. Tatsächlich kann man nun im Connect-String<\/b> beides kombinieren: den DSN-Namen<\/b> und die Optionsparameter<\/b> im Anschluss! Demgemäß hat der Connect-String<\/b> für SQLite<\/b> immer diesen Anfang:<\/p>\n Im Folgenden ergänzen Sie die weiteren Angaben, wovon der Ort der Datenbank mit Database<\/b> die wichtigste ist.<\/p>\n 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<\/b> wird der Connect-String <\/b>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<\/b> eingebracht, wobei sich dieser aus dem Verzeichnis des Frontends (CurrentProject.Path<\/b>) und der SQLite-Datei adressenneu.sqlite<\/b> bildet.<\/p>\n Listing 1: Neuverknüpfen einer ODBC-Tabelle über eine VBA-Prozedur<\/p>\n Im weiteren Verlauf – der Zweig nach If ViaConnect<\/b> – setzt die Prozedur die Objektvariable tdf<\/b> auf das TableDef<\/b>-Objekt zur Tabelle tblAdressen<\/b>. Deren Connect<\/b>-Eigenschaft wird nun mit dem neu gebildeten String aus sConnect<\/b> bestückt. RefreshLink<\/b> ist eine Methode, die Access anweist, die Connect<\/b>-Eigenschaft auszuwerten und die Verbindung zum Backend neu herzustellen. TableDefs.Refresh<\/b> 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<\/b> vor sich.<\/p>\n ViaConnect<\/b> als optionaler Parameter ist in der Routine standardmäßig auf True<\/b> gesetzt. Er bedeutet, dass zum Neuverknüpfen das TableDef<\/b>-Objekt und seine Connect<\/b>-Eigenschaft zum Einsatz kommen sollen. Es gibt jedoch noch eine alternative Methode.<\/p>\n Diese Methode des DoCmd<\/b>-Objekts vollbringt das gleiche, wie die änderung von TableDef.Connect<\/b>. Ist in der Routine aus Listing 1 der Parameter ViaConnect<\/b> auf False gesetzt, so wird der zweite Teil des Bedingungszweigs angesprungen. TransferDatabase<\/b> 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<\/b> drücken.<\/p>\n acLink<\/b> 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<\/b> zu folgen. Anschließend übergeben Sie den gleichen Connect-String<\/b> in sConnect<\/b>, wie beim TableDef<\/b>-Objekt. Zusätzlich definieren Sie mit acTable<\/b>, dass es hier um eine Tabelle handelt, deren Namen auf dem SQL-Server (tblAdressen<\/b>) 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!<\/p>\n Der Haken an der Geschichte: TransferDatabase<\/b> modifiziert eine bestehende Tabellenverknüpfung nicht, sondern legt eine neue an! Da die Tabelle tblAdressen<\/b> im Frontend bereits existiert, legt Access eigenmächtig einen anderen Namen an, nämlich tblAdressen1<\/b>, statt die Verknüpfung zu überschreiben. Aus diesem Grund löscht die folgende Codezeile über die Methode DeleteObject<\/b> von DoCmd<\/b> die alte Tabellenverknüpfung und benennt nun die neue per Rename<\/b> wieder in tblAdressen<\/b> um.<\/p>\n Zum ändern einer ODBC-Verknüpfung eignet sich also das TableDef<\/b>-Objekt besser, zum neuen Anlegen einer Verknüpfung die Methode TransferDatabase<\/b>.<\/p>\n 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<\/b> zu spendieren. Diesen Datentyp kennen Access-Tabellen selbst nicht, jedoch fast alle anderen DBMS<\/b>. Es handelt sich um einen speziellen Datumstyp, den der SQL-Server wie einen Autowert<\/b> 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!<\/p>\n Möchten Sie weiterlesen? Dann lösen Sie Ihr Ticket!<\/span>Set<\/span> dbs = CurrentDb\r\n dbs.TableDefs(\"tblAdressen\").Connect<\/pre>\n;DATABASE=c:\\users\\fritz\\test.accdb<\/pre>\n
ODBC;DSN=Access Basics Demo;Driver={SQLite3 ODBC Driver};Database=c:\\users\\fritz\\adressen.sqlite;FKSupport=1; usw...<\/pre>\nODBC;Driver={SQLite3 ODBC Driver}; Database=c:\\users\\fritz\\adressen.sqlite; FKSupport=1;...usw.<\/pre>\nODBC;DSN=SQLite3 Datasource; Driver={SQLite3 ODBC Driver};...<\/pre>\nSub <\/span>ChangeConnectTable(Optional<\/span> ViaConnect As Boolean<\/span> = True<\/span>)\r\n Dim <\/span>sConnect As String<\/span>\r\n Dim <\/span>dbs As <\/span>Database\r\n Dim <\/span>tdf As <\/span>TableDef\r\n Dim <\/span>prp As <\/span>DAO.Property\r\n \r\n sConnect = \"ODBC;DSN=SQLite3 Datasource;\" & _\r\n \"Driver={SQLite3 ODBC Driver};\" & _\r\n \"Database=\" & CurrentProject.Path & \"\\adressenneu.sqlite;\" & _\r\n \"LongNames=0;ShortNames=0;NoWCHAR=0;PWD=Admin;FKSupport=1;\" & _\r\n \"Timeout=500;NoTXN=0;OEMCP=1;BigInt=0;JDConv=0;StepAPI=0;\" & _\r\n \"NoCreat=1;SyncPragma=FULL;\"\r\n Debug.Print<\/span> sConnect\r\n If <\/span>ViaConnect Then<\/span>\r\n Set<\/span> dbs = CurrentDb\r\n Set<\/span> tdf = dbs.TableDefs(\"tblAdressen\")\r\n tdf.Connect = sConnect\r\n tdf.RefreshLink\r\n dbs.TableDefs.Refresh\r\n Set<\/span> tdf = Nothing\r\n Else<\/span>\r\n DoCmd.TransferDatabase acLink, \"ODBC Database\", sConnect, acTable, _\r\n \"tblAdressen\", \"tblAdressen\"\r\n DoCmd.DeleteObject acTable, \"tblAdressen\"\r\n DoCmd.Rename \"tblAdressen\", acTable, \"tblAdressen1\"\r\n End If<\/span>\r\nEnd Sub<\/span><\/pre>\nTransferDatabase<\/h2>\n
ODBC-Tabellen<\/h2>\n
PassThrough-Abfragen<\/h2>\n
\nHier geht es zur Bestellung des Jahresabonnements des Magazins Access [basics]<\/strong>:<\/span>
\nZur Bestellung ...<\/a><\/span>
\nDanach greifen Sie sofort auf alle rund 400 Artikel<\/strong> unseres Angebots zu - auch auf diesen hier!<\/span>
\nOder haben Sie bereits Zugangsdaten? Dann loggen Sie sich gleich hier ein:<\/span><\/p>\n<\/div>\n\n\t\n\t