RDBMS-Tabellen per VBA verknüpfen

2/2015, 2015, SQL Server und Co.

Im Beitrag RDBMS-Tabellen manuell verknüpfen haben Sie erfahren, wie Sie die Tabellen eines relationalen Datenbankmanagementsystems wie Microsoft SQL Server oder MySQL mit den Bordmitteln von Access verknüpfen. Im vorliegenden Beitrag schauen wir uns an, wie Sie dies per VBA erledigen können. Dabei lernen Sie auch die verschiedenen Verbindungszeichenfolgen kennen.

Wenn Sie eine Tabelle etwa vom SQL Server oder MySQL (stellvertretend für die vielen relationalen Datenbankmanagement-Systeme, die eine ODBC-Schnittstelle anbieten) verknüpfen, befinden sich ja nicht die Daten selbst in der Access-Datenbank, sondern nur die Verknüpfungen zu den Daten in den zugrunde liegenden Tabellen.

Wo aber speichert Access die Informationen darüber, wo sich diese Tabellen befinden Ein Teil dieser Informationen landet, wenn Sie die Verknüpfung über eine DSN (Data Source Name) erstellt haben, in der Registry (System-datenquelle oder Benutzerdatenquelle) oder in einer Textdatei (Dateidatenquelle).

Wir schauen uns dies anhand einiger Beispiele für den Zugriff auf eine MySQL-Datenbank an. Dazu legen wir, wie im Beitrag SQL-Server-Tabellen manuell verknüpfen beschrieben, drei Verknüpfungen zu einer Tabelle einer MySQL-Datenbank an – jeweils eine mit einer Dateidatenquelle, einer Benutzerdatenquelle und einer Systemdatenquelle. Wo wir die zugrunde liegenden Informationen dieser DSN finden, wissen wir – entweder in der Registry oder ein einer .dsn-Datei.

Die Systemtabelle MSysObjects

Aber woher weiß Access, woher es die Informationen über die Verknüpfungen nehmen soll Diese Daten finden wir in der Systemtabelle MSysObjects. Um diese Tabelle, die standardmäßig nicht sichtbar ist, zu öffnen, geben Sie im VBA-Editor den folgenden Befehl ein:

DoCmd.OpenTable "MSysObjects"

Den Inhalt dieser Tabelle nach dem Anlegen dreier Verknüpfungen wie oben beschrieben finden Sie wie in Bild 1 vor. Interessant sind hier vier Felder: Das Feld Type enthält den Objekttyp. Der Wert 4 steht für per ODBC eingebundene Tabellen.

Verknüpfungseigenschaften in der Systemtabelle MSysObjects

Bild 1: Verknüpfungseigenschaften in der Systemtabelle MSysObjects

ForeignName enthält den Namen der Tabelle in der Quelldatenbank, Name den Namen der Tabelle in der aktuellen Access-Datenbank. Connect liefert schließlich die Verbindungszeichenfolge. Und hier wird es interessant: Die per Dateidatenquelle eingebundene Tabelle referenziert im Gegensatz zu den beiden per System- oder Benutzerdatenquelle eingebundenen Tabellen gar keine externen Informationen.

Das bedeutet also, dass eine Dateidatenquelle nur zum Erstellen der Verknüpfung verwendet wird, danach nicht mehr. Die System- oder Benutzerdatenquelle hingegen wird wohl zumindest beim ersten Zugriff auf die verknüpfte Tabelle innerhalb einer Sitzung abgefragt.

Sinn der DSN

An dieser Stelle stellt sich die Frage nach dem Sinn einer DSN. Tatsächlich hängt es vom Anwendungsfall ab. Wenn die DSN nur von einer einzigen Datenbank verwendet wird, spielt es keine Rolle, ob Sie die Verknüpfungseigenschaften in der Datenbank speichern oder in eine DSN auslagern.

Sollten hingegen gleich mehrere Anwendungen auf die DSN zugreifen, hat eine System- oder Benutzerdatenquelle durchaus ihren Nutzen: Sie brauchen dann, wenn sich etwa der Servername der Datenquelle einmal ändert, nur die DSN anzupassen. Wenn Sie hingegen mehrere Datenbanken nutzen, die alle individuell mit der gleichen Datenquelle verknüpft sind, ohne eine System- oder Benutzerdatenquelle zu nutzen, müssen Sie die Verknüpfungsinformationen in allen betroffenen Datenbanken anpassen.

Wenn Sie die Verknüpfungseigenschaften allein in der Frontend-Datenbank speichern, haben Sie sogar den Vorteil, dass Sie diese Datenbank einfach verteilen können, ohne noch eine Benutzer- oder Systemdatenquelle hinzufügen zu müssen.

Verbindungszeichenfolge

Damit kommen wir nun dazu, uns den Umweg über die Assistenten zum Erstellen von Verknüpfungen zu ODBC-Datenquellen zu sparen und die Verknüpfung selbst zu erstellen. Dazu schauen wir uns zunächst an, wie solch eine Verbindungszeichenfolge überhaupt aufgebaut ist.

Kennzeichnung als ODBC-Verknüpfung

Diese beginnen immer mit dem Schlüsselwort ODBC, gefolgt von einem Semikolon (;). Dieses wird jedoch in der Eigenschaft Connect der verknüpften Tabellen in der Tabelle MSysObjects gar nicht aufgeführt. Der Grund ist: In der Spalte Connect landen ausschließlich Verbindungszeichenfolgen für ODBC-Datenquellen, daher wird das ODBC; hier weggelassen. Dass es dennoch nötig ist, zeigt ein Blick in die Eigenschaften des Entwurfs einer der betroffenen Tabellen (s. Bild 2).

