Home > Artikel > Ausgabe 1/2014 > Verknüpfte Daten kopieren

Verknüpfte Daten kopieren

  PDF ansehen

  Download PDF und Beispieldatenbank

Das Kopieren einfacher Datensätze ist schnell erledigt. Markieren, kopieren, einfügen – schon liegt der neue Datensatz vor. Was aber geschieht, wenn an dem zu kopierenden Datensatz noch weitere Daten hängen wie etwa solche aus verknüpften Tabellen? Dann gilt es erst einmal, die Beziehung zu prüfen und dann zu entscheiden, ob die verknüpften Daten ebenfalls dupliziert werden müssen. Und schließlich benötigen Sie auch noch etwas VBA-Code, um die verknüpften Daten in einem Rutsch zu kopieren. All dies finden Sie im vorliegenden Artikel.

Beispieldatenbank

Die Beispiele dieses Artikels finden Sie in der Datenbank 1401_VerknuepfteDatenKopieren.mdb.

Daten aus verknüpften Tabellen kopieren

Bevor wir uns auf die Programmierung der VBA-Routinen stürzen, wollen wir uns erst einmal ansehen, welche Konstellationen verknüpfter Daten es gibt und wann überhaupt auch die Inhalte der verknüpften Tabellen kopiert werden müssen.

Der einfachste Fall liegt vor, wenn etwa eine Tabelle namens tblKunden über das Fremdschlüsselfeld AnredeID mit einem Datensatz der Tabelle tblAnreden verknüpft ist (siehe Bild 1).

Beziehung zwischen Kunden und Anreden

Bild 1: Beziehung zwischen Kunden und Anreden

Die Tabelle tblAnreden enthält jede benötigte Anrede einmal, was schon darauf hindeutet, dass beim Kopieren eines Datensatzes einer Kundentabelle kein neuer Datensatz in der verknüpften Tabelle tblAnreden angelegt werden muss. Und so ist es: Sie kopieren einfach den Kundendatensatz, wobei der neue Datensatz im Fremdschlüsselfeld AnredeID den gleichen Verweis auf einen Datensatz der Tabelle tblAnreden enthält wie der Originaldatensatz.

Dies gilt eigentlich für alle Beziehungen, in denen die über ein Fremdschlüsselfeld referenzierte Tabelle lediglich Lookup-Werte für ein Feld der Haupttabelle liefert – also beispielsweise auch für Tabellen wie tblKategorien, tblGeschlecht et cetera. Dies kann sich aber auch auf Tabellen beziehen, die mehr als nur einen Lookup-Wert liefern, also beispielsweise einer Tabelle wie tblLieferanten, die etwa mit einer Tabelle namens tblArtikel verknüpft ist.

Individuelle Rechnungen

Anders sieht es aus, wenn die verknüpfte Tabelle Daten enthält, die erst in Zusammenhang mit dem Datensatz der Haupttabelle angelegt werden. Wenn Sie also etwa eine Rechnung zu einem Auftrag in der Tabelle tblRechnungen anlegen und dazu einige Rechnungspositionen in der damit verknüpften Tabelle tblRechnungspositionen, ändert sich die Situation (siehe Bild 2): Wenn Sie einen Rechnung kopieren, werden Sie wohl auch die Rechnungspositionen kopieren, um diese gegebenenfalls individuell anpassen zu können.

Rechnungen und Rechnungspositionen

Bild 2: Rechnungen und Rechnungspositionen

In diesem Fall gehen wir davon aus, dass es sich um Rechnungen für individuelle Leistungen handelt – also beispielsweise das Programmieren einer Anwendung für einen Kunden. Und trotz aller Individualität: Wenn es sich um ein größeres Projekt handelt, werden Sie möglicherweise immer wieder ähnliche Positionen in Rechnung stellen, die Sie nicht jedes Mal erneut formulieren möchten. Dies ist der erste Fall, den wir uns in diesem Artikel anschauen werden.

Bestellungen und Artikel

Der zweite Fall ist die klassische Bestellung mit Bestellpositionen und Artikeln. Hier werden die Bestellungen und die Artikel über eine Tabelle etwa namens tblBestelldetails miteinander per m:n-Beziehung verknüpft.

