Lösch- und Kopierreihenfolge für Tabellen

Bei der einen oder anderen Gelegenheit werden Sie die Daten von Tabellen von einer Datenbank zur nächsten kopieren wollen oder einfach den Inhalt aller Tabellen löschen. Wenn die Tabellen mit referenziellen Beziehungen versehen sind, ist sowohl das Kopieren als auch das Löschen der enthaltenen Daten nicht trivial: Sie müssen nämlich in beiden Fällen in einer bestimmten Reihenfolge vorgehen. Beim Löschen müssen Sie erst alle verknüpften Daten aus der Detailtabelle einer Beziehung entfernen, bevor Sie die Daten der Mastertabelle löschen können. Beim Kopieren sind hingegen erst die Daten in den Mastertabellen einzufügen, bevor Sie die Detailtabellen samt Fremdschlüsselfeldinhalt füllen können.

In welcher Reihenfolge können die Daten von Tabellen überhaupt gelöscht werden, und in welcher Reihenfolge muss man Daten kopieren, wenn man keine Fehler durch fehlende Datensätze in den verknüpften Tabellen erhalten möchte

Beim Löschen entfernt man zunächst die Daten aus den Tabellen, die selbst von keiner anderen Tabelle referenziert werden. Wenn Sie also nur zwei Tabellen namens tblKunden und tblAnreden in einer Tabelle verwenden, wobei die Tabelle tblKunden mit einem Fremdschlüsselfeld namens AnredeID auf die Datensätze der Tabelle tblAnreden verweist, müssten Sie zunächst die Daten der Tabelle tblKunden löschen und dann die der Tabelle tblAnreden.

Sie löschen also immer die Daten der Tabelle, mit der keine andere Tabelle per Fremdschlüsselfeld verknüpft ist, und arbeiten sich so Tabelle für Tabelle durch das Datenmodell. Eine exakte Reihenfolge gibt es dabei nur, wenn immer eine Tabelle genau eine weitere Tabelle referenziert. Anderenfalls kann man einfach immer die Daten der Tabellen löschen, für die keine Datensätze mehr in anderen Tabellen vorliegen, die per Fremdschlüsselfeld auf die Datensätze der Mastertabelle verweisen.

Wenn Sie die Daten in einer solchen Beziehung von einer Datenbank in die andere übertragen wollten, müssten Sie die umgekehrte Reihenfolge wie beim Löschen wählen: Dann kopieren Sie erst die Daten der Tabellen, die selbst keine weitere Tabelle per Fremdschlüsselfeld referenzieren. In dem kleinen Mini-Beispiel von oben mit den Tabellen tblKunden und tblAnreden übertragen Sie also erst die Datensätze der Tabelle tblAnreden und dann die Datensätze der Tabelle tblKunden. Hierbei müssen Sie natürlich darauf achten, dass Sie entweder die Werte der Primärschlüsselfelder der Tabelle tblAnreden genau übertragen und dann die Inhalte des Fremdschlüsselfeldes der Tabelle tblKunden übernehmen oder dass Sie die Werte der Fremdschlüsselfelder an die geänderten Werte der verknüpften Primärschlüsselfelder anpassen.

Bei umfangreicheren Datenmodellen würden Sie also zunächst die Daten aller Tabellen kopieren, die selbst keine Fremdschlüsselfelder zur Verknüpfung mit anderen Tabellen enthalten. Dann folgen die Tabellen, die nur eine oder mehrere der bereits kopierten Tabellen referenzieren und so weiter.

Reihenfolge per VBA bestimmen

Ziel dieses Beitrags ist es, per VBA die Reihenfolge für die beiden zuvor beschriebenen Vorgänge, also das Löschen aller Tabellen und das Kopieren der Daten aller Tabellen von einer Datenbank in eine andere Datenbank mit identischem Datenmodell, zu ermitteln und beispielsweise per Array bereitzustellen.

Dazu benötigen wir im Wesentlichen den Zugriff auf die Tabellen des Datenmodells, den wir uns mit der TableDefs-Auflistung und den enthaltenen Elementen verschaffen. Darüber hinaus greifen wir auf die Informationen über die Verknüpfungen zwischen den einzelnen Tabellen zu. Diese liefert die Relations-Auflistung des Database-Objekts der betroffenen Datenbank. Das Relation-Objekt als ein Objekt dieser Auflistung enthält beispielsweise die Namen der beiden an der Beziehung beteiligten Tabellen sowie die Namen der Felder, über welche die Beziehung herstellt wird.

Grundlegende Techniken sind beispielsweise das Durchlaufen aller Tabellendefinitionen der Datenbank. Dies geschieht in einer For Each-Schleife wie folgt:

Public Sub TabellenDurchlaufen()
     Dim db As DAO.Database
     Dim tdf As DAO.TableDef
     Set db = CurrentDb
     For Each tdf In db.TableDefs
         Debug.Print tdf.Name
     Next tdf
     Set db = Nothing
End Sub

Gegebenenfalls legt man bestimmte Regeln über die zu untersuchenden Tabellen fest. Zum Beispiel sollen nur solche Tabellen untersucht werden, die mit dem Präfix tbl beginnen. Dann bauen Sie eine If…Then-Bedingung in die Schleife ein:

