Im Beitrag „Kopier- und Löschreihenfolge für Tabellen“ (www.access-im-unternehmen.de/926) haben wir ermittelt, wie wir die richtige Reihenfolge für das Löschen von Tabellen und das Kopieren von Tabelleninhalten von einer Datenbank in die nächste ermitteln. Wenn Sie die Reihenfolge nicht beachten, kann es nämlich sein, dass Datensätze wegen Fremdschlüsselverletzungen nicht gelöscht und auch nicht kopiert werden können. Im vorliegenden Beitrag zeigen wir, wie Sie die vorgestellte Lösung für das Löschen und Kopieren von Tabellen in MySQL-Datenbanken nutzen können.
In oben genanntem Beitrag haben wir bereits erläutert, in welcher Reihenfolge die Tabellen gelöscht und deren Daten kopiert werden müssen, damit dies ohne Fehlermeldungen durch Restriktionen möglich ist. Wir fassen das noch einmal zusammen: Wenn Sie etwa nur die beiden Tabellen tblKunden und tblAnreden verwenden, bei denen die Tabelle über das Fremdschlüsselfeld AnredeID mit dem Feld AnredeID der Tabelle tblAnreden verknüpft ist, gelten folgende Regeln:
- Beim Löschen der enthaltenen Datensätze müssen erst die Datensätze gelöscht werden, die in der Tabelle mit dem Fremdschlüsselfeld der Beziehung enthalten sind. Wir löschen also zunächst die Datensätze der Tabelle tblKunden und erst dann die Datensätze der Tabelle tblAnreden.
- Beim Kopieren der Daten von Tabellen einer Datenbank in die Tabellen der anderen Datenbank müssen erst die Datensätze der Tabelle mit dem Primärschlüsselfeld der Beziehung kopiert werden und dann die der anderen Tabelle. Wir kopieren also erst die Datensätze der Tabelle tblAnreden und erst dann die der Tabelle tblKunden.
Auf die gleiche Weise können Sie bei komplexeren Datenmodellen vorgehen. Um das zu automatisieren, sucht man sich zum Löschen der Daten zunächst die Tabellen heraus, auf die keine andere Tabelle per Fremdschlüsselfeld verweist. Im zweiten Schritt ermitteln wir alle Tabellen, die nicht bereits ermittelt wurden und auf die keine andere der noch übrigen Tabellen per Fremdschlüsselfeld verweist und so weiter.
Beim Kopieren von Daten suchen wir im ersten Schritt zuerst die Tabellen heraus, die nicht per Fremdschlüsselfeld auf andere Tabellen verweisen. Dann suchen wir im zweiten Schritt die Tabellen heraus, die per Fremdschlüsselfeld nur auf die im ersten Schritt ermittelten Tabellen verweisen und so weiter.
Reihenfolge per VBA bestimmen
Im weiter oben erwähnten Beitrag haben wir bereits einen Algorithmus definiert, mit dem wir die Reihenfolge der Tabellen beim Löschen und beim Kopieren der Daten der Tabellen ermitteln. Dabei haben wir die Elemente der DAO-TableDefs-Auflistung durchlaufen und für die Informationen bezüglich der Beziehungen die Daten der Relations-Auflistung genutzt.
Unter MySQL stehen uns diese Elemente nicht zur Verfügung. Wenn Sie zwei miteinander in Beziehung stehende und per ODBC eingebundene MySQL-Tabellen in das Beziehungen-Fenster ziehen, sehen Sie bereits, dass die für diese Tabellen definierte Beziehung nicht in Access verfügbar ist (siehe Bild 1).
Bild 1: Keine Beziehungen von ODBC-verknüpften Tabellen sichtbar
Alle Tabellen einbinden
Hier stehen wir am Scheideweg, denn nun gibt es zwei Möglichkeiten:
- Sie fügen alle per ODBC eingebundenen Tabellen zum Beziehungen-Fenster hinzu und fügen die Beziehungen manuell ein. Das ist allerdings je nach Anzahl der Tabellen eine Fleißarbeit, die – wie bei solchen Arbeiten üblich – mit einer Fehleranfälligkeit ausgestattet ist. Danach können Sie die Reihenfolge der Tabelle zum Löschen und Kopieren mit den bereits vorgestellten Prozeduren ermitteln.
- Sie schreiben eine Prozedur, die alle Tabellen der Datenbank unter MySQL hinsichtlich der Beziehungen untersucht und diese automatisiert zu den per ODBC eingebundenen Tabellen hinzufügt. Danach können Sie die Reihenfolge der Tabelle zum Löschen und Kopieren ebenfalls mit den bereits vorgestellten Prozeduren ermitteln.
- Oder wir passen die vorhandenen Prozeduren so an, dass wir per ADO direkt auf die Informationen über die Beziehungen zwischen den Tabellen zugreifen. Hierzu müssten wir noch herausfinden, wie wir auf die gewünschten Informationen zugreifen. Da wir hierbei etwas Neues lernen, entscheiden wir uns an dieser Stelle für diese Option.
Tabellen durchlaufen
Das Durchlaufen der Tabellen erfolgt nicht über eine Auflistung wie TableDefs, sondern wir nutzen die Anweisung SHOW TABLES, mit der wir wie mit einer SELECT-Anweisung auf die Daten der MySQL-Datenbank zugreifen. Show Tables liefert uns ebenfalls ein Ergebnis in Form eines Recordsets zurück. In den folgenden Codezeilen nutzen wir eine Hilfsfunktion namens GetRecordset, deren ersten Parameter wir mit einer Konstanten füllen, welche die Verbindungszeichenfolge zu der gewünschten Datenbank enthält:
Dim rstTabellen As ADODB.Recordset Set rstTabellen = GetRecordset(GetConnection( cStrVerbindungszeichenfolgeAlt), "SHOW TABLES") Do While Not rstTabellen.EOF Debug.Print rstTabellen.Fields(0) rstTabellen.MoveNext Loop
Hier füllen wir so ein Recordset namens rstTabellen mit allen Einträgen, die der Befehl SHOW TABLES zurückliefert und geben diese im Direktbereich des VBA-Fensters aus. Für die verwendeten Hilfsfunktionen benötigen wir noch einen Verweis auf die Bibliothek Microsoft ActiveX Data Objects x.y Library, den Sie über den Verweise-Dialog hinzufügen (VBA-Editor, Menüeintrag Extras|Verweise).
Beziehungen ermitteln
Nun wollen wir wissen, ob eine Tabelle über ein Fremdschlüsselfeld mit einer anderen Tabelle verknüpft ist oder ob eine Tabelle vom Fremdschlüsselfeld einer anderen Tabelle referenziert wird.
Dazu verwenden wir testweise die Prozedur Relationinfo aus Listing 1. In dieser weisen wir der Variablen strSQL die folgende Abfrage zu:
Public Sub Relationinfo() Dim rst As ADODB.Recordset Dim strSQL As String Dim fld As ADODB.Field strSQL = "SELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME " & vbCrLf _ & "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE" & vbCrLf _ & "WHERE REFERENCED_TABLE_NAME IS NOT NULL;" Set rst = GetRecordset(GetConnection(cStrVerbindungszeichenfolgeAlt), strSQL) For Each fld In rst.Fields Debug.Print fld.Name, Next fld Debug.Print Do While Not rst.EOF For Each fld In rst.Fields Debug.Print fld.Value, Next fld Debug.Print rst.MoveNext Loop End Sub
Listing 1: Ermitteln aller Beziehungen mit beteiligten Tabellen und Feldern
SELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME IS NOT NULL;
Hier ermitteln wir die Felder mit den relevanten Informationen aus der Tabelle INFORMATION_SCHEMA.KEY_COLUMN_USAGE für die Bedingung REFERENCED_TABLE_NAME IS NOT NULL. Danach durchlaufen wir alle Felder der Tabelle und geben die Namen der Felder als Kopfzeile im Direktbereich des VBA-Editors aus und liefern direkt anschließend die Werte dieser Felder für alle gefundenen Datensätze.
Das sieht dann etwa wie folgt aus:
TABLE_NAME COLUMN_NAME REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME s_order_basket_attributes basketID s_order_basket id s_core_auth_attributes authID s_core_auth id s_articles_attributes articleID s_articles id
Damit kommen wir nun zumindest an alle Informationen heran, die wir theoretisch benötigen, um die Reihenfolge der Tabellen für das Löschen und das Kopieren zu bestimmen. Wir müssen die Informationen nur noch geschickt kombinieren, was wir in den folgenden Schritten erledigen werden.
Reihenfolge für das Kopieren ermitteln
Die Reihenfolge der Tabellen für das Kopieren der Daten ermitteln wir in der Funktion TabellenreihenfolgeKopieren aus Listing 2. In dieser Funktion, welche die zu verwendende Verbindungszeichenfolge als Parameter erwartet und die ein Collection-Objekt als Ergebnis zurückliefert, erstellen wir zwei Collections. Die erste heißt colOffen und speichert alle Tabellen, die noch nicht in die Reihenfolge eingearbeitet wurden. Die zweite heißt colReihenfolge und enthält die Tabellen in der ermittelten Reihenfolge.
Public Function TabellenreihenfolgeKopieren(strVerbindungszeichenfolge As String) As Collection Dim colOffen As Collection, colReihenfolge As Collection, bolIstDetailtabelle As Boolean Dim varOffen As Variant, varReihenfolge As Variant, intCountVorher As Integer Dim rstTabellen As ADODB.Recordset, rstBeziehungen As ADODB.Recordset, strSQL As String Set colOffen = New Collection Set colReihenfolge = New Collection Set rstTabellen = GetRecordset(GetConnection(strVerbindungszeichenfolge), "SHOW TABLES") Do While Not rstTabellen.EOF colOffen.Add rstTabellen.Fields(0).Value, rstTabellen.Fields(0).Value rstTabellen.MoveNext Loop strSQL = "SELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME " & vbCrLf _ & "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE" & vbCrLf & "WHERE REFERENCED_TABLE_NAME IS NOT NULL;" Set rstBeziehungen = GetRecordset(GetConnection(cStrVerbindungszeichenfolgeAlt), strSQL) Do While colOffen.Count > 0 If Not intCountVorher = colOffen.Count Then intCountVorher = colOffen.Count For Each varOffen In colOffen bolIstDetailtabelle = False rstBeziehungen.MoveFirst Do While Not rstBeziehungen.EOF If rstBeziehungen!TABLE_NAME = varOffen Then bolIstDetailtabelle = True For Each varReihenfolge In colReihenfolge If rstBeziehungen!REFERENCED_TABLE_NAME = varReihenfolge Then bolIstDetailtabelle = False Exit For End If Next varReihenfolge End If rstBeziehungen.MoveNext Loop If bolIstDetailtabelle = False Then colReihenfolge.Add varOffen, varOffen colOffen.Remove varOffen End If DoEvents Next varOffen Else Exit Do End If Loop MsgBox "Anzahl offener Tabellen (da Zirkelbezug): " & colOffen.Count & vbCrLf _ & "Die Tabellen werden an die Collection angehängt." For Each varOffen In colOffen colReihenfolge.Add varOffen, varOffen Next varOffen Set TabellenreihenfolgeKopieren = colReihenfolge End Function
Listing 2: Ermitteln der Reihenfolge für das Kopieren von Daten
Um die Collection colOffen zu füllen, erstellen wir ein Recordset namens rstTabellen auf Basis der Abfrage SHOW TABLES. Dieses durchlaufen wir und tragen den Namen der Tabelle für jeden Datensatz in die Collection colOffen ein.
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