Beziehungen zwischen Tabellen/Abfragen ermitteln

Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.

Bild 1: Datenmodell mit Beispielverknüpfungen

Im Beitrag “Berichte und Unterberichte konfigurieren” haben wir die Aufgabe, für Kombinationen aus Tabellen und Abfragen herauszufinden, ob eine Beziehung zwischen den beiden Datenquellen besteht und über welche Felder diese realisiert wird. In diesem Fall nutzen wir dies, um automatisch die Verknüpfungsfelder für die Datensatzquellen von Haupt- und Unterberichten zu ermitteln. Wir erhalten also die Tabelle/Abfrage des Hauptberichts und des Unterberichts und benötigen nun die Felder, über welche die Beziehung zwischen den beiden Datenquellen hergestellt wird. Wie das gelingt, zeigt der vorliegende Beitrag.

Als Beispiel für diesen Beitrag verwenden wir die Tabellen der Südsturm-Datenbank, einen Ausschnitt des Datenmodells sehen Sie in Bild 1.

Bild 1: Datenmodell mit Beispielverknüpfungen

Aufgabenstellung

Wir benötigen eine Funktion, die für zwei gegebene Tabellen oder Abfragen die folgenden Daten herausfindet:

• Name der Parenttabelle
• Name des Parentfeldes
• Name der Childtabelle
• Name des Childfeldes

Nochmal die Definition von Parent- und Childtabellen: Die Parenttabelle ist die Tabelle, deren Primärschlüsselfeld an der Beziehung beteiligt ist, die Childtabelle ist die Tabelle, deren Fremdschlüsselfeld an der Beziehung beteiligt ist.

Bei der Beziehung zwischen den Tabellen tblKunden und tblBestellungen ist also tblKunden die Parenttabelle und tblBestellungen die Childtabelle. Das Parentfeld heißt KundeID, das Childfeld ebenfalls. Genau diese Informationen wollen wir mit der geplanten Funktion ermitteln.

Definition der benötigten Funktion

Die Funktion soll also die folgenden Eingangsparameter enthalten:

• Erste Tabelle (strTableOrQuery1)
• Zweite Tabelle (strTableOrQuery2)

Außerdem benötigen wir diese Ausgangsparameter für die Funktion:

• Parenttabelle (strParenttable)
• Parentfeld (strParentfield)
• Childtabelle (strChildtable)
• Childfeld (strChildfield)

Schließlich soll die Funktion noch einen Funktionswert zurückliefern, der angibt, ob überhaupt eine Beziehung gefunden wurde – dazu verwenden wir den Datentyp Boolean. Der Kopf der Funktion sieht also schon einmal so aus:

Public Function GetReference(strTableOrQuery1 As String, strTableOrQuery2 As String, strParenttable As String, strParentfield As String, strChildtable As String, strChildfield As String) As Boolean

Den Rest der Funktion sehen Sie in Listing 1. Die Funktion stellt zunächst alle Werte der Rückgabeparameter auf eine leere Zeichenkette ein (dies geschieht nur, weil wir mehrere Testaufrufe hintereinander durchführen und die Werte der Variablen in den Testaufrufen sonst beim erneutern Aufruf beibehalten würden).

Public Function GetReference(strTableOrQuery1 As String, strTableOrQuery2 As String, strParenttable As String, 
         strParentfield As String, strChildtable As String, strChildfield As String) As Boolean
     Dim db As DAO.Database
     Dim rel As DAO.Relation
     Set db = CurrentDb
     strParenttable = ""
     strParentfield = ""
     strChildtable = ""
     strChildfield = ""
     For Each rel In db.Relations
         If rel.Table = strTableOrQuery1 And rel.ForeignTable = strTableOrQuery2 Then
             strParenttable = rel.Table
             strChildtable = rel.ForeignTable
             strParentfield = rel.Fields(0).Name
             strChildfield = rel.Fields(0).ForeignName
             GetReference = True
             Exit Function
         End If
         If rel.Table = strTableOrQuery2 And rel.ForeignTable = strTableOrQuery1 Then
             strParenttable = rel.Table
             strChildtable = rel.ForeignTable
             strParentfield = rel.Fields(0).Name
             strChildfield = rel.Fields(0).ForeignName
             GetReference = True
             Exit Function
         End If
     Next rel
End Function

Listing 1: Funktion zum Ermitteln von Beziehungsdaten zweier Tabellen

