Es gibt ja die ausgefallensten Konstellationen von Access-Anwendungen. Manche Benutzer verwenden beispielsweise ein Access-Frontend, um die Daten aus verschiedenen Backend-Datenbanken zusammenzuführen und Auswertungen über die enthaltenen Daten durchzuführen. Interessant wird dies, wenn diese Backends sich an häufig wechselnden Orten befinden. Nehmen wir an, drei Tabellen kommen aus Datenbank X, fünf aus Datenbank Y und sieben aus Datenbank Z und so weiter – und manche Datenbanken befinden sich im gleichen Verzeichnis. Da gerät die Aktualisierung der Verknüpfungen schnell zum Tagesprojekt. Damit dies schneller geht, bauen wir uns ein kleines Formular zum schnellen Umschalten der Datenbanken.
Wenn Sie in einer Datenbankanwendung Verknüpfungen zu den Tabellen anderer Datenbanken verwenden, können Sie diese mit dem Tabellenverknüpfungsmanager aktualisieren. Dieser ist jedoch nicht besonders komfortabel: Erstens können Sie das Fenster nicht vergrößern, um beispielsweise die kompletten Pfade der verknüpften Tabellen anzuzeigen (s. Bild 1).
Bild 1: Der Tabellenverknüpfungs-Manager von Access
Sollten Sie eine Access-Datenbank zum Zusammenführen von Tabellenverknüpfungen aus mehreren anderen Datenbanken einsetzen, können Sie auch nicht auf die Schnelle alle Tabellen einer bestimmten Tabelle markieren, um diese en bloc zu aktualisieren. Sie müssen die Tabellen einer Datenbank mühsam zusammensuchen.
Noch interessanter wird es, wenn sich eine Anzahl von Datenbanken mit verknüpften Tabellen auf einem Netzlaufwerk befindet und sich etwa durch die änderung des Laufwerksbuchstabens die Pfade für alle verknüpften Tabellen ändern. In diesem Fall müssen Sie die änderungen für alle Tabellen manuell über den Tabellenverknüpfungs-Manager durchführen.
Glücklicherweise ist das Verknüpfen einer Tabelle kein Hexenwerk, und auch das Aktualisieren einer solchen gelingt per VBA recht einfach. Grund genug, die Funktionalität des Tabellenverknüpfungs-Managers nachzubilden und zu erweitern.
Was ist zu tun
Schauen wir uns an, welche Situationen auftreten können:
- Ein Benutzer verwendet eine einzige Frontend-Datenbank, die ihre Daten aus einer oder mehreren Backend-Datenbanken bezieht.
- Aus jeder Backend-Datenbank können eine oder mehrere Tabellen eingebunden sein.
- Eine oder mehrere Backend-Datenbanken können das Verzeichnis wechseln. Gegebenenfalls ändern sich dabei Teile des Verzeichnisses für alle betroffenen Datenbanken.
Was benötigen wir, um diese änderungen möglichst komfortabel abbilden zu können Das Formular aus Bild 2 und den darin enthaltenen VBA-Code. Das Add-In ist als Menü-Add-In konzipiert und kann somit von jeder Datenbank aus etwa über den Ribbon-Eintrag Datenbanktools|Add-Ins|Add-Ins|BackendAdmin gestartet werden. Es liest beim Öffnen alle Tabellen ein, die aus anderen Access-Datenbanken stammen und zeigt diese in einem Listenfeld an. Was können Sie nun mit diesem Add-In erledigen
Bild 2: Access-Add-In zum Aktualisieren der Verknüpfungen von Tabellen aus externen Datenbanken
Datenbanken nach dem Namen filtern
Ganz oben finden Sie ein Kombinationsfeld, in das Sie Text zum Filtern der Einträge des Listenfeldes mit den verknüpften Tabellen eingeben können. Nach der Eingabe eines jeden Buchstabens filtert das Listenfeld seinen Inhalt und markiert gleichzeitig alle aktuell sichtbaren Einträge.
Dies hat den Vorteil, dass Sie damit gleich die darunter befindlichen Schaltflächen nutzen können:
- Markierte auswählen: Fügt alle aktuell markierten Elemente zu der Auswahl hinzu, die beim Klick auf Verknüpfungen aktualisieren aktualisiert wird. Nicht markierte Einträge werden aus der Auswahl entfernt.
- Markierte hinzufügen: Fügt alle aktuell markierten Elemente zur Auswahl hinzu. Unterschied zur vorherigen Variante: Die bereits ausgewählten Einträge bleiben ausgewählt.
- Markierte entfernen: Entfernt alle markierten Einträge aus der Auswahl der Tabellen für die Aktualisierung der Verknüpfung.
Zum Verständnis: Markierung und Auswahl
Damit Sie die Arbeitsweise des Add-Ins verstehen können, müssen wir kurz ein paar Begriffe klären.
Mit Markiert meint das Add-In solche Einträge der Tabellenliste, die aktuell mit schwarzem Hintergrund markiert sind.
Mit Ausgewählt sind solche Einträge gemeint, die in der ersten Spalte des Listenfeldes ein x aufweisen und die beim Klick auf Verknüpfungen aktualisieren berücksichtigt werden (s. Bild 3). Mit einer Ausnahme, die später erläutert wird.
Bild 3: Verschiedene Markierungen beim BackendAdmin
Es gibt noch zwei Stellen, an denen Einträge mit dem Buchstaben x markiert werden können: Wenn eine Tabelle nicht in der angegebenen Datenbank gefunden werden konnte, stellt das Listenfeld dem jeweiligen Tabellennamen ein x voran. Und wenn die komplette Datenbank nicht unter dem angegebenen Dateinamen zu finden ist, wird diese mit einem x markiert.
Verknüpfungen aktualisieren
Davon ausgehend, dass Sie eine oder mehrere verknüpfte Tabellen zum Aktualisieren ausgewählt haben, müssen Sie noch angeben, welche Teile des Dateinamens angepasst werden sollen. Wenn beispielsweise der Laufwerksbuchstabe von e:\ auf x:\ geändert werden soll, tragen Sie im Kombinationsfeld Zu ersetzen: den Ausdruck e:\ und im Kombinationsfeld Ersetzen mit: den Ausdruck x:\ ein.
Sie werden feststellen, dass bei der Eingabe der Zeichenfolge im Kombinationsfeld Zu ersetzen: wiederum Einträge im Listenfeld markiert werden. In Bild 4 sehen Sie beispielsweise drei markierte Einträge, die eigentlich aktualisiert werden sollen. Davon enthalten aber nur zwei Einträge den im Kombinationsfeld Zu ersetzen: enthaltenen Ausdruck und dementsprechend werden auch nur diese aktualisiert.
Bild 4: Es werden nur die Einträge aktualisiert, die dem Ausdruck im Feld Zu ersetzen: entsprechen.
Ein Klick auf die Schaltfläche Verknüpfungen aktualisieren erledigt nun den Rest und stellt eine neue Verknüpfung zu der angegebenen Tabelle in der Datenbank mit den neu ermittelten Dateinamen her.
Dabei können zwei Probleme auftauchen:
- Die per Suchen/Ersetzen ermittelte Datenbank kann im Dateisystem nicht gefunden werden. In diesem Fall erscheint eine Meldung wie die aus Bild 5.
- Oder die Datenbank wird gefunden, die zu verknüpfende Tabelle ist jedoch nicht in dieser Datenbank enthalten. Dies zieht die Meldung aus Bild 6 mit der Angabe aller betroffenen Tabellen nach sich.
Bild 5: Die neue Quelldatenbank kann nicht gefunden werden …
Bild 6: … oder die zu verknüpfende Tabelle ist nicht in der angegebenen Datenbank enthalten.
Auch wenn mit dem Suchen/Ersetzen-Ausdruck nicht alle zum Neuverknüpfen ausgewählten Datenbanken erfasst werden können, erscheint ein entsprechender Hinweis. Für alle Tabellen, deren neue Quelldatenbank gefunden werden konnte und die dann auch noch eine Tabelle mit dem gesuchten Namen enthalten, aktualisiert das Add-In die Verknüpfung.
Programmierung der Anwendung
Das Formular frmBackendAdmin ist die Schaltzentrale der Anwendung und enthält auch jegliche Funktionalität. Gleich beim Laden wird das Ereignis Beim Laden ausgelöst, was die Ausführung der folgenden Ereignisprozedur nach sich zieht:
Private Sub Form_Load()
VerknuepfungenEinlesen
End Sub
Die Prozedur VerknuepfungenEinlesen (s. Listing 1) verwendet zwei Variablen des Typs Database – eine für die Datenbank, von der aus das Add-In gestartet wird (mit CurrentDb referenziert) und eine für das Add-In selbst (CodeDb). Die Prozedur führt gleich zu Beginn zwei SQL-Aktionsabfragen aus. Die erste löscht alle in der Tabelle tblTabellenUndDatenbanken enthaltenen Datensätze, die zweite fügt für jede in der Zieldatenbank enthaltene Verknüpfung einen Datensatz zu dieser Tabelle hinzu. Dabei verwendet sie die Tabelle MSysObjects als Datenherkunft und bezieht sich dabei auf alle Einträge, deren Feld Type den Wert 6 enthält – dies steht für per ODBC verknüpfte Tabellen. Es landen übrigens die Werte der Felder Name, Database und ForeignName in den Zielfeldern Tabelle, DatenbankAlt und TabelleOriginal. Tabelle entspricht dem Namen der Verknüpfung, so wie er im Datenbankfenster beziehungsweise Navigationsbereich der Datenbank angezeigt wird, TabelleOriginal ist der Name der Tabelle in der Quelldatenbank.
Listing 1: Einlesen der aktuellen Verknüpfungen der Datenbank
Private Sub VerknuepfungenEinlesen()
Dim dbAddIn As DAO.Database
Dim dbHost As DAO.Database
Dim rst As DAO.Recordset
Dim rstTest As DAO.Recordset
Set dbAddIn = CodeDb
Set dbHost = CurrentDb
dbAddIn.Execute "DELETE FROM tblTabellenUndDatenbanken", dbFailOnError
dbAddIn.Execute "INSERT INTO tblTabellenUndDatenbanken(Tabelle, DatenbankAlt, " _
& "TabelleOriginal) SELECT Name, Database, ForeignName From MSysObjects IN ''" _
& CurrentDb.Name & "'' WHERE Type=6", dbFailOnError
Set rst = dbAddIn.OpenRecordset("SELECT * FROM tblTabellenUndDatenbanken " _
& "ORDER BY DatenbankAlt, Tabelle", dbOpenDynaset)
Do While Not rst.EOF
rst.Edit
If Len(Dir(rst!DatenbankAlt)) = 0 Then
rst!DatenbankNichtVorhanden = True
Else
Set rstTest = dbAddIn.OpenRecordset("SELECT Name FROM MSysObjects IN ''" _
& rst!DatenbankAlt & "'' WHERE Name = ''" & rst!TabelleOriginal & "''")
If rstTest.EOF Then
rst!TabelleNichtVorhanden = True
End If
End If
rst.Update
rst.MoveNext
Loop
Me!lstVerknuepfteTabellen.Requery
End Sub
Die Tabelle tblTabellenUndDatenbanken sieht im Entwurf übrigens wie in Bild 7 aus. Die Felder Tabelle und TabelleOriginal speichern den Namen der Verknüpfung sowie den der Originaltabelle, DatenbankAlt und DatenbankNeu den bisherigen und den zukünftigen Dateipfad der Quelldatei. DatenbankNichtVorhanden und TabelleNichtVorhanden nehmen die Werte für die x-Markierungen in den Spalten für die Tabelle und die Datenbank im Listenfeld auf und Markiert verweist mit dem Wert True auf Tabellen mit zu ändernden Verknüpfungen.
Bild 7: Tabelle zum Speichern der verknüpften Dateien und ihrer Informationen
Danach durchläuft die Prozedur alle Datensätze der Tabelle tblTabellenUndDatenbanken in einer Do While-Schleife.
Dabei versetzt die Prozedur den aktuellen Datensatz zunächst mit der Edit-Methode in den Bearbeitungsmodus. In einer ersten Prüfung ermittelt sie, ob die angegebene Quelldatenbank dieser Verknüpfung im Dateisystem zu finden ist. Dazu verwendet sie die Dir-Funktion mit dem Dateipfad als Parameter.
Liefert diese eine Zeichenkette mit einer Länge größer als 0 zurück, ist die Datenbank vorhanden. Anderenfalls stellt die Prozedur den Wert des Feldes DatenbankNichtVorhanden auf True ein. Ist die Datenbank vorhanden, testet die Prozedur noch, ob diese die angegebene Tabelle enthält.
Dazu erstellt sie ein Recordset auf der Tabelle MSysObjects aus der Datenbank, welche die Verknüpfung enthält – dies wird mit dem IN-Schlüsselwort und dem nachfolgend angegebenen Namen der Quelldatenbank erreicht. Wenn dieses Recordset auf Basis der Tabelle MSysObjects einen Datensatz enthält, dessen Name mit dem Namen aus rst!TabelleOriginal übereinstimmt, ist auch die Tabelle vorhanden.
Anderenfalls stellt die Prozedur den Wert des Feldes TabelleNichtVorhanden der Tabelle tblTabellenUndDatenbanken auf den Wert True ein. Schließlich speichert die Prozedur die änderungen mit der Update-Methode und springt mit MoveNext zum nächsten Datensatz des Recordsets. Anschließend aktualisiert die Prozedur noch den Inhalt des Listenfeldes lstVerknuepfteTabellen mit der Requery-Methode.
Listenfeld füllen
Damit kommen wir zum Inhalt des Listenfeldes lstVerknuepfteTabellen. Dieses verwendet die Abfrage qryTabellenListe als Datensatzherkunft (s. Bild 8). Die Abfrage enthält einige berechnete Felder, deren Formeln aus Platzgründen nicht im Screenshot abgebildet werden konnten.
Bild 8: Diese Abfrage dient als Datenherkunft des Listenfeldes lstVerknuepfteTabellen.
Das Feld MarkiertesFeld verwendet die folgende Formel, die den Wert x ausgibt, wenn das Feld Markiert den Wert Wahr enthält:
MarkiertesFeld: Wenn([Markiert]=Wahr;"x";"")
Das Feld Tabellenname soll nicht nur den Tabellennamen ausgeben, sondern auch noch ein x voranstellen, wenn das Feld TabelleNichtVorhanden den Wert True enthält. Außerdem soll neben dem Namen der Verknüpfung in der aktuellen Tabelle auch der Name der Originaltabelle ausgegeben werden:
Tabellenname: Wenn([TabelleNichtVorhanden]; "x ";"") & [Tabelle] & "/" & [TabelleOriginal]
Gleiches gilt für das Feld Datenbank. Dieses prüft jedoch den Inhalt des Feldes DatenbankNichtVorhanden und fügt den Wert x voran, wenn dieses den Wert True aufweist:
Datenbank: Wenn([DatenbankNichtVorhanden]; "x ";"") & [DatenbankAlt]
Damit das Listenfeld die Daten wie gewünscht anzeigt, stellen Sie die Eigenschaft Spaltenanzahl auf 4 und die Eigenschaft Spaltenbreiten auf 0cm;0,501cm;6cm ein (s. Bild 9). Auf diese Weise wird die erste Zeile mit dem Primärschlüsselwert als gebundene Spalte ausgeblendet beziehungsweise mit der Breite 0cm angezeigt. Die zweite Zeile zeigt mit einer Breite von 0,5cm das x an, das einen Eintrag als für eine Verknüpfungsänderung ausgewählt kennzeichnet.
Bild 9: Einstellungen für die Datensatzherkunft des Listenfeldes lstVerknuepfteTabellen
Die Zeile mit dem Namen der Verknüpfung und der Originaltabelle erhält eine Breite von 6cm, der Dateipfad der Quelldatenbanken nimmt den Rest der Listenfeldbreite ein. Benutzer von Access 2007 und jünger profitieren hier davon, dass sich die Breite des Listenfeldes beim ändern der Breite des Formulars ebenfalls ändert.
Damit der Benutzer bequem Einträge der Liste aus- und abwählen kann, stellen Sie die Eigenschaft Mehrfachauswahl des Listenfeldes auf den Wert Erweitert ein. Die Auswahl funktioniert dann genauso wie im Windows Explorer.
Filtern von Verknüpfungen
Das Kombinationsfeld cboSuche soll nach der Eingabe eines Ausdrucks die im Listenfeld lstVerknuepfteTabellen angezeigten Datensätze filtern – und zwar so, dass nur solche Einträge angezeigt werden, deren Name den im Suchfeld angegebenen Ausdruck enthält. Außerdem sollen alle passenden Einträge direkt markiert werden, damit der Benutzer diese mit einem Klick auf eine der Schaltflächen Markierte auswählen oder Markierte hinzufügen mit einem x in der ersten Spalte versehen und sie somit zur Auswahl der zu ändernden Verknüpfungen hinzufügen kann.
Die beim Eingeben eines Zeichens in das Kombinationsfeld cboSuche ausgelöste Prozedur sieht wie in Listing 2 aus und prüft zunächst, ob die Länge der enthaltenen Zeichenkette länger als 0 ist. Falls ja, stellt sie die Datensatzherkunft auf einen neuen Ausdruck ein. Dieser liefert nur noch diejenigen Einträge der Abfrage qryTabellenListe, deren Feld Datenbank den in cboSuche enthaltenen Ausdruck enthält:
SELECT * FROM qryTabellenListe WHERE Datenbank LIKE ''*" & Me!cboSuche.Text & "*''"
Listing 2: Filtern und markieren von Tabellen mit bestimmten Quelldatenbanken
Private Sub cboSuche_Change()
Dim i As Integer
If Len(Me!cboSuche.Text) > 0 Then
Me!lstVerknuepfteTabellen.RowSource = "SELECT * FROM qryTabellenListe " _
& "WHERE Datenbank LIKE ''*" & Me!cboSuche.Text & "*''"
For i = 0 To Me!lstVerknuepfteTabellen.ListCount - 1
Me!lstVerknuepfteTabellen.Selected(i) = True
Next i
Else
Me!lstVerknuepfteTabellen.RowSource = "SELECT * FROM qryTabellenListe"
For i = 0 To Me!lstVerknuepfteTabellen.ListCount - 1
Me!lstVerknuepfteTabellen.Selected(i) = False
Next i
End If
End Sub
Die nun angezeigten Einträge durchläuft die Prozedur dann in einer For…Next-Schleife und stellt die Eigenschaft Selected für die aktuellen Einträge auf den Wert True ein. Dadurch werden diese Einträge mit dunklem Hintergrund markiert.
Sollte der Benutzer hingegen soeben den Inhalt von cboSuche geleert haben, soll das Listenfeld wieder alle Einträge anzeigen. Außerdem entfernt die Prozedur dann alle Hintergrund-Markierungen, indem sie in einer entsprechenden Schleife den Wert der Eigenschaft Selected für alle Einträge auf False einstellt.
Markierte Einträge für Verknüpfungsänderung auswählen
Ein Klick auf die Schaltfläche cmdAktuelleAuswahl soll die aktuell markierten, also mit dunklem Hintergrund dargestellten Einträge mit einem x in der ersten Spalte versehen. Die dadurch ausgelöste Prozedur finden Sie in Listing 3.
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