Wenn Sie hier eine Bestellung neu auf einer bereits vorhandenen Bestellung anlegen möchten, welche die gleichen Bestellpositionen wie das Original enthält, werden Sie sich freuen, wenn Sie dies per Mausklick erledigen können statt die Bestellung manuell Bestellposition für Bestellposition anzulegen. Dies ist das zweite in diesem Artikel behandelte Beispiel (siehe Bild 3).

Bestellungen, Bestellpositionen und Artikel

Bild 3: Bestellungen, Bestellpositionen und Artikel

Daten aus 1:n-Beziehungen kopieren

Nun wollen wir uns das erste Beispiel der individuellen Rechnungen ansehen. Den relevanten Teil des Datenmodells haben Sie ja bereits weiter oben kennengelernt. Zusätzlich haben wir ein Formular erstellt, dass die Daten der Tabelle tblRechnungen anzeigt und die Daten der per 1:n-Beziehung verknüpften Tabelle tblRechnungspositionen zur aktuellen Rechnung in einem Unterformular anzeigt (siehe Bild 4). Haupt- und Unterformular sind über den Wert RechnungID in den Eigenschaften Verknüpfen von und Verknüpfen nach des Unterformular-Steuerelements miteinander verknüpft.

Formular zum Erstellen individueller Rechnungen

Bild 4: Formular zum Erstellen individueller Rechnungen

Wenn Sie nun für den gleichen Kunden eine neue Rechnung erstellen, können Sie dies natürlich manuell erledigen. Wenn sich jedoch weder der Rechnungsbetreff noch die enthaltenen Rechnungspositionen nicht wesentlich geändert haben, können Sie auch den vorhandenen Rechnungsdatensatz kopieren – und ebenso die damit verknüpften Rechnungspositionen.

Dazu haben wir oben im Formular eine Schaltfläche namens cmdRechnungKopieren angelegt. Die durch das Anklicken dieser Schaltfläche ausgelöste Ereignisprozedur soll nun zwei Aufgaben erledigen:

  • Kopieren des Datensatzes aus der Tabelle tblRechnung (also der im Hauptformular angezeigte Datensatz) und
  • Kopieren der mit dem Datensatz im Hauptformular verknüpften Datensätze der Tabelle tblRechnungspositionen (also der Datensätze im Unterformular).

Es gibt (mindestens) zwei Möglichkeiten, diese Aufgabe zu erledigen:

  • durch das Anlegen der neuen Datensätze mit den DAO-Methoden AddNew/Update oder
  • mit entsprechenden SQL-Anweisungen.

Kopieren per DAO

Die erste Variante sieht wie in Listing 1 aus und kopiert die Daten mit AddNew/Update. Dazu ist offensichtlich eine Menge Code erforderlich, aber wir wollen ja auch gleich die Daten aus zwei verknüpften Tabellen duplizieren.

Private Sub cmdRechnungKopieren_Click()

     Dim db As DAO.Database

     Dim rstRechnungen As DAO.Recordset

     Dim rstPositionenAlt As DAO.Recordset

     Dim rstPositionenNeu As DAO.Recordset

     Dim lngAlteRechnungID As Long

     Dim lngNeueRechnungID As Long

     Set db = CurrentDb

     lngAlteRechnungID = Me!RechnungID

     Set rstRechnungen = db.OpenRecordset("SELECT * FROM tblRechnungen WHERE 1=2", dbOpenDynaset)

     With rstRechnungen

         .AddNew

         !Rechnungsbetreff = Me!Rechnungsbetreff

         !Rechnungstext = Me!Rechnungstext

         !Bemerkungen = Me!Bemerkungen

         !KundeID = Me!KundeID

         !Rechnungsdatum = Me!Rechnungsdatum

         lngNeueRechnungID = !RechnungID

         .Update

     End With

     Set rstPositionenAlt = db.OpenRecordset("SELECT * FROM tblRechnungspositionen WHERE RechnungID = " & lngAlteRechnungID, dbOpenDynaset)

     Set rstPositionenNeu = db.OpenRecordset("SELECT * FROM tblRechnungspositionen WHERE 1=2", dbOpenDynaset)

     With rstPositionenAlt

         Do While Not .EOF

             rstPositionenNeu.AddNew

             rstPositionenNeu!RechnungID = lngNeueRechnungID

             rstPositionenNeu!Rechnungsposition = !Rechnungsposition

             rstPositionenNeu!Menge = !Menge

             rstPositionenNeu!Preis = !Preis

             rstPositionenNeu!Mehrwertsteuer = !Mehrwertsteuer

             rstPositionenNeu!EinheitID = !EinheitID

             rstPositionenNeu.Update

             .MoveNext

         Loop

     End With

     Me.Requery

     Me.Recordset.FindFirst "RechnungID = " & lngNeueRechnungID