For Each tdf In db.TableDefs
     If tdf.Name Like "tbl*" Then
         Debug.Print tdf.Name
     End If
Next tdf

Auf die Beziehungen zwischen den Tabellen greifen Sie über die Relations-Auflistung zu – etwa so wie in Listing 1. Dies gibt jeweils die Mastertabelle und die Detailtabelle aus sowie die beiden Felder, die an der Beziehung beteiligt sind. Die Detailtabelle entspricht dabei der Tabelle, welche das Fremdschlüsselfeld enthält, die Mastertabelle der Tabelle mit dem an der Beziehung beteiligten Primärschlüsselfeld.

Public Sub BeziehungenDurchlaufen()
     Dim db As DAO.Database
     Dim rel As DAO.Relation
     Set db = CurrentDb
     For Each rel In db.Relations
         Debug.Print "Mastertabelle: " & rel.Table & " (" & rel.Fields(0).Name & ")"
         Debug.Print "Detailtabelle: " & rel.ForeignTable & " (" & rel.Fields(0).ForeignName & ")"
         Debug.Print "=============================="
     Next rel
     Set db = Nothing
End Sub

Listing 1: Beispielprozedur zum Ausgeben aller Beziehungen samt der betroffenen Tabellen und Felder

Referenzielle Integrität ermitteln

Wichtig ist im Zusammenhang mit der Ermittlung der Reihenfolge zum Kopieren von Daten, ob für die Beziehung referenzielle Integrität definiert ist. Ist das der Fall, können Sie für das Fremdschlüsselfeld nur solche Werte eingeben, die auch im Primärschlüsselfeld der an der Beziehung beteiligten Mastertabelle enthalten sind. Wenn keine referenzielle Integrität definiert ist, können Sie beliebige Werte in das Fremdschlüsselfeld eintragen, auch solche, die gar nicht in dem verknüpften Primärschlüsselfeld enthalten sind.

Reihenfolge zum Kopieren bestimmen

Das Kopieren aller Daten von einer Datenbank in eine weitere Datenbank mit identischem Datenmodell erfolgt beispielsweise in der Reihenfolge aus Bild 1. Dort werden erst alle Tabellen kopiert, die selbst keine Fremdschlüsselfelder enthalten, dann die Tabellen, die nur Fremdschlüsselfelder enthalten, die auf bereits in die Reihenfolge aufgenommene Tabellen verweisen und so weiter.

Bild 1: Reihenfolge beim Löschen der Tabellen dieses Datenmodells

Im ersten Schritt würden wir hier also zunächst alle Tabellen ermitteln, die selbst nicht als Detailtabelle an einer Beziehung beteiligt sind. Eine weitere Voraussetzung ist es, dass die zu untersuchenden Beziehungen mit referenzieller Integrität definiert wurden. Alle anderen spielen bei der Reihenfolge des Kopierens keine Rolle.

Für ein Datenmodell wie etwa das der Beispieldatenbank liefert die Funktion TabellenreihenfolgeKopieren aus Listing 2 ein Collection-Objekt mit der richtigen Reihenfolge zum Kopieren der Daten. Diese Prozedur füllt zunächst die Variable db mit einem Verweis auf die aktuelle Datenbank und instanziert zwei Collection-Objekte. Das erste namens colOffen nimmt zunächst die Tabellen aller zu untersuchenden Tabellen auf. Das zweite heißt colReihenfolge und wird Schritt für Schritt mit den Tabellen in der für das Kopieren benötigten Reihenfolge gefüllt.

Public Function TabellenreihenfolgeKopieren() As Collection
     Dim db As DAO.Database
     Dim rel As DAO.Relation
     Dim tdf As DAO.TableDef
     Dim colOffen As Collection, colReihenfolge As Collection
     Dim bolIstDetailtabelle As Boolean
     Dim varOffen As Variant, varReihenfolge As Variant
     Set db = CurrentDb
     Set colOffen = New Collection
     Set colReihenfolge = New Collection
     For Each tdf In db.TableDefs
         If tdf.Name Like "tbl*" Then
             colOffen.Add tdf.Name, tdf.Name
         End If
     Next tdf
     Do While colOffen.Count > 0
         For Each varOffen In colOffen
             bolIstDetailtabelle = False
             For Each rel In db.Relations
                 If rel.ForeignTable = varOffen Then
                     If Not ((rel.Attributes And dbRelationDontEnforce) = dbRelationDontEnforce) Then
                         bolIstDetailtabelle = True
                         For Each varReihenfolge In colReihenfolge
                             If rel.Table = varReihenfolge Then
                                 bolIstDetailtabelle = False
                                 Exit For
                             End If
                         Next varReihenfolge
                     End If
                 End If
             Next rel
             If bolIstDetailtabelle = False Then
                 colReihenfolge.Add varOffen, varOffen
                 colOffen.Remove varOffen
             End If
         Next varOffen
     Loop
     Set db = Nothing
     Set TabellenreihenfolgeKopieren = colReihenfolge
End Function

Listing 2: Prozedur, welche die Reihenfolge der Tabellen zum Kopieren von Daten ermittelt

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