Verknüpfungseigenschaften in den Eigenschaften des Tabellenentwurfs

Bild 2: Verknüpfungseigenschaften in den Eigenschaften des Tabellenentwurfs

Treiber festlegen

Neben dem einführenden ODBC; erwartet eine Verbindungszeichenfolge die Angabe des Treibers für die zu verwendende ODBC-Schnittstelle. Diese werden unter DRIVER angegeben. Für den SQL Server verwenden Sie beispielsweise die folgenden Werte – zunächst für den allgemeinen Treiber:

DRIVER={SQL Server};

Für eine bestimmte Version des SQL Servers verwenden Sie diesen Treiber:

DRIVER={SQL Server Native Client x.0};

Das x steht dabei für die Version des SQL Servers:

  • 9: SQL Server 2005
  • 10: SQL Server 2008/2008 R2
  • 11: SQL Server 2012

Für die aktuellen Versionen von MySQL verwenden Sie beispielsweise die folgenden Treiber-Ausdrücke – hier für die Version 5.2 (für Version 5.3 einfach 2 durch 3 ersetzen):

DRIVER={MySQL ODBC 5.2 ANSI Driver};
DRIVER={MySQL ODBC 5.2 Unicode Driver};

Diese ersetzen die zuvor verwendeten folgenden Ausdrücke für die Version 5.1 (a für ANSI, w für Unicode):

DRIVER={MySQL ODBC 5.1a Driver};
DRIVER={MySQL ODBC 5.1w Driver};

Festlegen des Servers

Mit dem Parameter SERVER geben Sie die Adresse des Servers an. Bei Verwendung von SQL Server können Sie beim Zugriff auf die Standardinstanz auf dem gleichen Rechner den Wert localhost, den Punkt (.) oder den Rechnernamen angeben – also beispielsweise so:

SERVER=localhost;
SERVER=.
SERVER=Rechnername

Wenn Sie auf die Standardinstanz von SQL Server auf einem anderen Rechner zugreifen wollen, geben Sie einfach dessen Rechnernamen als Wert an:

SERVER=Rechnername;

Bei SQL Server gibt es benannte Instanzen. Auf diese greifen Sie zu, indem Sie diese per Backslash (\) vom Ausdruck für den Rechner getrennt angeben:

SERVER=localhost\BenannteInstanz
SERVER=.\BenannteInstanz
SERVER=Rechnername\BenannteInstanz

Bei Verwendung von SQL Server Express Edition geben Sie explizit SQLEXPRESS als Instanzname an, zum Beispiel so:

SERVER=localhost\SQLEXPRESS
SERVER=Rechnername\SQLEXPRESS

Bei MySQL geben Sie beispielsweise localhost an, wenn sich MySQL auf dem gleichen Rechner befindet:

SERVER=localhost

Wenn es sich bei dem Rechner mit der MySQL-Installation etwa um einen Internetserver handelt, auf den Sie von außen zugreifen wollen, geben Sie die IP des Servers an:

SERVER=xxx.xxx.xxx.xxx

Angeben der Datenbank

Die Datenbank legen Sie mit dem Parameter DATABASE fest. Diesem übergeben Sie schlicht und einfach den Datenbanknamen:

DATABASE:Datenbankname;

Authentifizierungsmethode und -daten

Die Authentifizierungsmethode geben Sie nur beim SQL Server an. Dieser bietet ja die Möglichkeit, mit Windows-Authentifizierung zu arbeiten, also die Windows-Benutzerdaten zu verwenden. In diesem Fall stellen Sie den Parameter Trusted_Connection auf den Wert True ein:

TRUSTED_CONNECTION=true;

Verwenden Sie unter SQL Server die SQL Server-Authentifizierung, also mit separaten Benutzerkonten für den SQL Server, geben Sie Benutzername und Kennwort mit den Parametern UID und PWD an:

UID=Benutzername;PWD=Kennwort;

Dies gilt in dieser Form auch für MySQL.

Verknüpfung per VBA anlegen

Damit haben wir nun alle Informationen zusammen, um eine Verknüpfung per VBA zu erstellen.

Dies erledigen wir zunächst in einer einfachen VBA-Prozedur unter hartkodierter Eingabe der Parameter (s. Listing 1) – und zwar für eine Verknüpfung zu einer Tabelle einer MySQL-Datenbank.

Public Sub VerknuepfungErstellen()
     Dim strConnection As String
     Dim db As DAO.Database
     Dim tdf As DAO.TableDef
     strConnection = strConnection & "ODBC;"
     strConnection = strConnection & "DRIVER={MySQL ODBC 5.2 ANSI Driver};"
     strConnection = strConnection & "SERVER=xxx.xxx.xxx.xxx;"
     strConnection = strConnection & "DATABASE=MeineDatenbank;"
     strConnection = strConnection & "UID=MeinBenutzername;"
     strConnection = strConnection & "PWD=MeinKennwort;"
     Set db = CurrentDb
     On Error Resume Next
     db.TableDefs.Delete "tblBenutzer"
     On Error GoTo 0
     Set tdf = db.CreateTableDef("tblBenutzer")
     With tdf
         .Connect = strConnection
         .SourceTableName = "s_user"
     End With
     db.TableDefs.Append tdf
     tdf.RefreshLink
     Application.RefreshDatabaseWindow
End Sub

Listing 1: Erstellen einer Verknüpfung per VBA

Die Prozedur Verknuepfung-Er-stel-len stellt zunächst die Verbindungszeichenfolge in der Variablen strConnection zusammen. Diese sieht zusammengefasst so aus:

Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...

den kompletten Artikel im PDF-Format mit Beispieldatenbank

diesen und alle anderen Artikel mit dem Jahresabo

Schreibe einen Kommentar