Microsoft Access bietet eine ganze Reihe von praktischen Assistenten. Ich setze beispielsweise sehr oft den Nachschlage-Assistenten ein, der nicht nur eine Beziehung mit den gewünschten Optionen anlegt, sondern das bearbeitete Feld auch noch als Kombinationsfeld auslegt, mit dem die Daten der verknüpften Tabelle leicht ausgewählt werden können. Eine m:n-Beziehung stellen Sie her, indem Sie zwei solcher Nachschlage-felder in der sogenannten Verknüpfungstabelle anlegen. Noch praktischer wäre es, wenn Sie diese Verknüpfungstabelle gar nicht erst anlegen müssten. Stattdessen wären nur die zu verknüpfenden Tabellen auszuwählen und der Assistent erledigt den Rest – das Anlegen der Verknüpfungstabelle mit den notwendigen Feldern sowie das Einrichten der Beziehungen zu den zu verknüpfenden Tabellen. Dieser Beitrag zeigt, wie Sie einen solchen Assistenten programmieren können.
Ausgangsposition
Der Assistent soll zwei Tabellen wie die in Bild 1 miteinander verknüpfen. Dazu benötigen wir eine weitere Tabelle etwa namens tblProdukteKategorien mit dem Primärschlüsselfeld ProduktKategorieID und den beiden Fremdschlüsselfeldern ProduktID und KategorieID.
Bild 1: Zu verknüpfende Tabellen
Diese stellen jeweils die Verknüpfung zu den Tabellen tblProdukte und tblKategorien her, sodass alle Einträge der Tabelle tblProdukte mit den Einträgen der Tabelle tblKategorien verknüpft werden können. Die Fremdschlüsselfelder legen Sie am schnellsten an, indem Sie im Tabellenentwurf den Eintrag Nachschlage-Assistent… wählen und die Verknüpfung darüber vornehmen. Die Verknüpfungstabelle sieht danach wie in Bild 2 aus.
Bild 2: Die Verknüpfungstabelle
Wechseln Sie in die Datenblattansicht, erhalten Sie eine Tabelle, mit deren Fremdschlüsselfeldern Sie die gewünschten Kombinationen aus Produkten und Kategorien leicht zusammenstellen können (siehe Bild 3).
Bild 3: Datenblattansicht der Verknüpfung
Die Beziehungen können Sie danach wie in Bild 4 im Beziehungen-Fenster einsehen.
Bild 4: Die m:n-Beziehung im Beziehungen-Fenster
Vorher
Um eine m:n-Beziehung zwischen zwei Tabellen herzustellen, sind normalerweise die folgenden Schritte nötig:
- Erstellen der Verknüpfungstabelle
- Wahl eines Namens für die Verknüpfungstabelle
- Hinzufügen eines Primärschlüsselfeldes
- Hinzufügen eines Fremdschlüsselfeldes zum Verknüpfen der Verknüpfungstabelle mit der m-Tabelle
- Hinzufügen eines Fremdschlüsselfeldes zum Verknüpfen der Verknüpfungstabelle mit der n-Tabelle
- Aufrufen des Nachschlage-Assistenten zum Erstellen der Beziehung zwischen dem ersten Fremdschlüsselfeld und dem Primärschlüsselfeld der m-Tabelle oder, falls kein Nachschlagefeld gewünscht ist, sondern nur die reine Beziehung, manuelles Hinzufügen der Beziehung über das Beziehungen-Fenster. Außerdem Einstellen der Beziehungseigenschaften wie referenzielle Integrität, Löschweitergabe und Aktualisierungsweitergabe.
- Aufrufen des Nachschlage-Assistenten zum Erstellen der Beziehung zwischen dem zweiten Fremdschlüsselfeld und dem Primärschlüsselfeld der n-Tabelle oder, falls kein Nachschlagefeld gewünscht ist, sondern nur die reine Beziehung, manuelles Hinzufügen der Beziehung über das Beziehungen-Fenster. Außerdem Einstellen der Beziehungseigenschaften wie referenzielle Integrität, Löschweitergabe und Aktualisierungsweitergabe.
- Gegebenenfalls Hinzufügen weiterer Felder zur m:n-Verknüpfungstabelle wie etwa Einzelpreis oder Menge bei einer Tabelle zum Speichern von Bestellpositionen
Anforderungen
Was genau benötigen wir an Informationen, um automatisiert eine Verknüpfungstabelle für zwei per m:n-Beziehung zu verknüpfenden Tabellen zu erstellen Beziehungsweise welche Informationen muss der Assistent zur Erstellung einer m:n-Beziehung vom Entwickler abfragen
- Name der ersten Tabelle
- Name des Primärschlüsselfeldes der ersten Tabelle
- Gegebenenfalls Name des anzuzeigenden Feldes der ersten Tabelle
- Name der zweiten Tabelle
- Name des Primärschlüsselfeldes der zweiten Tabelle
- Gegebenenfalls Name des anzuzeigenden Feldes der zweiten Tabelle
- Name der zu erstellenden Tabelle
- Namen für die Fremdschlüsselfelder zum Herstellen der Beziehung mit der m-Tabelle und der n-Tabelle
- Gegebenenfalls weitere Felder, die zur m:n-Tabelle hinzugefügt werden sollen
Programmieren des Add-Ins
Starten wir also direkt mit der Programmierung des Add-Ins. Dieses soll über ein Formular verfügen, mit dem wir alle Einstellungen für die Erstellung der m:n-Beziehung vornehmen können.
Auswahl der beteiligten Tabellen
Als Erstes benötigen wir zwei Kombinationsfelder, mit denen wir die beiden an der Beziehung beteiligten Tabellen ermitteln können.
Diese Kombinationsfelder erhalten nach dem Hinzufügen zu einem neuen Formular namens frmMNAssistent die Bezeichnungen cboMTabelle und cboNTabelle. Diese fügen wir jeweils in einen neuen Rahmen ein, sodass der Aufbau in der Entwurfsansicht zunächst wie in Bild 5 aussieht.
Bild 5: Assistenten-Formular
Damit der Benutzer die in der aktuellen Datenbank enthaltenen Tabellen auswählen kann, stellen wir die Eigenschaft Datensatzherkunft der beiden Kombinationsfelder auf die Abfrage aus Bild 6 ein.
Bild 6: Datensatzherkunft der Kombinationsfelder zur Auswahl der an der Beziehung beteiligten Tabellen
Damit die hier verwendete Tabelle MSysObjects sichtbar ist, müssen Sie zunächst die entsprechende Option aktivieren. Diese finden Sie, wenn Sie mit der rechten Maustaste auf den Navigationsbereich klicken und dann den Eintrag Navigationsoptionen… auswählen. Im nun erscheinenden Dialog Navigationsoptionen aktivieren Sie die Option Systemobjekte anzeigen.
Danach können Sie der Abfrage für die Eigenschaft Datensatzherkunft wie in der Abbildung die Tabelle MSysObjects hinzufügen und die beiden Felder Name und Type zum Entwurfsraster der Abfrage hinzufügen. Außerdem stellen Sie als Kriterium für das Feld Name den Wert Nicht Wie “MSys*” Und Nicht Wie “USys*” Und Nicht Wie “f_*” und für das Feld Type den Wert 1 ein. Das sorgt erstens dafür, dass keine System- und temporären Tabellen erscheinen und dass nur lokale Tabellen zur Auswahl angeboten werden.
Nach dem Schließen des Abfrageentwurfs können Sie den Wert der Eigenschaft Datensatzherkunft des Kombinationsfeldes cboMTabelle in die entsprechende Eigenschaft des Kombinationsfeldes cboNTabelle kopieren:
SELECT Name, Type FROM MSysObjects WHERE (Name Not Like "MSys*" And Name Not Like "USys*" And Name Not Like "f_*") AND (Type=1);
Wenn Sie anschließend in die Formularansicht wechseln, können Sie bereits die an der Beziehung beteiligten Tabellen auswählen (siehe Bild 7).
Bild 7: Auswahl der an der Beziehung beteiligten Kombinationsfelder
Auswahl der beteiligten Primärschlüsselfelder
Unter den beiden Kombinationsfeldern platzieren wir zwei weitere Kombinationsfelder namens cboMPrimaerschluessel und cboNPrimaerschluessel.
Diese sollen direkt nach der Auswahl einer Tabelle mit dem darüber befindlichen Kombinationsfeld alle Felder der gewählten Tabelle anzeigen und das Primärschlüsselfeld dieser Tabelle als Vorauswahl einstellen.
Dazu müssen wir zwei Aufgaben erledigen:
- eine Liste aller Felder der jeweiligen Tabelle zusammenstellen und
- das Primärschlüsselfeld aus diesen Feldern ermitteln.
Die Liste der Felder können wir auf zwei Wegen herausfinden. Der erste verwendet ausschließlich VBA und nutzt die Fields-Auflistung des jeweiligen TableDef-Objekts.
Der zweite verwendet die Herkunftsart Feldliste und die im ersten Kombinationsfeld ausgewählte Tabelle als Datensatzherkunft. Dabei müssen wir aber dennoch nach der Auswahl VBA bemühen, um die Eigenschaft Datensatzherkunft zu füllen. Um nur das zu erledigen, hinterlegen wir die folgenden beiden Ereignisprozeduren jeweils für Kombinationsfelder cboMTabelle und cboNTabelle:
Private Sub cboMTabelle_AfterUpdate() Me!cboMPrimaerschluessel.RowSource = Me!cboMTabelle End Sub Private Sub cboNTabelle_AfterUpdate() Me!cboNPrimaerschluessel.RowSource = Me!cboNTabelle End Sub
Damit bieten die beiden Kombinationsfelder bereits die Felder der jeweiligen Tabelle zur Auswahl an (siehe Bild 8).
Bild 8: Auswahl des Primärschlüsselfeldes für das Erstellen der m:n-Beziehung
Primärschlüsselfeld auswählen
Nun wollen wir noch dafür sorgen, dass direkt die Primärschlüsselfelder dieser Tabellen angezeigt werden. Schließlich soll der Benutzer so wenig Aufwand wie möglich haben. Dazu fügen wir den beiden Ereignisprozeduren cboMTabelle_AfterUpdate und cboNTabelle_AfterUpdate jeweils einen Aufruf einer Funktion namens Primaerschluessel-Ermitteln hinzu:
Private Sub cboMTabelle_AfterUpdate() Me!cboMPrimaerschluessel.RowSource = Me!cboMTabelle Me!cboMPrimaerschluessel = PrimaerschluesselErmitteln(Me!cboMTabelle) End Sub
Diese Funktion erwartet den Namen der zu untersuchenden Tabelle als Parameter. Sie füllt die Variable db mit einem Verweis auf das aktuelle Database-Objekt und tdf mit einem Verweis auf das TableDef-Objekt für die Tabelle aus strTabelle. Dann durchläuft sie alle Einträge der Auflistung tdf.Indexes und speichert das jeweils aktuelle Element in der Variablen idx. Für dieses prüft sie den Wert der Eigenschaft Primary.
Ist dieser True, schreibt sie den Namen des ersten Feldes dieses Indizes in den Rückgabewert der Funktion und beendet diese mit Exit Function. Das funktioniert für keinen, einen oder zusammengesetzte Primärschlüssel gleichermaßen sinnvoll: Wenn kein Primärschlüssel für die Tabelle vorhanden ist, liefert die Funktion eine leere Zeichenkette zurück, für einen Primärschlüssel mit einem Feld den Namen des betroffenen Feldes und für einen zusammengesetzten Primärschüssel den Namen des ersten Feldes:
Private Function PrimaerschluesselErmitteln( strTabelle As String) As String Dim db As DAO.Database Dim tdf As DAO.TableDef Dim idx As DAO.Index Set db = CurrentDb Set tdf = db.TableDefs(strTabelle) For Each idx In tdf.Indexes If idx.Primary Then PrimaerschluesselErmitteln = idx.Fields(0).Name Exit Function End If Next idx End Function
Für unser Beispiel mit den Produkten und Kategorien stellen die Prozeduren zuverlässig das jeweilige Primärschüsselfeld ein.
Informationen für das Nachschlagefeld
Zusätzlich wollen wir in diesem Formular die Daten für die Nachschlagefelder abfragen, sofern der Benutzer diese anlegen möchte. Manchmal ist es sinnvoll, zum Beispiel bei Bestellpositionen, wo man direkt aus der m:n-Verknüpfungstabelle das Produkt zu einer Bestellposition auswählen möchte. An anderen Stellen sind die Werte der Verknüpfungstabelle vielleicht gar nicht sichtbar, zum Beispiel wenn diese über zwei Listenfelder angezeigt werden.
Also fügen wir die notwendigsten Felder hinzu plus einem Kontrollkästchen zum Aktivieren oder Deaktivieren dieser Einstellungen.
Die Kontrollkästchen heißen chkMNachschlagefeld und chkNNachschlagefeld, die Kombinationsfelder zur Auswahl des anzuzeigenden Feldes für das Nachschlagefeld heißen cboMNachschlagefeld und cboNNachschlagefeld (siehe Bild 9). Damit die beiden Kombinationsfelder genau wie die zur Auswahl der Primärschlüsselfelder auch die Felder der zu verknüpfenden Tabellen anzeigen, fügen wir den Prozeduren cboMTabelle_AfterUpdate und cboNTabelle_AfterUpdate noch jeweils eine Anweisung hinzu:
Bild 9: Informationen für die Nachschlagefelder
Private Sub cboMTabelle_AfterUpdate() ... Me!cboMNachschlagefeld.RowSource = Me!cboMTabelle End Sub Private Sub cboNTabelle_AfterUpdate() ... Me!cboNNachschlagefeld.RowSource = Me!cboNTabelle End Sub
Damit die beiden Kontrollkästchen beim Aktivieren und Deaktivieren auch die beiden Kombinationsfelder cboMNachschlagefeld und cboNNachschlagefeld aktivieren beziehungsweise deaktivieren, fügen wir diese Ereignisprozeduren für das Ereignis Nach Aktualisierung der beiden Kontrollkästchen hinzu:
Private Sub chkMNachschlagefeld_AfterUpdate() Me!cboMNachschlagefeld.Enabled = Me!chkMNachschlagefeld End Sub Private Sub chkNNachschlagefeld_AfterUpdate() Me!cboNNachschlagefeld.Enabled = Me!chkNNachschlagefeld End Sub
Name für die zu erstellende Tabelle und ihre Felder ermitteln
Die Bezeichnungen für die zu erstellende Tabelle und die enthaltenen Felder bestimmen wir automatisch auf Basis der gewählten zu verknüpfenden Tabellen und den gewählten Primärschlüsselfeldern. Dies erledigen wir in der Prozedur VerknuepfungstabelleAktualisieren aus Listing 1. Sie liest zuerst die gewählten Tabellen in die Variablen strMTabelle und strNTabelle ein sowie die selektierten Primärschlüsselfelder.
Private Sub VerknuepfungstabelleAktualisieren() Dim strMTabelle As String Dim strNTabelle As String Dim strMPrimaerschluessel As String Dim strNPrimaerschluessel As String strMTabelle = Nz(Me!cboMTabelle, "") strNTabelle = Nz(Me!cboNTabelle, "") strMPrimaerschluessel = Nz(Me!cboMPrimaerschluessel, "") strNPrimaerschluessel = Nz(Me!cboNPrimaerschluessel, "") If Left(strMTabelle, 4) = "tbl_" Then strMTabelle = Mid(strMTabelle, 5) End If If Left(strMTabelle, 3) = "tbl" Then strMTabelle = Mid(strMTabelle, 4) End If If Left(strNTabelle, 4) = "tbl_" Then strNTabelle = Mid(strNTabelle, 5) End If If Left(strNTabelle, 3) = "tbl" Then strNTabelle = Mid(strNTabelle, 4) End If Me!txtVerknuepfungstabelle = "tbl" & strMTabelle & strNTabelle If strMPrimaerschluessel = "ID" Then strMPrimaerschluessel = strMTabelle & "ID" ElseIf Right(strMPrimaerschluessel, 2) = "ID" Then strMPrimaerschluessel = Left(strMPrimaerschluessel, Len(strMPrimaerschluessel) - 2) End If If strNPrimaerschluessel = "ID" Then strNPrimaerschluessel = strNTabelle & "ID" ElseIf Right(strNPrimaerschluessel, 2) = "ID" Then strNPrimaerschluessel = Left(strNPrimaerschluessel, Len(strNPrimaerschluessel) - 2) End If Me!txtMFremdschluesselfeld = strMPrimaerschluessel & "ID" Me!txtNFremdschluesselfeld = strNPrimaerschluessel & "ID" Me!txtPrimaerschluesselfeld = strMPrimaerschluessel & strNPrimaerschluessel & "ID" End Sub
Listing 1: Einstellen der Daten der Verknüpfungstabelle
Dann folgt die Prüfung, ob der Benutzer eines der Präfixe tbl oder tbl_ für die beiden zu verknüpfenden Tabellen verwendet. Falls ja, werden diese vorn abgeschnitten, sodass beispielsweise von tblProdukte oder tbl_Produkte nur noch Produkte übrig bleibt.
Daraus leitet die Prozedur den Namen der Verknüpfungstabelle ab, der aus dem Präfix tbl, dem Namen der m-Tabelle und dem Namen der n-Tabelle jeweils ohne Präfix besteht.
Das Ergebnis landet im Textfeld txtVerknuepfungstabelle.
Dann untersucht die Prozedur die gewählten Primärschlüsselfelder der beiden Tabellen. Diese dienen schließlich als Grundlage für die Benennung der Fremdschlüsselfelder der Verknüpfungstabelle.
Wenn der Name eines der Primärschlüsselfelder lediglich ID lautet, stellt die Prozedur den oben ermittelten Namen der Tabelle ohne Präfix voran. Das kann in unserem Fall auch der Plural sein, was zu Bezeichnungen wie ProdukteID oder KategorienID führen kann. Das ist aber kein Problem, denn der Benutzer kann die Bezeichnungen ja noch anpassen.
Sollte die Bezeichnung des Primärschlüsselfeldes jedoch der von uns erwarteten Konvention entsprechen, also aus dem Singular der Bezeichnung der enthaltenen Objekte bestehen (wie ProduktID oder KategorieID), dann speichert die Prozedur den Teil vor ID in den Variablen strMPrimaerschluessel und strNPrimaerschluessel. Anschließend hängen wir hinten wieder ID an und schreiben das Ergebnis in die Textfelder txtMFremdschluesselfeld und txtNFremdschluesselfeld. Warum haben wir ID erst abgetrennt Weil wir noch das Primärschlüsselfeld für die Verknüpfungstabelle benennen müssen, dass den Singular der Bezeichnungen der Elemente der ersten und der zweiten Tabelle und das Suffix ID enthalten soll, zum Beispiel ProduktKategorieID. Dieser Ausdruck landet schließlich im Textfeld txtPrimaerschluesselfeld.
Das Ergebnis sieht schließlich wie in Bild 10 aus. Damit dies wie gewünscht funktioniert, fügen wir den Aufruf dieser Prozedur in die folgenden beiden Prozeduren nachträglich ein:
Bild 10: Das Formular frmMNAssistent in der Formularansicht
Private Sub cboMTabelle_AfterUpdate() ... VerknuepfungstabelleAktualisieren End Sub Private Sub cboNTabelle_AfterUpdate() ... VerknuepfungstabelleAktualisieren End Sub
Für die Aktualisierung der beiden Kombinationsfelder cboMPrimaerschluessel und cboNPrimaerschluessel legen wir die folgenden Ereignisprozeduren an:
Private Sub cboNPrimaerschluessel_AfterUpdate() VerknuepfungstabelleAktualisieren End Sub Private Sub cboMPrimaerschluessel_AfterUpdate() VerknuepfungstabelleAktualisieren End Sub
Prüfen, ob Verknüpfungstabelle bereits vorhanden ist
Bevor wir gleich die Verknüpfungstabelle mit den angegebenen Feldern und Indizes anlegen, prüfen wir, ob es bereits eine Tabelle mit dem angegebenen Namen gibt.
Dazu verwenden wir die folgende Funktion namens ExistsTable, die als Parameter den Namen der zu untersuchenden Tabelle entgegennimmt:
Public Function ExistsTable(strTable As String) As Boolean Dim db As dao.Database Dim tdf As dao.TableDef Set db = CurrentDb On Error Resume Next Set tdf = db.TableDefs(strTable) On Error GoTo 0 If Not tdf Is Nothing Then ExistsTable = True End If End Function