End Sub

Listing 1: Kopieren von Rechnungsdaten aus dem Haupt- und dem Unterformular

Um diese Prozedur anzulegen, klicken Sie in der Entwurfsansicht des Formulars frmRechnungen auf die Schaltfläche cmdRechnungKopieren und wählen dann im Eigenschaftsfenster für die Eigenschaft Beim Klicken den Eintrag [Ereignisprozedur] aus.

Wenn Sie dann rechts auf die Schaltfläche mit den drei Punkten (...) klicken, zeigt der VBA-Editor ein neues Klassenformular für den Formularcode mit der benötigten Prozedur an. Diese füllen Sie dann mit dem nachfolgend beschriebenen Code.

Die Prozedur speichert zunächst den Primärschlüsselwert des aktuellen Datensatzes im Formular in der Variablen lngAlteRechnungID und erstellt ein Recordset auf Basis der Tabelle tblRechnungen, wobei dieses allerdings keine Daten enthalten soll (Bedingung WHERE 1=2). Dieser Datensatzgruppe fügt die Prozedur mit der AddNew-Methode einen neuen Datensatz hinzu. Dann stellt sie die einzelnen Felder des neuen Datensatzes auf die entsprechenden Werte der Steuerelemente im Formular ein.

Nun speichert sie den Primärschlüsselwert des neuen Datensatzes (also den Wert des Feldes RechnungID), der eigentlich schon nach der Ausführung der AddNew-Methode vorliegt, in der Variablen lngNeueRechnungID – wir benötigen diesen Wert gleich noch für mehrere Aktionen. Schließlich speichert die Prozedur den neuen Datensatz mit der Update-Methode.

Nun füllt die Prozedur zwei weitere Recordset-Variablen mit Verweisen auf neu geöffnete Recordsets. Das erste heißt rstPositionenAlt und enthält alle Datensätze der Tabelle tblRechnungspositionen, deren Feld RechnungID den in lngAlteRechnungID gespeicherten Wert enthält.

Dies entspricht allen Datensätzen der Tabelle tblRechnungspositionen, die mit dem zuvor kopierten Datensatz der Tabelle tblRechnungen verknüpft sind. Die zweite Datensatzgruppe basiert ebenfalls auf der Tabelle tblRechnungspositionen, soll aber leer sein und dient lediglich dem Anlegen der Kopien der Rechnungspositionen der Originalrechnung. Daher stellen wir auch hier als Kriterium den Ausdruck 1=2 ein.

Nun durchläuft die Prozedur alle Datensätze des ersten Recordsets mit den Original-Rechnungspositionen. Sie legt für jede Rechnungsposition zunächst einen neuen Datensatz an. Wichtig ist hier die Zeile, die das Feld RechnungID füllt: Dieses erhält nämlich mit dem in der Variablen lngNeue­RechnungID gespeicherten Wert den Wert des Primärschlüsselfeldes des soeben angelegten Datensatzes in der Tabelle tblRechnungen. Die übrigen Feldinhalt werden einfach von den entsprechenden Feldern des aktuellen Datensatzes des Recordsets rstPositionenAlt übernommen.

Schließlich speichert die Routine den neuen Datensatz mit der Update-Methode des Recordset-Objekts und wechselt mit der MoveNext-Methode zum nächsten Datensatz – und dies wird solange wiederholt, solange die Abbruchbedingung in der Do While-Schleife (rstPositionenAlt.EOF) nicht erfüllt ist.

Schließlich zeigt die Prozedur den neu angelegten Rechnungsdatensatz im Hauptformular an, wobei erneut der in lngRechnungID gespeicherte Primärschlüsselwert des neuen Datensatzes zum Einsatz kommt.

Verknüpfte Daten per SQL kopieren

