<\/p>\n
änderungen an den Daten in den Tabellen einer Datenbank kann man auf verschiedenste Arten durchführen – zum Beispiel über die Benutzeroberfläche. Gelegentlich werden Sie jedoch auch Daten per VBA ändern wollen. In diesem Falle gibt es eine ganze Reihe von Varianten, die normalerweise mit den Methoden der DAO-Bibliotheken abgebildet werden: Das öffnen eines Recordsets und ändern der enthaltenen Daten mit AddNew\/Edit und Update, das Zusammenstellen einer SQL-Aktionsabfrage, die dann mit der Execute-Methode des Database-Objekts ausgeführt wird, oder der Einsatz gespeicherter Aktionsabfragen, die man mit der Execute-Methode des QueryDef-Objekts startet. Wir schauen uns die verschiedenen Varianten an und optimieren diese.<\/b><\/p>\n
Performance und Stil<\/b><\/p>\n
Der Grund für diesen Beitrag ist offensichtlich: Die verschiedenen Methoden zum ändern von Daten unterscheiden sich stark in der Performance. Wenn Sie eine Schleife über eine Datensatzgruppe durchlaufen und daraus entnommene Daten mit den DAO-Methoden AddNew <\/b>und Update <\/b>zu einem weiteren Recordset hinzufügen, dauert das schlicht und einfach länger als wenn Sie die gesamte Prozedur in einer einzigen Aktionsabfrage unterbringen.<\/p>\n
Und das ist nicht nur das Bild, das man bei der Unterstützung anderer Entwickler vorfindet, sondern man selbst neigt ebenso dazu, erstmal den scheinbar einfacheren Weg über DAO zu wählen, bevor man auf den ersten Blick kompliziert erscheinende Aktionsabfragen zusammenstellt.<\/p>\n
Und damit landen wir gleich beim anderen Aspekt: In vielen Fällen kostet das Entwickeln einer eleganten, performanten Lösung schlicht und einfach mehr Zeit als das stumpfe Herunterprogrammieren bekannter Codestrukturen – zumindest wenn man nicht über ausreichend Programmiererfahrung verfügt.<\/p>\n
Dann hat man nämlich oft genug erst Datensatz für Datensatz mit DAO durchpflügt, bevor man den ersten Entwurf dann in einem oder mehreren Schritten zu einer zufriedenstellenden Lösung weiterentwickelt hat, die sowohl den Anspruch an die Performance als auch an die Eleganz befriedigt.<\/p>\n
Eine solche Entwicklung können Sie in der Lösung zum Beitrag Datenmodelle vergleichen <\/b>(www.access-im-unternehmen.de\/916<\/b>) nachvollziehen. Im vorliegenden Beitrag schauen wir uns ein etwas einfacheres Beispiel an und führen die Schritte vom ersten Ansatz bis zur perfekten Lösung durch.<\/p>\n
Beispiel: Kundendaten archivieren<\/b><\/p>\n
Im Beispiel dieses Beitrags wollen wir einfach bestimmte Kundendaten in eine andere Tabelle kopieren – beispielsweise, um diese zu archivieren. Die Kundendaten sollen nach bestimmten Kriterien ausgewählt werden – beispielsweise nach dem Zeitpunkt der letzten Bestellung. Liegt diese mehr als ein Jahr zurück, soll der Kunde in eine entsprechende Tabelle kopiert werden.<\/p>\n
Außerdem soll die neue Tabelle noch ein weiteres Feld mit dem Datum der Archivierung enthalten (s. Bild 1).<\/p>\n
![\"Quell-](\"..\/fileadmin\/_temp_\/2013_06\/pic_911_001.png\")
<\/p>\n
Bild 1: Quell- und Zieltabelle<\/span><\/b><\/p>\n Daten mit bestimmten Kriterien aus einer Tabelle in eine weitere Tabelle kopieren, die überdies noch um weitere Daten ergänzt werden soll – das hört sich sehr kompliziert an. Also gehen wir das Ganze mal schön langsam Schritt für Schritt an – so, dass man zu jeder Zeit kontrollieren kann, was dort geschieht.<\/p>\n Start der Archivierung<\/b><\/p>\n Die Archivierung soll über Schaltflächen eines Formulars namens frmKundenArchivieren<\/b> gestartet werden (s. Bild 2).<\/p>\n Bild 2: Formular mit Schaltflächen zum Starten der verschiedenen Archivierungen<\/span><\/b><\/p>\n Dieses Formular ruft die verschiedenen Varianten der Archivierung auf und übergibt dabei jeweils das im Textfeld txtStichtag <\/b>gespeicherte Datum an die Funktionen<\/b>, für die erste Schaltfläche etwa so:<\/p>\n Die „sichere“ Variante<\/b><\/p>\n Wer noch nicht erfahren im Umgang mit Access ist, hat vielleicht noch nicht mit SQL gearbeitet und wird dementsprechend zunächst eine Abfrage wie oben beschrieben erstellen.<\/p>\n Dann wird er eine VBA-Funktion bauen, die wie die in Listing 1 aussieht. Sie sucht nach Kunden, die seit dem per Parameter übergebenen Stichtag keine Bestellung mehr durchgeführt haben und archiviert werden sollen, und liefert die Anzahl der betroffenen Datensätze zurück.<\/p>\n Listing 1: Die „sichere“ Variante zum Kopieren von Daten<\/span><\/b><\/p>\n Die Funktion erstellt ein Database<\/b>-Objekt namens db <\/b>sowie zwei Datensatzgruppen namens rstQuelle <\/b>und rstZiel<\/b>. Das Recordset<\/b>-Objekt rstQuelle <\/b>wird mit einem Verweis auf alle Datensätze der Tabelle tblKunden <\/b>gefüllt, das Recordset<\/b>-Objekt rstZiel <\/b>mit einer Datenherkunft, die auf der Zieltabelle tblKunden_Archiv <\/b>basiert, aber wegen des Kriteriums 1=2 <\/b>keine Datensätze enthält.<\/p>\n Die Prozedur durchläuft nun alle Datensätze des Recordsets tblKunden<\/b>. Dabei ermittelt sie zunächst mit dem Aufruf von DLookup <\/b>das aktuellste Datum einer Bestellung aus der Tabelle tblBestellungen <\/b>für die Datensätze, deren Feld KundeID <\/b>mit dem Kunden des aktuellen Datensatzes aus rstQuelle <\/b>übereinstimmt.<\/p>\n Für den Fall, dass für den Kunden noch gar keine Bestellung vorliegt, soll datLetzteBestellung <\/b>den Wert 0 <\/b>erhalten, was die Nz<\/b>-Funktion mit entsprechendem zweiten Parameter bewerkstelligt.<\/p>\n Die folgende If…Then<\/b>-Bedingung prüft, ob das in datLetzteBestellung <\/b>gespeicherte Datum kleiner als datStichtag <\/b>ist. Nur in diesem Fall geht es weiter, und zwar mit einer weiteren Prüfung: Wenn der Kunde bereits in der Archivtabelle gespeichert ist, soll dieser nicht erneut gespeichert werden.<\/p>\n Erst danach wird der neue Datensatz angelegt, und zwar mit der AddNew<\/b>-Methode des Recordset<\/b>-Objekts rstZiel<\/b>. Die folgenden Anweisungen tragen jeweils den Wert eines Feldes der Quelltabelle in das entsprechende Feld der Zieltabelle ein.<\/p>\n Danach fügt die Funktion noch dem Feld ArchiviertAm <\/b>das aktuelle Datum und die aktuelle Uhrzeit hinzu. Die Update<\/b>-Methode sorgt für das Speichern des neu angelegten Datensatzes in der Tabelle tblKunden_Archiv<\/b>.<\/p>\n Schließlich erhöht die Prozedur den Wert der Variablen intAnzahl<\/b>, welche die Anzahl der übertragenen Datensätze zählen soll, um 1<\/b>. Die MoveNext<\/b>-Methode bewegt den Datensatzzeiger zum nächsten Datensatz, die Loop<\/b>-Anweisung lässt die Funktion mit einem weiteren Durchlauf der Do While<\/b>-Schleife beginnen.<\/p>\n Schließlich liefert die Funktion die in intAnzahl <\/b>gespeicherte Anzahl betroffener Datensätze zurück.<\/p>\n Wenn Sie in Betracht ziehen, dass eine solche Vorgehensweise auf große Datenmengen angewendet wird, sind eine Menge Codezeilen abzuarbeiten, bis das gewünschte Resultat erreicht wird. Also kümmern wir uns darum, dies zu optimieren. Gleichwohl ist zu erwähnen, dass die hier eingesetzte Vorgehensweise nicht völlig abwegig ist. Erstens kommt sie fast komplett ohne den Einsatz von SQL-Anweisungen aus, was für viele Einsteiger erst in späteren Schritten folgt. Somit kommt man mit den grundlegenden DAO-Anweisungen sowie Domänenfunktionen über die Runden.<\/p>\n Das Entscheidende für den Entwickler dieser Zeilen mag aber sein, dass sich zu jeder Zeit im Debug-Modus beobachten lässt, was dort geschieht. Dies ist beim Einsatz etwa von SQL-Aktionsabfragen nur eingeschränkt möglich.<\/p>\n Optimierung 1: DMax auflösen<\/b><\/p>\n Die obige Funktion muss wirklich viel Arbeit leisten, weil sie jeden einzelnen Datensatz der Herkunftstabelle tblKunden <\/b>durchläuft und diese dahingehend prüft, wie lange die letzte Bestellung des Kunden zurückliegt und ob dieser Datensatz bereits in der Tabelle tblKunden_Archiv <\/b>gespeichert ist. Dabei ist für jeden Datensatz der Aufruf der DMax<\/b>-Funktion fällig.<\/p>\n Wir wollen in kleinen Schritten optimieren und versuchen zunächst, die DMax<\/b>-Funktion aus der Schleife herauszunehmen. Die Datenherkunft besteht bisher aus der Tabelle tblKunden<\/b>. Deren Feld KundeID <\/b>verwenden wir, um per DMax<\/b> das Datum der letzten Bestellung zu ermitteln.<\/p>\n Wie bekommen wir nun die DMax<\/b>-Anweisung aus der Do While<\/b>-Schleife heraus Der erste Schritt dazu ist einfach: Wir erstellen einfach eine Abfrage auf Basis der Tabelle tblKunden <\/b>und fügen ein weiteres Feld hinzu, welches das Datum der neuesten Bestellung ermittelt – dies wiederum durch den Einsatz der DMax<\/b>-Funktion. Der Ausdruck für dieses Feld sieht so aus, die komplette Abfrage qryKundenNachBestelldatum <\/b>finden Sie in Bild 3:<\/p>\n Bild 3: Abfrage basierend auf der Tabelle tblKunden und der neuesten Bestellung für diesen Kunden<\/span><\/b><\/p>\n Der Clou hierbei ist, dass die DMax<\/b>-Funktion die ID des Kunden, für den die neueste Bestellung ermittelt werden soll, aus dem Feld KundeID <\/b>der Datenherkunft bezieht.<\/p>\n Die Abfrage qryKundenNachBestelldatum <\/b>nutzen wir nun als Datenherkunft für die zu übertragenen Daten. Das Feld NeuesteBestellung <\/b>nutzen wir nun direkt als Kriterium der If…Then<\/b>-Bedingung, anstatt diesen Wert erst noch in der Prozedur per DMax <\/b>ermitteln zu müssen (s. Listing 2).<\/p>\n <\/p>\n Listing 2: Optimierung der Schleife durch übertragen von DMax in die Datenherkunft <\/span><\/b><\/p>\n Zeit messen<\/b><\/p>\n Nachdem wir nun eine erste vermeintliche Verbesserung angewendet haben, wollen wir natürlich auch wissen, ob sich dies auf die Performance auswirkt.<\/p>\n Die Zeit zwischen zwei Ereignissen messen Sie unter Windows am genauesten mit den beiden API-Funktionen QueryPerformanceFrequency <\/b>und QueryPerformanceCounter<\/b>. Die Erste liefert die Frequenz, die Zweite den Zählerstand des Prozessors. Wenn Sie diesen einmal beim Start der Messung einlesen und einmal zum Ende, brauchen Sie nur noch die Differenz durch die Frequenz zu teilen und erhalten die Zeit in Sekunden.<\/p>\n In Listing 3 werden zunächst die beiden API-Funktionen deklariert, außerdem drei Currency<\/b>-Variablen zum Speichern der Frequenz, des Zählerstands beim Start und des Zählerstands nach Beenden der zu messenden Vorgänge. Die Prozedur, die beim Anklicken der Schaltflächen zum Starten der verschiedenen Varianten unseres Vorgangs ausgelöst wird, erledigt nun folgende Aufgaben:<\/p>\n Listing 3: Einrichtung zum Messen der Zeit<\/span><\/b><\/p>\n Diese Zeilen fügen wir jeder Schaltfläche hinzu, nur der Aufruf der Funktion sowie das Textfeld für die Ausgabe der Zeit ändert sich. Wie Bild 4 zeigt, hat sich die erste Maßnahme durchaus gelohnt: Wir sparen rund 10% der Zeit gegenüber der ersten Variante ein.<\/p>\n Bild 4: Ergebnis der ersten beiden Varianten<\/span><\/b><\/p>\n Optimierung II: <\/p>\n Nun führen wir allerdings immer noch für jeden Datensatz einen Aufruf der DMax<\/b>-Funktion aus. Wie können wir dies verhindern Eine Möglichkeit wäre, statt der DMax<\/b>-Funktion mit einer Unterabfrage zu arbeiten. Diese formulieren wir in einer neuen Abfrage namens qryKundenNachBestelldatumMitGruppierung <\/b>wie folgt (siehe auch Bild 5):<\/p>\n Bild 5: Abfrage mit Unterabfrage<\/span><\/b><\/p>\n Nach dem Anlegen einer neuen Schaltfläche im Formular frmKundenArchivieren <\/b>und dem Kopieren der Funktion Datenkopieren_II<\/b> in eine neue Funktion namens Datenkopieren_III <\/b>sehen wir uns an, was diese Optimierung bringt.<\/p>\n Und das Ergebnis ist beeindruckend: Wir sparen rund 97% gegenüber der Startkonfiguration! Aber Vorsicht: Zum Glück lassen wir immer noch die Anzahl der gespeicherten Datensätze mitlaufen. Und dieses gibt nun an, statt der vorhandenen 1456 Artikel nur noch 89 Datensätze archiviert zu haben. Haben wir vergessen, die schon archivierten Datensätze vorher zu löschen Nein. Aber wo liegt der Fehler Ganz einfach: Im Gegensatz zum Ausdruck <\/p>\n liefert die Unterabfrage nicht den Wert 0<\/b>, sondern den Wert Null<\/b>, wenn noch keine Bestellung für den Kunden vorliegt. Dies müssten wir nun in Form einer IIf<\/b>-Funktion oder ähnlich in der Abfrage unterbringen.<\/p>\n Vorerst wollen wir uns damit begnügen, den Vergleich des Feldes NeuesteBestellung <\/b>in der Prozedur mit der Nz<\/b>-Funktion zu versehen, sodass im Falle eines Null<\/b>-Wertes der Wert 0 <\/b>angenommen wird:<\/p>\n Nach einem weiteren Test liefert dies immerhin eine Zeit von ca. 2,1 Sekunden und somit eine Verbesserung von knapp 30%.<\/p>\n Optimierung III: AddNew durch INSERT INTO ersetzen<\/b><\/p>\n Bei dieser Optimierung wollen wir die AddNew<\/b>-Methode, die Update<\/b>-Methode und die vielen einzelnen Anweisungen zum Zuweisen der Feldwerte von der einen an die andere Tabelle ersetzen. Wir arbeiten also weiter mit der Abfrage qryKundenNachBestellungMitGruppierung<\/b>, aber ersetzen eine ganze Reihe Anweisungen durch eine einzige.<\/p>\n Das Ergebnis sieht wie in Listing 4 aus. Die Variable für das Recordset rstZiel<\/b> und alle damit verknüpften Anweisungen fallen hier weg. Dafür kommt eine etwas längliche INSERT INTO<\/b>-Anweisung hinzu, die mit der Execute<\/b>-Methode des Database<\/b>-Objekts db <\/b>ausgeführt wird.<\/p>\n Listing 4: AddNew und Update durch Execute und INSERT INTO ersetzen<\/span><\/b><\/p>\n Eigentllich denkt man, dass dies schneller als die vorherige Variante sein sollte. Allerdings müssen wir in der Zeile, welche die Anweisung zusammenstellt und ausführt, drei VBA-Funktionen unterbringen:<\/p>\n Im Ergebnis führt diese änderung im Vergleich zur vorherigen Version zu einer deutlich schlechteren Performance, die aber immer noch etwas besser als die der nicht optimierten Version ist. Aber: Es handelt sich nur um einen Zwischenschritt!<\/p>\n Am SQL-Ausdruck können wir höchstens eine kleine änderung vornehmen. In diesem Fall würden wir das per VBA erzeugte und formatierte Datum durch die reine Now()<\/b>-Funktion ersetzen. Anscheinend wirkt sich diese Optimierung jedoch nicht entscheidend aus.<\/p>\n Optimierung IV: Kriterium in Abfrage bringen<\/b><\/p>\n Bislang prüfen wir noch für jeden einzelnen Datensatz innerhalb der Do While<\/b>-Schleife, ob das über die Gruppierung ermittelte Feld NeuesteBestellung <\/b>kleiner als der Stichtag ist, oder füllen es, wenn es leer ist, mit dem Wert 0<\/b> – was auf jeden Fall kleiner als der Stichtag ist (außer, der Stichtag liegt vor dem 1.1.1900<\/b>).<\/p>\n Diese Prüfung sollten wir ebenfalls noch in die Abfrage verlegen. Aber wir hatten ja schon zuvor das Problem, dass wir das Ergebnis der Gruppierung noch auf den Wert Null <\/b>hätten prüfen und dieses gegebenenfalls durch den Wert 0 <\/b>hätten ersetzen müssen, um dieses mit dem Stichtag zu vergleichen.<\/p>\n Es gibt jedoch eine recht elegante Lösung, wenn es darum geht, Kunden ohne Bestellung dennoch auszuwählen: Wir vergleichen das Ergebnis der Gruppierung, also das Feld NeuesteBestellung<\/b>, einfach mit dem Wert Null<\/b>. Ist NeuesteBestellung <\/b>leer, ist die Bedingung wahr und der Kunde kann archiviert werden. Wenn anderenfalls ein Datum für die neueste Bestellung vorliegt, verknüpfen wir den Vergleich mit dem Stichtag einfach per Oder <\/b>mit dem ersten Kriterium.<\/p>\n Nun müssen wir nur noch den Vergleichswert, also den Stichtag, in die Abfrage bekommen. Dies erledigen wir mit einem Parameter namens [Stichtag]<\/b>, den wir wie in Bild 6 in die Abfrage integrieren.<\/p>\n Bild 6: Vergleich des neuesten Bestelldatums mit dem Stichtag<\/span><\/b><\/p>\n An der Prozedur sind auch einige Anpassungen nötig (s. Listing 5). Der Grund ist, dass wir der Abfrage mitteilen müssen, welchen Wert sie für den Parameter [Stichtag] <\/b>einsetzen soll. Dazu benötigen wir zwei weitere Deklarationen, nämlich für das QueryDef<\/b>-Objekt qdf <\/b>und das Parameter<\/b>-Objekt prm<\/b>.<\/p>\n Listing 5: Auslagern des Vergleichskriteriums in die Abfrage<\/span><\/b><\/p>\n Die neue Version der Abfrage, qryKundenNachBestelldatumMitStichtag<\/b>, referenzieren Sie nun zunächst mit der Variablen qdf<\/b>.<\/p>\n Für diese legen Sie ein neues Parameter<\/b>-Objekt auf Basis des Parameters Stichtag <\/b>an und referenzieren es mit prm<\/b>. Schließlich führen Sie die mit qdf <\/b>referenzierte Abfrage mit der OpenRecordset<\/b>-Methode aus und erhalten zuvor ein Recordset, das Sie dann wie zuvor durchlaufen können.<\/p>\n Performancemäßig bringt uns dies eine Verbesserung um rund zehn Prozent gegenüber der nicht optimierten Variante.<\/p>\n Hier ist allerdings anzumerken, dass wir immer noch den hemmenden Einsatz der INSERT INTO<\/b>-Aktionsabfrage mit uns herumtragen.<\/p>\n Parameter als Datum deklarieren<\/b><\/p>\n Beim Testen dieser Optimierung stellten wir fest, dass zunächst nicht alle 1.456 Datensätze berücksichtigt wurden, sondern ein paar weniger. Dies stellt sich als Problem mit der Parameter-Deklaration heraus. Wir hatten diesen zunächst nicht deklariert, weshalb er unser Testdatum (5.11.2013<\/b>) als Text verarbeitet hat.<\/p>\n Die Abfrage erkennt offenbar erst beim Durchführen des Vergleichs, dass der Parameter mit einem Datumsfeld verglichen werden soll, und konvertiert dieses entsprechend – allerdings wurde hier aus dem 5.11.2013 <\/b>der 11.5.2013<\/b>, was auch genau zu der falsch gelieferten Anzahl Datensätze passt. Und in der Tat lieferte die Abfrage des Parameter-Datentyps mit der folgenden Anweisung den Wert 8<\/b>, was der Konstanten vbString <\/b>entspricht.<\/p>\n Sie müssen also den Datentyp des Parameters im Abfrageentwurf auf Datum\/Uhrzeit <\/b>einstellen (s. Bild 7).<\/p>\n Bild 7: Einstellen des Datentyps des Parameters Stichtag<\/span><\/b><\/p>\n Optimierung V: Prüfung auf bestehende KundeID-Werte in Abfrage verlagern<\/b><\/p>\n Nun prüfen wir innerhalb der Schleife noch mit einer DLookup<\/b>-Abfrage, ob sich in der Tabelle tblKunden_Archiv <\/b>bereits ein Datensatz befindet, der den gleichen Wert im Feld KundeID <\/b>aufweist wie der Datensatz, der nun hinzugefügt werden soll.<\/p>\n Das Entfernen der DMax<\/b>-Funktion weiter oben hat die Performance bereits stark optimiert, also versuchen wir auch die DLookup<\/b>-Funktion aus der VBA-Funktion herauszubekommen.<\/p>\n Im Grunde ist das kein Problem: Wir müssen nur eine entsprechende Unterabfrage als Kriterium der Abfrage hinzufügen. Diese sieht wie folgt aus:<\/p>\n Die aktuelle Version der Abfrage, die diesmal qryKundenNachBestelldatumMitVergleichAufKundeID <\/b>heißt, sieht nun wie in Bild 8 aus.<\/p>\n Bild 8: Entwurf der Abfrage qryKundenNachBestelldatumMitVergleichAufKundeID mit Unterabfrage<\/span><\/b><\/p>\n Auch hier ist wieder eine kleine Anpassung des VBA-Codes nötig. Dies äußert sich darin, dass wir die letzte If…Then<\/b>-Bedingung aus der Schleife entfernen können.<\/p>\n Diese enthält nun nur noch die Anweisung zum Schreiben des aktuellen Datensatzes in die Tabelle tblKunden_Archiv<\/b> und die Erhöhung des Zählers intAnzahl<\/b> (s. Listing 6).<\/p>\n Listing 6: Auch die letzte If…Then-Bedingung haben wir noch aus der Abfrage entfernt.<\/span><\/b><\/p>\n Und nun wird es interessant: Wie wirkt sich diese Optimierung auf die Performance aus Das Ergebnis ist hervorragend: Die neue Version benötigt 70% weniger Zeit als die nicht optimierte Variante.<\/p>\n Optimierung VI: Do While entfernen<\/b><\/p>\n Nun gehen wir den letzten Performancefresser an: die Do While<\/b>-Schleife mit dem x-fachen Aufruf der INSERT INTO<\/b>-Abfrage. Wir haben mittlerweile alle weiteren Prüfungen und Informationen in die Abfrage übertragen, welche die Daten zum Einfügen in die Tabelle tblKunden_Archiv <\/b>liefert. Was sollte uns noch davon abhalten, aus dieser Auswahlabfrage einfach eine Aktionsabfrage zu machen, die ihre Daten einfach direkt in die Zielabfrage schreibt – ohne Umweg über eine Do While<\/b>-Schleife und eine per VBA generierte INSERT INTO<\/b>-Anweisung Sie ahnen es: Davon hält uns nichts ab!<\/p>\n Also, ans Werk: Wir erstellen eine neue Abfrage auf Basis der Abfrage qryKundenNachBestelldatumMitVergleichAufKundeID<\/b>, die wir qryKundenNachBestelldatumMitInsertInto <\/b>nennen.<\/p>\n öffnen Sie diese Abfrage im Entwurf und wählen Sie den Kontextmenü-Eintrag Abfragetyp|Anfügeabfrage…<\/b> aus. Legen Sie die Tabelle tblKunden_Archiv <\/b>als Zieltabelle fest. Access fügt nun eine weitere Zeile namens Anfügen an: <\/b>zum Entwurfsraster der Abfrage hinzu. Die meisten Zielfelder werden dort automatisch eingetragen. Allein das Feld ArchiviertAm <\/b>müssen Sie noch vollständig hinzufügen, und zwar mit folgendem Ausdruck:<\/p>\n Dies ermittelt dynamisch das aktuelle Datum und die aktuelle Uhrzeit und liefert diese unter dem Feldnamen ArchiviertAm<\/b> aus. Wählen Sie auch für dieses Feld ein Zielfeld aus, und zwar ArchiviertAm<\/b>. Die Abfrage sieht nun etwa wie in Bild 9 aus.<\/p>\n Bild 9: Die endgültige Fassung der Abfrage wird als INSERT INTO-Aktionsabfrage implementiert.<\/span><\/b><\/p>\n Den Code können Sie natürlich weiter entschlacken. Die Do While<\/b>-Schleife benötigen wir ebenso wenig wie die Execute<\/b>-Methode zum Ausführen der INSERT INTO<\/b>-Anweisungen.<\/p>\n Diese weichen dem schlichten Aufruf der Methode Execute <\/b>des QueryDef<\/b>-Objekts qdf<\/b>. Auch die Zählervariable intAnzahl <\/b>ist nicht mehr vorhanden – die Anzahl der betroffenen Datensätze liefert nunmehr direkt die Eigenschaft RecordsAffected <\/b>des QueryDef<\/b>-Objekts (s. Listing 7).<\/p>\n![\"Formular](\"..\/fileadmin\/_temp_\/2013_06\/pic_911_011.png\")
<\/p>\nPrivate Sub <\/span>cmd1_Click()\r\n Dim <\/span>intAnzahl As Integer<\/span>\r\n intAnzahl = Datenkopieren_ DAOMitAbfrage(Me!txtStichtag)\r\n MsgBox<\/span> intAnzahl _\r\n & \" Datensätze archiviert\"\r\nEnd Sub<\/span><\/pre>\nPublic Function <\/span>Datenkopieren_DAOMitAbfrage(datStichtag As Date<\/span>) As Integer<\/span>\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>rstQuelle As <\/span>DAO.Recordset\r\n Dim <\/span>rstZiel As <\/span>DAO.Recordset\r\n Dim <\/span>datLetzteBestellung As Date<\/span>\r\n Dim <\/span>intAnzahl As Integer<\/span>\r\n Set<\/span> db = CurrentDb\r\n Set<\/span> rstQuelle = db.OpenRecordset(\"SELECT * FROM tblKunden\", dbOpenDynaset)\r\n Set<\/span> rstZiel = db.OpenRecordset(\"SELECT * FROM tblKunden_Archiv WHERE 1 = 2\", dbOpenDynaset)\r\n Do While<\/span> Not<\/span> rstQuelle.EOF\r\n datLetzteBestellung = Nz(DMax(\"Bestelldatum\", \"tblBestellungen\", _\r\n \"KundeID = \" & rstQuelle!KundeID), 0)\r\n If <\/span>datLetzteBestellung < datStichtag Then<\/span>\r\n If <\/span>IsNull(DLookup(\"KundeID\", \"tblKunden_Archiv\", \"KundeID = \" & rstQuelle!KundeID)) Then<\/span>\r\n With<\/span> rstZiel\r\n .AddNew<\/span>\r\n !KundeID = rstQuelle!KundeID\r\n !KundenCode = rstQuelle!KundenCode\r\n !Firma = rstQuelle!Firma\r\n !Kontaktperson = rstQuelle!Kontaktperson\r\n !Strasse = rstQuelle!Strasse\r\n !Ort = rstQuelle!Ort\r\n !Region = rstQuelle!Region\r\n !PLZ = rstQuelle!PLZ\r\n !Land = rstQuelle!Land\r\n !Telefon = rstQuelle!Telefon\r\n !Telefax = rstQuelle!Telefax\r\n !ArchiviertAm = Now\r\n .Update\r\n intAnzahl = intAnzahl + 1\r\n End With<\/span>\r\n End If<\/span>\r\n End If<\/span>\r\n rstQuelle.MoveNext<\/span>\r\n Loop<\/span>\r\n Datenkopieren_DAOMitAbfrage = intAnzahl\r\nEnd Function<\/span><\/pre>\n![\"Abfrage](\"..\/fileadmin\/_temp_\/2013_06\/pic_911_003.png\")
<\/p>\nNeuesteBestellung: Nz(DomMax(\"Bestell-datum\";\"tblBestellungen\";\"KundeID = \" _\r\n & [KundeID]);0)<\/pre>\n
Public Function <\/span>Datenkopieren_II(datStichtag As Date<\/span>) As Integer<\/span>\r\n ...\r\n Set<\/span> rstQuelle = db.OpenRecordset(\"SELECT * FROM qryKundenNachBestelldatum\", dbOpenDynaset)\r\n Set<\/span> rstZiel = db.OpenRecordset(\"SELECT * FROM tblKunden_Archiv WHERE 1 = 2\", dbOpenDynaset)\r\n Do While<\/span> Not<\/span> rstQuelle.EOF\r\n If <\/span>rstQuelle!NeuesteBestellung < datStichtag Then<\/span>\r\n ...\r\n Datenkopieren_II = intAnzahl\r\nEnd Function<\/span><\/pre>\nPrivate <\/span>Declare Function QueryPerformanceCounter Lib \"Kernel32\" (X As Currency<\/span>) As Long<\/span>\r\nPrivate <\/span>Declare Function QueryPerformanceFrequency Lib \"Kernel32\" (Y As Currency<\/span>) As Long<\/span>\r\nDim <\/span>curFreq As Currency<\/span>\r\nDim <\/span>curStart As Currency<\/span>\r\nDim <\/span>curEnde As Currency<\/span>\r\nPrivate Sub <\/span>cmd1_Click()\r\n Dim <\/span>intAnzahl As Integer<\/span>\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Set<\/span> db = CurrentDb\r\n db.Execute \"DELETE FROM tblKunden_Archiv\", dbFailOnError\r\n QueryPerformanceFrequency curFreq\r\n QueryPerformanceCounter curStart\r\n intAnzahl = Datenkopieren_I(Me!txtStichtag)\r\n QueryPerformanceCounter curEnde\r\n Me!txtZeitI = (curEnde - curStart) \/ curFreq\r\n MsgBox<\/span> intAnzahl & \" Datensätze archiviert\"\r\nEnd Sub<\/span><\/pre>\n\n
![\"Ergebnis](\"..\/fileadmin\/_temp_\/2013_06\/pic_911_004.png\")
<\/p>\n![\"Abfrage](\"..\/fileadmin\/_temp_\/2013_06\/pic_911_005.png\")
<\/p>\nNeuesteBestellung: (SELECT TOP 1 Bestelldatum FROM tblBestellungen \r\nWHERE tblBestellungen.KundeID = tblKunden.KundeID)<\/pre>\n
NeuesteBestellung: Nz(DomMax(\"Bestell-datum\";\"tblBestellungen\";\"KundeID = \" & [KundeID]);0)<\/pre>\n
If <\/span>Nz(rstQuelle!NeuesteBestellung, 0) < datStichtag Then<\/span><\/pre>\nPublic Function <\/span>Datenkopieren_III(datStichtag As Date<\/span>) As Integer<\/span>\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>rstQuelle As <\/span>DAO.Recordset\r\n Dim <\/span>intAnzahl As Integer<\/span>\r\n Set<\/span> db = CurrentDb\r\n Set<\/span> rstQuelle = db.OpenRecordset(\"SELECT * FROM qryKundenNachBestelldatumMitGruppierung\", dbOpenDynaset)\r\n Do While<\/span> Not<\/span> rstQuelle.EOF\r\n If <\/span>Nz(rstQuelle!NeuesteBestellung, 0) < datStichtag Then<\/span>\r\n If <\/span>IsNull(DLookup(\"KundeID\", \"tblKunden_Archiv\", \"KundeID = \" & rstQuelle!KundeID)) Then<\/span>\r\n db.Execute \"INSERT INTO tblKunden_Archiv(KundeID, KundenCode, Firma, Kontaktperson, Strasse, " _ & "Ort, Region, PLZ, Land, Telefon, Telefax, ArchiviertAm) VALUES(\" & rstQuelle!KundeID _\r\n & \", ''\" & rstQuelle!KundenCode & \"'', ''\" & Replace<\/span>(rstQuelle!Firma, \"''\", \"''''\") & \"'', ''\" _\r\n & rstQuelle!Kontaktperson & \"'', ''\" & Replace<\/span>( rstQuelle!Strasse,\"''\", \"''''\") & \"'', ''\" _\r\n & rstQuelle!Ort & \"'', ''\" & rstQuelle!Region & \"'', ''\" & rstQuelle!PLZ & \"'', ''\" _\r\n & rstQuelle!Land & \"'',''\" & rstQuelle!Telefon & \"'',''\" & rstQuelle!Telefax & \"'', \" _\r\n & ISODatum(Now) & \")\", dbFailOnError\r\n intAnzahl = intAnzahl + 1\r\n End If<\/span>\r\n End If<\/span>\r\n rstQuelle.MoveNext<\/span>\r\n Loop<\/span>\r\n Datenkopieren_III = intAnzahl\r\nEnd Function<\/span><\/pre>\n\n
![\"Vergleich](\"..\/fileadmin\/_temp_\/2013_06\/pic_911_006.png\")
<\/p>\nPublic Function <\/span>Datenkopieren_IV(datStichtag As Date<\/span>) As Integer<\/span>\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>qdf As <\/span>DAO.QueryDef\r\n Dim <\/span>prm As <\/span>DAO.Parameter\r\n Dim <\/span>rstQuelle As <\/span>DAO.Recordset\r\n Dim <\/span>intAnzahl As Integer<\/span>\r\n Set<\/span> db = CurrentDb\r\n Set<\/span> qdf = db.QueryDefs(\"qryKundenNachBestelldatumMitStichtag\")\r\n Set<\/span> prm = qdf.Parameters(\"Stichtag\")\r\n prm.Value = datStichtag\r\n Set<\/span> rstQuelle = qdf.OpenRecordset\r\n Do While<\/span> Not<\/span> rstQuelle.EOF\r\n If <\/span>IsNull(DLookup(\"KundeID\", \"tblKunden_Archiv\", \"KundeID = \" & rstQuelle!KundeID)) Then<\/span>\r\n db.Execute \"INSERT INTO tblKunden_Archiv(KundeID, KundenCode, Firma, Kontaktperson, Strasse,... \", _\r\n dbFailOnError\r\n intAnzahl = intAnzahl + 1\r\n End If<\/span>\r\n rstQuelle.MoveNext<\/span>\r\n Loop<\/span>\r\n Datenkopieren_IV = intAnzahl\r\nEnd Function<\/span><\/pre>\nDebug.Print<\/span> prm.Type<\/pre>\n![\"Einstellen](\"..\/fileadmin\/_temp_\/2013_06\/pic_911_007.png\")
<\/p>\nNicht In (SELECT KundeID FROM tblKunden_Archiv)<\/pre>\n
![\"Entwurf](\"..\/fileadmin\/_temp_\/2013_06\/pic_911_008.png\")
<\/p>\nPublic Function <\/span>Datenkopieren_V(datStichtag As Date<\/span>) As Integer<\/span>\r\n ...\r\n Do While<\/span> Not<\/span> rstQuelle.EOF\r\n db.Execute \"INSERT INTO tblKunden_Archiv(KundeID, KundenCode, ...\r\n intAnzahl = intAnzahl + 1\r\n rstQuelle.MoveNext<\/span>\r\n Loop<\/span>\r\n Datenkopieren_V = intAnzahl\r\nEnd Function<\/span><\/pre>\nArchiviertAm: Jetzt()<\/pre>\n
![\"Die](\"..\/fileadmin\/_temp_\/2013_06\/pic_911_009.png\")
<\/p>\nPublic Function <\/span>Datenkopieren_VI(datStichtag As Date<\/span>) As Integer<\/span>\r\n Dim <\/span>db As <\/span>DAO.Database\r\n Dim <\/span>qdf As <\/span>DAO.QueryDef\r\n Dim <\/span>prm As <\/span>DAO.Parameter\r\n Set<\/span> db = CurrentDb\r\n Set<\/span> qdf = db.QueryDefs(\"qryKundenNachBestelldatumMitInsertInto\")\r\n Set<\/span> prm = qdf.Parameters(\"Stichtag\")\r\n prm.Value = datStichtag\r\n qdf.Execute\r\n Datenkopieren_VI = qdf.RecordsAffected\r\nEnd Function<\/span><\/pre>\n