PDF ansehen - Access [basics]

ACCESS
Datenzugriff programmieren
Verknüpfte Daten kopieren
basicS
Verknüpfte Daten kopieren
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).
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
www.access-basics.de
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.
Bild 1: Beziehung zwischen Kunden und Anreden
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
Bild 2: Rechnungen und Rechnungspositionen
Anders sieht es aus,
wenn die verknüpfte Tabelle Daten
enthält, die erst in
Seite 15
ACCESS
Datenzugriff programmieren
Verknüpfte Daten kopieren
basicS
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.
Bild 3: Bestellungen, Bestellpositionen und Artikel
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
www.access-basics.de
Bild 4: Formular zum Erstellen individueller Rechnungen
in diesem Artikel behandelte Beispiel
(siehe Bild 3).
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.
Wenn Sie nun für den gleichen Kunden eine neue Rechnung erstellen,
können Sie dies natürlich manuell
Seite 16
ACCESS
Datenzugriff programmieren
Verknüpfte Daten kopieren
basicS
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 Da-
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
www.access-basics.de
Seite 17
ACCESS
Datenzugriff programmieren
Verknüpfte Daten kopieren
basicS
ten mit AddNew/Update. Dazu ist
offensichtlich eine Menge Code
erforderlich, aber wir wollen ja auch
gleich die Daten aus zwei verknüpften Tabellen duplizieren.
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.
der Kopien der Rechnungspositionen
der Originalrechnung. Daher stellen
wir auch hier als Kriterium den Ausdruck 1=2 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 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.
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
Schließlich speichert die Routine den
neuen Datensatz mit der UpdateMethode des Recordset-Objekts und
wechselt mit der MoveNext-Methode
zum nächsten Datensatz – und dies
wird solange wiederholt, solange die
Abbruchbedingung in der Do WhileSchleife (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
Bild 5: Neue Schaltfläche zum Kopieren per SQL-Anweisung
www.access-basics.de
Wir wollen uns noch eine zweite Variante zum Kopieren der Datensätze
ansehen. Diese basiert auf der Ver-
Seite 18
ACCESS
Datenzugriff programmieren
Verknüpfte Daten kopieren
basicS
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
wendung 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).
Diese löst die Prozedur aus Listing 2
aus. Die Prozedur stellt zunächst
eine die zum Kopieren des Rechnungsdatensatzes benötigte SQLAnweisung 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.
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.
www.access-basics.de
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.
tensä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
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 Da-
!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 tblRechnun-
Seite 19
ACCESS
Datenzugriff programmieren
Verknüpfte Daten kopieren
basicS
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
gen bezieht, und zwar für den
Datensatz, den wir mit dem Kriterium "...WHERE RechnungID = " &
Me!Rechnung ermittelt haben:
strSQL = "INSERT INTO tblRechnungen µ
SELECT Rechnungsbetreff, µ
de 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.
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.
Dort verwenden wir die VALUESSyntax, 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 folgen-
www.access-basics.de
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#.
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.
Dies erreichen Sie durch die Verwendung einer kleinen Hilfsfunktion
namens SQLDatum. Diese sieht wie
in Listing 4 aus.
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.
Kopieren von Daten in m:nBeziehungen
Zusammenfassung und
Ausblick
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:
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.
Die Artikel im n-Teil der Beziehung
werden
auch im
Public Function SQLDatum(varDate As Variant)
SQLDatum = Format(varDate, "\#yyyy\/mm\/dd#")
ZusamEnd Function
menhang
Listing 4: Hilfsfunktion zum Formatieren von Datumsangaben
mit der
Seite 20