Möchtest Du den gesamten Artikel lesen? Und vielleicht sogar den Artikel im PDF-Format und die Beispieldateien herunterladen? Dann hole Dir den Artikel gleich hier - völlig kostenlos!
Wenn Du die Tabellen einer Access-Datenbank mit dem SQL Server Migration Assistant zum SQL Server migriert hast, ist der Großteil einer SQL Server-Migration bereits geschafft. Allerdings stehen noch weitere Arbeiten wie das Anpassen des VBA-Codes, Abfragen, Formularen und Berichten bevor. Eine andere, wichtige Aufgabe ist das Sicherstellen der Funktion der Tabellenverknüpfungen. Diese wurden, wenn die richtige Option im SQL Server Migration Assistant markiert wurde, bereits initial angelegt, während die Original-Tabellen der Access-Datenbank umbenannt wurden. Wie aber stellen wir sicher, dass die Tabellenverknüpfungen auch nach dem Ändern des Tabellenentwurfs im SQL Server aktuell bleiben? Das erläutern wir in diesem Artikel.
Beispieldatenbank
Im Artikel Access-Datenbank zum SQL Server migrieren (www.vbentwickler.de/484) zeigen wir, wie Du eine Access-Datenbank zum SQL Server migrierst. Die dort erstellt SQL Server-Datenbank nutzen wir als Beispielmaterial für die folgenden Abschnitte. Wir gehen zum Start davon aus, dass wie eine leere Access-Datenbank haben, in der wir Tabellenverknüpfungen auf Basis dieser SQL Server-Datenbank erstellen wollen.
Warum Tabellen per VBA verknüpfen?
Bevor wir uns ans Werk machen, stellt sich die Frage: Warum sollte ich überhaupt meine Access-Datenbank überhaupt per VBA mit Tabellen aus dem SQL Server verknüpfen?
Wenn man eine Migration mit dem SQL Server Management Studio durchführe, werden ja bereits automatisch Tabellenverknüpfung hinzugefügt, und außerdem gibt es doch in Access ausreichend Möglichkeiten, Tabellen über die Benutzeroberfläche zu verknüpfen. Dazu gehört beispielsweise der Assistent, den wir über den Ribbon-Eintrag Externe Daten|Aus Datenbank|Aus SQL Server öffnen (siehe Bild 1).
Bild 1: Öffnen des Assistenten zum Verknüpfen oder Importieren von SQL Server-Tabellen
Alternativ können wir auch den Befehl Externe Daten|Neue Datenquelle|Aus anderen Quellen|ODBC-Datenbank nutzen (siehe Bild 2).
Bild 2: Öffnen des Assistenten für ODBC-Datenbanken
Beide führen dazu, dass wir eine Datenquelle erstellen, die auf einem Treiber besteht – der in beiden Fällen optimalerweise der aktuelle ODBC-Treiber für die Verbindung mit Microsoft SQL Server ist.
In beiden Fällen können wir Tabellenverknüpfungen erstellen, die funktionieren und die je nach den Einstellungen, die wir dort vornehmen, zum Beispiel wie folgt aussehen:
Description=AccessSQLServer;DRIVER=ODBC Driver 18 for SQL Server;SERVER=amvDesktop2023;Trusted_Connection=Yes;APP=Microsoft Office;DATABASE=SQLServerTabellenverknuepfen;TrustServerCertificate=Yes;
Tabellenverknüpfungen verwalten
Wenn die Tabellen einmal verknüpft sind, können wir diese sogar über die Benutzeroberfläche verwalten.
Dazu bietet sich zunächst der Tabellenverknüpfungs-Manager an, den wir über den Ribbon-Eintrag aus Bild 3 öffnen.
Bild 3: Öffnen des Tabellenverknüpfungs-Managers
Der Tabellenverknüpfungs-Manager erscheint anschließend wie in Bild 4.
Bild 4: Der Tabellenverknüpfungs-Managers
Hier sehen wir für jede Datenquelle einen Haupteintrag, den wir öffnen können. Danach erscheinen alle Tabellen, die aus dieser Datenquelle stammen.
Wir können alle Tabellen markieren, indem wir einen Haken für den Haupteintrag setzen, oder auch einzelne Einträge markieren.
Dies aktiviert die beiden Schaltflächen Aktualisieren und Verknüpfung erneuern. Wenn wir auf Aktualisieren klicken, wird die Verknüpfung aktualisiert, wobei alle Änderungen, die wir zwischenzeitlich an der markierten Tabelle im SQL Server vorgenommen haben, an die Tabellenverknüpfung übertragen werden.
Wenn wir also beispielsweise ein neues Feld zur Tabelle tblAbteilungen hinzufügen und diesen Befehl betätigen, sehen wir beim nächsten Öffnen der Tabellenverknüpfung in Access das neue Feld.
Ähnlich funktioniert die Schaltfläche Verknüpfung erneuern. Wenn wir nur eine Tabelle neu verknüpfen wollen, erscheint der Dialog aus Bild 5, mit dem wir die bereits verknüpfte Tabelle übernehmen oder einen neuen Namen eingeben können.
Bild 5: Neuverknüpfung einer Tabelle
Wenn wir jedoch die gesamte Datenquelle markieren, erscheint der Dialog aus Bild 6. Damit können wir also auch die Verbindungszeichenfolge bearbeiten. Danach wird der Dialog zum Verknüpfen einer Tabelle für alle Tabellenverknüpfungen dieser Datenquelle jeweils einmal angezeigt.
Bild 6: Neuverknüpfung einer Datenquelle
Verknüpfungen über den Navigationsbereich aktualisieren
Die Funktion zum Aktualisieren einer Tabellenverknüpfung können wir auch schneller aufrufen. Dazu klicken wir mit der rechten Maustaste auf die Tabellenverknüpfung im Navigationsbereich und wählen dort den Befehl Link aktualisieren aus (siehe Bild 7).
Bild 7: Aktualisieren einer Datenquelle
Direkt darüber sehen wir noch eine weitere Möglichkeit, den Tabellenverknüpfungs-Manager zu öffnen.
Außerdem können wir eine Tabellenverknüpfung über das Kontextmenü in eine lokale Tabelle konvertieren.
Das ist hilfreich, wenn man zum Beispiel eine Datenbank an einen anderen Entwickler übergeben möchte, wenn dieser Arbeiten an der Datenbank durchführen soll, man aber nicht die SQL Server-Datenbank mitliefern möchte.
Wenn es solche Möglichkeiten gibt, warum sollten wir dann also das Aktualisieren oder Erneuern der Tabellenverknüpfungen per VBA programmieren?
Dazu gibt es verschiedene Gründe:
- Zum Aktualisieren oder Neuverknüpfen sind immer mehrere Klicks erforderlich. Das kostet Zeit und ist fehleranfällig, weil man schnell eine falsche Verbindungszeichenfolge oder einen falschen Tabellennamen eingegeben hat.
- In der Regel entwickeln wir auf dem eigenen Rechner. Gegebenenfalls wechseln wir dabei die SQL Server-Datenbank oder den SQL Server. Wenn wir das im Entwicklungsmodus noch über die Benutzeroberfläche machen, erhöht dies lediglich den oben beschriebenen Aufwand.
- Spätestens wenn wir die Anwendung in den Produktivbetrieb übernehmen, müssen wir sicherstellen, dass die Tabellenverknüpfungen mit der dort zu verwendenden Verbindungszeichenfolge erstellt werden. Auch das könnten wir noch vorbereitend durchführen, aber dann müssten wir immer bereits auf unserem lokalen Entwicklungsrechner die Tabellenverknüpfungen mit genau der Verbindungszeichenfolge ausstatten, die wir auch auf dem Zielsystem vorfinden.
- Ein weiterer Grund ist, dass wir beim Neuverknüpfen von Tabellen über die Benutzeroberfläche für die Tabellenverknüpfungen zum Beispiel der Tabelle dbo.tblAbteilungen einen Namen wie dbo_tblAbteilungen erhalten. Das heißt, dass wir diesen noch nachträglich anpassen müssen, wenn wir beispielsweise nur den Namen der Tabelle, also tblAbteilungen, ohne das vorangestellte Schema erhalten wollen.
Deshalb ist es sinnvoll, einige VBA-Routinen vorzubereiten, mit denen wir die Tabellenverknüpfungen jederzeit per Mausklick entweder aktualisieren oder erneuern können.
Wenn wir dann zum Beispiel auf dem Entwicklungssystem den Server wechseln, brauchen wir einfach nur eine andere Verbindungszeichenkette zu irgendwo in der Datenbank zu speichern, die dann auf dem Zielrechner zum Herstellen der Tabellenverknüpfungen verwendet wird.
Tabellenverknüpfungen aktualisieren bei Windows-Authentifizierung vs. SQL Server-Authentifizierung
Wenn wir die Tabellenverknüpfungen über die Benutzeroberfläche aktualisieren wollen, gibt es einen Unterschied bezüglich der beiden Authentifizierungsmethoden Windows-Authentifizierung und SQL Server-Authentifizierung.
Bei der Windows-Authentifizierung wird bekanntlich das Windows-Konto des aktuellen Benutzers im SQL Server überprüft.
Ist dieses bekannt und die entsprechende Anmeldung im SQL Server hat Berechtigungen für den Zugriff auf die entsprechenden Tabellen, erfolgt die Aktualisierung ohne weitere Interventionen des SQL Servers.
Wenn wir jedoch die SQL Server-Authentifizierung verwendet wird, prüft der SQL Server, ob Benutzername und Kennwort der jeweiligen SQL Server-Anmeldungen vorliegen.
Das ist der Fall, wenn man in der laufenden Access-Session bereits einmal die Anmeldedaten für das Aktualisieren oder erneute Anlegen der Tabellenverknüpfungen verwendet hat. Diese werden dann intern gespeichert.
Sobald die Session jedoch geschlossen ist, also die laufende Access-Anwendung mit den zwischengespeicherten Anmeldedaten beendet wurde, liegen die Anmeldedaten nicht mehr im Speicher.
Öffnet man die Access-Anwendung mit den verknüpften Tabellen erneut und versucht, eine der Tabellen zu öffnen, erscheint die Meldung aus Bild 8.
Bild 8: Abfrage der Verbindungsdaten von per SQL Server-Authentifizierung verknüpften Tabellen
Wenn wir die Daten einmal eingegeben haben, können wir alle Tabellen, die über die gleiche Verbindungszeichenfolge verknüpft sind, wieder öffnen.
Nachfolgend beschreiben wir, wie man die Verknüpfung zu einer einzelnen Tabelle per VBA aktualisiert und wie man alle Tabellenverknüpfungen erneuert.
Bei Verwendung der SQL Server-Authentifizierung erscheint daher auch hier die Meldung zur Eingabe der Verbindungsdaten. Eine automatische Aktualisierung der Tabellenverknüpfungen ist also nur möglich, wenn wir die Windows-Authentifizierung für den Zugriff auf die Tabellen des SQL Servers verwenden.
Wenn wir die SQL Server-Authentifizierung nutzen, können wir die Tabellen nicht einfach mit den nachfolgend beschriebenen VBA-Prozeduren aktualisieren, sondern müssen zumindest eine Tabellenverknüpfung löschen und erneut anlegen. Dadurch sind anschließend aber auch alle anderen Tabellen mit der gleichen Verbindung wieder zugreifbar.
Hinweis: Früher war es möglich, den Benutzernamen und das Kennwort in der Verbindungszeichenfolge für eine Tabelle dauerhaft zu speichern, aber diese Funktion hat Microsoft aus Sicherheitsgründen entfernt. Das ist sinnvoll, denn sonst könnten die Zugangsdaten über die Benutzeroberfläche ausgelesen werden, was potenzielle Sicherheitslücken mit sich bringt.
Tabellenverknüpfung aktualisieren per VBA
Als Erstes schauen wir uns an, wie wir die Tabellenverknüpfungen per VBA aktualisieren können – also so, als ob wir den Befehl Link aktualisieren im Kontextmenü einer Tabelle im Navigationsbereich aufrufen.
Wir gehen hier davon aus, dass uns eine frisch mit dem SQL Server Migration Assistant migrierte Datenbankanwendung vorliegt.
Um beispielsweise die Tabellenverknüpfung zur Tabelle tblAbteilungen zu aktualisieren, verwenden wir die Methode RefreshLink des jeweiligen TableDef-Objekts.
Das Aufwendigste daran ist das Referenzieren dieses Objekts. Dazu verwenden wir die folgende Prozedur:
Public Sub TabellenverknuepfungAktualisieren() Dim db As DAO.Database Dim tdf As DAO.TableDef Set db = CurrentDb Set tdf = db.TableDefs("tblAbteilungen") tdf.RefreshLink Application.RefreshDatabaseWindow End Sub
Diese füllt die Variable db mit einem Verweis auf das aktuelle Database-Objekt. Dann referenzieren wir das TableDef-Objekt der Tabelle tblAbteilungen und rufen seine Methode RefreshLink auf. Schließlich aktualisieren wir noch den Navigationsbereich, damit die Änderung direkt sichtbar wird.
Sollten wir in der Zwischenzeit den Entwurf der SQL Server-Tabelle tblAbteilungen angepasst haben, werden diese Änderungen nun beim Öffnen der Tabelle in Access direkt sichtbar.
Alle Tabellenverknüpfungen aktualisieren
Eine einzige Tabellenverknüpfung wollen wir meist nur während der Entwicklung aktualisieren, was sich schneller durch das Betätigen des Befehls Link aktualisieren des Kontextmenüs des jeweiligen Eintrags im Navigationsbereich von Access realisieren.
Wir wollen vermutlich eher direkt alle Tabellen aktualisieren, zum Beispiel direkt dann, wenn der Benutzer die Anwendung öffnet. Dann können wir die folgende Prozedur aufrufen:
Public Sub AlleTabellenverknuepfungenAktualisieren() Dim db As DAO.Database Dim tdf As DAO.TableDef Set db = CurrentDb For Each tdf In db.TableDefs If Not Len(tdf.Connect) = 0 Then If Left(tdf.Connect, 5) = "ODBC;" Then Debug.Print tdf.Connect tdf.RefreshLink End If End If Next tdf Application.RefreshDatabaseWindow End Sub
Die Prozedur durchläuft alle Elemente der TableDefs-Auflistung und prüft in zwei If…Then-Bedingung, ob die Tabelle erstens einen Wert für die Eigenschaft Connect enthält und zweitens, ob dieser mit ODBC; beginnt.
Für Tabellen, die per ODBC verknüpft sind, liefert die Eigenschaft Connect beispielsweise Werte wie den folgenden:
ODBC;Description=AccessSQLServer;DRIVER=ODBC Driver 18 for SQL Server;SERVER=amvDesktop2023;Trusted_Connection=Yes;APP=Microsoft Office;DATABASE=SQLServerTabellenverknuepfen;TrustServerCertificate=Yes;
Tabellenverknüpfung mit neuer Verbindungszeichenfolge aktualisieren
Wenn wir die Frontend-Datenbank vom Entwicklungsrechner zum Rechner des Benutzers übertragen, bei dem einer der folgenden Faktoren zutrifft, reicht das reine Aktualisieren mit RefreshLink nicht aus:
- der Servername lautet anders,
- der SQL Server-Datenbankname lautet anders,
- es wird nicht die gleiche Authentifizierungsart verwendet, also Windows-Authentifizierung statt SQL Server-Authentifizierung oder umgekehrt oder
- es wird SQL Server-Authentifizierung verwendet, aber mit anderen Benutzerdaten.
In diesem Fall müssen wir vor dem Aufruf der Methode RefreshLink noch die neue Verbindungszeichenfolge mit den geänderten Parametern für die Eigenschaft Connect festlegen.
Die Prozedur TabellenverknuepfungAktualisieren müssen wir dann wie in Listing 1 um die Definition der zu verwendenden Verbindungszeichenfolge erweitern und diese der Eigenschaft Connect zuweisen.
Public Sub TabelleAktualisieren_NeueVerbindungszeichenfolge() Dim db As DAO.Database Dim tdf As DAO.TableDef Dim strODBCVerbindungszeichenfolge As String Set db = CurrentDb Set tdf = db.TableDefs("tblAbteilungen") strODBCVerbindungszeichenfolge = "ODBC;DRIVER=ODBC Driver 18 for SQL Server;SERVER=amvDesktop2023;" _ & "DATABASE=SQLServerTabellenVerknuepfen;UID=sa;PWD=********;Encrypt=YES;TrustServerCertificate=YES;" tdf.Connect = strODBCVerbindungszeichenfolge tdf.RefreshLink Application.RefreshDatabaseWindow End Sub
Listing 1: Aktualisieren mit neuer Verbindungszeichenfolge
Wann Tabellenverknüpfungen aktualisieren und wann löschen und erneuern?
Damit stellt sich nun die Frage, in welchem Szenario wir mit dem Aktualisieren der Tabellenverknüpfungen auskommen und wann wie die Tabellenverknüpfungen löschen und und neu erstellen müssen.
Da wir sogar eine neue Verbindungszeichenfolge beim Aktualisieren der Tabellenverknüpfungen über die Connect-Eigenschaft des TableDef-Objekts übergeben können, sollte dies für sehr viele Fälle bereits ausreichen.
Es gibt jedoch auch Fälle, wo tatsächlich ein Löschen und Neuerstellen der Tabellenverknüpfungen erforderlich ist:
- wenn sich Tabellennamen geändert haben
- wenn neue Tabellen hinzugekommen sind
- wenn Tabellen gelöscht wurden und verwaiste Tabellenverknüpfungen zurücklassen würden
Für diese Fälle haben wir einen Satz von Prozeduren und Funktionen programmiert, die wir in den folgenden Abschnitten beschreiben.
Besser aktualisieren als löschen und neu erstellen
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