Dann durchläuft die Funktion alle Relation-Objekte der aktuellen Datenbank, die wir mit der Relations-Auflistung des Database-Objekts aus CurrentDb erhalten. In dieser For Each-Schleife prüfen wir zunächst, ob der Name der mit strTableOrQuery1 übergebenen Tabelle mit der Eigenschaft Table des Relation-Objekts übereinstimmt und der Name aus strTableOrQuery2 mit dem Wert der Eigenschaft ForeignTable. Table enthält immer den Namen der Parenttabelle, ForeignTable den Namen der Childtabelle.

Ist das der Fall, füllt die Funktion einfach die vier Rückgabeparameter mit folgenden Eigenschaften des Relation-Objekts:

• strParenttable: erhält den Wert der Eigenschaft Table
• strChildtable: erhält den Wert der Eigenschaft ForeignTable
• strParentfield: erhält den Wert der Eigenschaft Fields(0).Name
• strChildfield: erhält den Wert der Eigenschaft Fields(0).ForeignName

Warum Fields(0) Es kann sein, dass zwei Tabellen über mehr als eine Beziehung verknüpft sind. Dieser Fall sollte aber in einem sauber formulierten Datenmodell nicht vorkommen, daher berücksichtigen wir nur eine Beziehung.

Wenn die Parameter gefüllt sind, stellen wir noch den Funktionswert auf True ein und verlassen die Funktion mit Exit Function.

Da es auch sein kann, dass die Parenttabelle mit dem Parameter strTableOrQuery2 und die Childtabelle mit dem Parameter strTableOrQuery1 übergeben wurde, prüfen wir in einer zweiten If…Then-Bedingung auch noch den umgekehrten Fall. Auch hier tragen wir dann die entsprechenden Werte in die Rückgabeparameter ein und verlassen dann die Funktion.

Testen der Funktion

Um die Funktion zu testen, haben wir eine Prozedur mit einigen Testaufrufen programmiert. Diese finden Sie in gekürzter Fassung in Listing 2.

Public Sub Test_GetReference()
     Dim strParenttable As String
     Dim strParentfield As String
     Dim strChildtable As String
     Dim strChildfield As String
     If GetReference("tblArtikel", "tblBestelldetails", strParenttable, strParentfield, strChildtable, strChildfield) _
             = True Then
         If Not (strParenttable = "tblArtikel" And strParentfield = "ArtikelID" _
                 And strChildtable = "tblBestelldetails" And strChildfield = "ArtikelID") Then
             Debug.Print "Test 1a fehlgeschlagen 1"
         End If
     Else
         Debug.Print "Test 1a: fehlgeschlagen 2"
     End If
     If GetReference("tblBestelldetails", "tblArtikel", strParenttable, strParentfield, strChildtable, strChildfield) _
             = True Then
         If Not (strParenttable = "tblArtikel" And strParentfield = "ArtikelID" _
                 And strChildtable = "tblBestelldetails" And strChildfield = "ArtikelID") Then
             Debug.Print "Test 1b fehlgeschlagen 1"
         End If
     Else
         Debug.Print "Test 1b: fehlgeschlagen 2"
     End If
     If GetReference("tblArtikel", "tblPersonal", strParenttable, strParentfield, strChildtable, strChildfield) _
             = True Then
         Debug.Print "Test 5 fehlgeschlagen"
     End If
End Sub

Listing 2: Testen der Funktion zum Ermitteln von Beziehungsdaten zweier Tabellen

Diese Prozedur ruft die Funktion auf und übergibt beliebige Kombinationen von Tabellen. Liefert die Funktion in diesen Aufrufen den Wert True zurück, prüfen wir in einer If…Then-Bedingung, ob die vier Rückgabeparameter die erwarteten Werte zurückliefern.

Falls nicht, geben wir eine entsprechende Meldung im Direktbereich des VBA-Editors aus. Wenn schon das Funktionsergebnis entgegen den Erwartungen False lautet, geben wir ebenfalls eine Meldung im Direktbereich aus.

Am Ende haben wir auch noch einen Test für die Kombination zweier Tabellen eingefügt, für die es keine Beziehung gibt. Hier soll entsprechend der Wert False als Funktionswert zurückgegeben werden.

Erweiterung auf Abfragen

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

Testzugang

eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel

diesen und alle anderen Artikel mit dem Jahresabo