Wir wollen uns noch eine zweite Variante zum Kopieren der Datensätze ansehen. Diese basiert auf der Verwendung der INSERT INTO-Anweisung, die mit der Execute-Methode des Database-Objekts ausgeführt wird.

Dazu fügen wir dem Formular frmRechnungen eine weitere Schaltfläche namens cmdRechnungKopieren hinzu (siehe Bild 5).

Neue Schaltfläche zum Kopieren per SQL-Anweisung

Bild 5: Neue Schaltfläche zum Kopieren per SQL-Anweisung

Diese löst die Prozedur aus Listing 2 aus. Die Prozedur stellt zunächst eine die zum Kopieren des Rechnungsdatensatzes benötigte SQL-Anweisung zusammen und speichert diese in der Variablen strSQL. Diese führt die Prozedur in der folgenden Zeile mit der Methode Execute des Database-Objekts aus.

Private Sub cmdRechnungKopierenSQL_Click()

     Dim db As DAO.Database

     Dim lngRechnungID As Long

     Dim strSQL As String

     Set db = CurrentDb

     strSQL = "INSERT INTO tblRechnungen SELECT Rechnungsbetreff, Rechnungstext, Bemerkungen, KundeID FROM tblRechnungen " _

         & "WHERE RechnungID = " & Me!RechnungID

     db.Execute strSQL, dbFailOnError

     If db.RecordsAffected = 1 Then

         lngRechnungID = db.OpenRecordset("SELECT @@IDENTITY").Fields(0)

         strSQL = "INSERT INTO tblRechnungspositionen SELECT Rechnungsposition, Menge, Preis, Mehrwertsteuer, EinheitID, " _

             & lngRechnungID & " AS RechnungID FROM tblRechnungspositionen WHERE RechnungID = " & Me!RechnungID

         db.Execute strSQL, dbFailOnError

     End If

     Me.Requery

     Me.Recordset.FindFirst "RechnungID = " & lngRechnungID

End Sub

Listing 2: Kopieren der verknüpften Datensätze per INSERT INTO-Anweisung

Die folgende If...Then-Bedingung prüft mit dem Ausdruck db.RecordsAffected = 1, ob die zuvor ausgeführte Aktionsabfrage genau einen Datensatz betroffen hat. Ist dies der Fall, wurde der neue Datensatz erfolgreich angelegt und wir können mit dem Kopieren der mit dieser Rechnung verknüpften Rechnungspositionen fortfahren.

Innerhalb der If...Then-Bedingung ermittelt die Prozedur mit der Abfrage SELECT @@IDENTITY den zuletzt in der aktuellen Sitzung hinzugefügten Primärschlüsselwert. Damit ausgestattet, können wir in der folgenden Zeile die SQL-Anweisung definieren, die alle Datensätze der Tabelle tblRechnungspositionen, die mit dem zu kopierenden Datensatz der Tabelle tblRechnungen verknüpft sind, mit dem in der Variablen lngNeueRechnungID gespeicherten Wert im Fremdschlüsselfeld RechnungID als neue Datensätze in die Tabelle tblRechnungspositionen kopiert. Auch diese SQL-Anweisung führen wir mithilfe der Execute-Methode aus.

Anschließend sorgt die Requery-Methode des aktuellen Formulars noch für das Neuladen der Daten für den aktuellen Datensatz und springt dann zu dem neu angelegten Datensatz der Tabelle tblRechnungen.

Unterschiede

Der große Unterschied zwischen den beiden zuvor verwendeten Methoden zum Kopieren von verknüpften Datensätzen liegt sicher darin, dass wir statt der AddNew/Update-Methoden des Recordset-Objekts nun die Execute-Methode mit einer entsprechenden INSERT INTO-Aktionsabfrage verwendet haben.

Ein weiterer Unterschied ist, dass wir bei der DAO-Methode das neue Recordset der Tabelle tblRechnungen mit den Daten des aktuell angezeigten Datensatzes im Formular gefüllt haben:

With rstRechnungen

     .AddNew

     !Rechnungsbetreff = Me!Rechnungsbetreff

     !Rechnungstext = Me!Rechnungstext

     !Bemerkungen = Me!Bemerkungen

     !KundeID = Me!KundeID

     lngNeueRechnungID = !RechnungID

     .Update

