Assistent für m:n-Beziehungen

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

Username or Email

Passwort

Angemeldet bleiben