End With

Im zweiten Beispiel mit der SQL-Aktionsabfrage haben wir die notwendige Anweisung so zusammengesetzt, dass diese die zu kopierenden Werte direkt aus der Tabelle tblRechnungen bezieht, und zwar für den Datensatz, den wir mit dem Kriterium "...WHERE RechnungID = " & Me!Rechnung ermittelt haben:

strSQL = "INSERT INTO tblRechnungen SELECT Rechnungsbetreff, Rechnungstext, Bemerkungen, KundeID FROM tblRechnungen WHERE RechnungID = " & Me!RechnungID

Wir hätten die Daten zum Einfügen in die Tabelle tblRechnungen per INSERT INTO auch direkt aus dem Formular beziehen können. Dies sieht dann, in Ausschnitten, wie in Listing 3 aus.

Private Sub cmdRechnungKopierenSQL_Click()

     ...

     strSQL = "INSERT INTO tblRechnungen(Rechnungsbetreff, Rechnungstext, Bemerkungen, Rechnungsdatum, KundeID) VALUES('" _

         & Me!Rechnungsbetreff & "', '" & Me!Rechnungstext & "', '" & Me!Bemerkungen & "', " & SQLDatum(Me!Rechnungsdatum) & ", " _

         & Me!KundeID & ")"

     ...

End Sub

Listing 3: INSERT INTO-Anweisung mit VALUES-Schlüsselwort

Dort verwenden wir die VALUES-Syntax, bei der wir hinter INSERT INTO zunächst in Klammern die Liste der Felder angeben, die gefüllt werden sollen, und dann – wiederum in Klammern – die tatsächlichen Werte als kommaseparierte Liste hinterlegen. Eine solche Anweisung sieht, mit konkreten Werten gefüllt, beispielsweise wie folgt aus:

INSERT INTO tblRechnungen(Rechnungsbetreff, Rechnungstext, Bemerkungen, Rechnungsdatum, KundeID) VALUES('Programmierung "Access-Tools"', 'Sehr geehrter Herr Müller, hiermit stellen wir folgende Positionen in Rechnung:', 'Mit freundlichen Grüßen André Minhorst', #2014/01/14#, 2)

Nun wundern Sie sich vielleicht, was aus der Datumsangabe 14.1.2014 geworden ist.

Damit ein solches Datum von SQL verarbeitet werden kann, müssen wir es entsprechend aufbereiten – und zwar beispielsweise durch die Darstellung in der Form #2014/1/14#.

Dies erreichen Sie durch die Verwendung einer kleinen Hilfsfunktion namens SQLDatum. Diese sieht wie in Listing 4 aus.

Public Function SQLDatum(varDate As Variant)

     SQLDatum = Format(varDate, "\#yyyy\/mm\/dd#")

End Function

Listing 4: Hilfsfunktion zum Formatieren von Datumsangaben

Kopieren von Daten in m:n-Beziehungen

Hinter dem Kopieren von Daten aus m:n-Beziehungen verbirgt sich eine ganz ähnliche Technik wie für das Kopieren von 1:n-Daten, was ja auch logisch ist. Wir betrachten dies für die drei Tabellen tblBestellungen, tblBestellpositionen und tblArtikel:

Die Artikel im n-Teil der Beziehung werden auch im Zusammenhang mit der Bestellung nicht berührt, sondern nur referenziert - das heißt, dass man die in der Tabelle tblArtikel enthaltenen Datensätze auch nicht mitkopieren muss.

Sie würden also in der oben genannten Konstellation den Datensatz der Tabelle tblBestellungen kopieren und alle Datensätze der Tabelle tblBestellpositionen, die mit diesem Datensatz verknüpft sind.

Die Datensätze der Tabelle tblArtikel braucht man in diesem Fall nicht zu kopieren, da diese wohl kaum zum Zwecke der Anpassung dupliziert werden sollen.

Zusammenfassung und Ausblick

Das Kopieren verknüpfter Daten ist manchmal recht hilfreich, vor allem, wenn es um das Kopieren von Datensätzen geht, die man in der gleichen oder einer leicht abgewandelten Form benötigt – also zumindest so, dass es weniger Aufwand bedeutet, die Daten zu kopieren und anzupassen, als diese manuell zu erstellen.