Zur Hauptseite ... Zum Onlinearchiv ... Zum Abonnement ... Zum Newsletter ... Zu den Tools ... Zum Impressum ... Zum Login ...

Achtung: Dies ist nicht der vollständige Artikel, sondern nur ein paar Seiten davon. Wenn Sie hier nicht erfahren, was Sie wissen möchten, finden Sie am Ende Informationen darüber, wie Sie den ganzen Artikel lesen können.

Kompletten Artikel lesen?

Einfach für den Newsletter anmelden, dann lesen Sie schon in einer Minute den kompletten Artikel und erhalten die Beispieldatenbanken.

E-Mail:

Gedrucktes Heft

Diesen Beitrag finden Sie in Ausgabe 6/2013.

Unser Angebot für Sie!

Lesen Sie diesen Beitrag und 500 andere sofort im Onlinearchiv, und erhalten Sie alle zwei Monate brandheißes Access-Know-how auf 72 gedruckten Seiten! Plus attraktive Präsente, zum Beispiel das bald erscheinende Buch 'Access 2010 - Das Grundlagenbuch für Entwickler'!

Diesen Beitrag twittern

Aktionsabfragen statt Schleifen

Ä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.

Performance und Stil

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 und Update zu einem weiteren Recordset hinzufügen, dauert das schlicht und einfach länger als wenn Sie die gesamte Prozedur in einer einzigen Aktionsabfrage unterbringen.

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.

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.

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.

Eine solche Entwicklung können Sie in der Lösung zum Beitrag Datenmodelle vergleichen (www.access-im-unternehmen.de/916) 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.

Beispiel: Kundendaten archivieren

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.

Außerdem soll die neue Tabelle noch ein weiteres Feld mit dem Datum der Archivierung enthalten (s. Bild 1).

Quell- und Zieltabelle

Bild 1: Quell- und Zieltabelle

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.

Start der Archivierung

Die Archivierung soll über Schaltflächen eines Formulars namens frmKundenArchivieren gestartet werden (s. Bild 2).

Formular mit Schaltflächen zum Starten der verschiedenen Archivierungen

Bild 2: Formular mit Schaltflächen zum Starten der verschiedenen Archivierungen

Dieses Formular ruft die verschiedenen Varianten der Archivierung auf und übergibt dabei jeweils das im Textfeld txtStichtag gespeicherte Datum an die Funktionen, für die erste Schaltfläche etwa so:

Private Sub cmd1_Click()
     Dim intAnzahl As Integer
     intAnzahl = Datenkopieren_ DAOMitAbfrage(Me!txtStichtag)
     MsgBox intAnzahl _
         & " Datensätze archiviert"
End Sub

Die "sichere" Variante

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.

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.

Public Function Datenkopieren_DAOMitAbfrage(datStichtag As Date) As Integer
     Dim db As DAO.Database
     Dim rstQuelle As DAO.Recordset
     Dim rstZiel As DAO.Recordset
     Dim datLetzteBestellung As Date
     Dim intAnzahl As Integer
     Set db = CurrentDb
     Set rstQuelle = db.OpenRecordset("SELECT * FROM tblKunden", dbOpenDynaset)
     Set rstZiel = db.OpenRecordset("SELECT * FROM tblKunden_Archiv WHERE 1 = 2", dbOpenDynaset)
     Do While Not rstQuelle.EOF
         datLetzteBestellung = Nz(DMax("Bestelldatum", "tblBestellungen", _
             "KundeID = " & rstQuelle!KundeID), 0)
         If datLetzteBestellung < datStichtag Then
             If IsNull(DLookup("KundeID", "tblKunden_Archiv", "KundeID = " & rstQuelle!KundeID)) Then
                 With rstZiel
                     .AddNew
                     !KundeID = rstQuelle!KundeID
                     !KundenCode = rstQuelle!KundenCode
                     !Firma = rstQuelle!Firma
                     !Kontaktperson = rstQuelle!Kontaktperson
                     !Strasse = rstQuelle!Strasse
                     !Ort = rstQuelle!Ort
                     !Region = rstQuelle!Region
                     !PLZ = rstQuelle!PLZ
                     !Land = rstQuelle!Land
                     !Telefon = rstQuelle!Telefon
                     !Telefax = rstQuelle!Telefax
                     !ArchiviertAm = Now
                     .Update
                     intAnzahl = intAnzahl + 1
                 End With
             End If
         End If
         rstQuelle.MoveNext
     Loop
     Datenkopieren_DAOMitAbfrage = intAnzahl
End Function

Listing 1: Die "sichere" Variante zum Kopieren von Daten

Die Funktion erstellt ein Database-Objekt namens db sowie zwei Datensatzgruppen namens rstQuelle und rstZiel. Das Recordset-Objekt rstQuelle wird mit einem Verweis auf alle Datensätze der Tabelle tblKunden gefüllt, das Recordset-Objekt rstZiel mit einer Datenherkunft, die auf der Zieltabelle tblKunden_Archiv basiert, aber wegen des Kriteriums 1=2 keine Datensätze enthält.

Die Prozedur durchläuft nun alle Datensätze des Recordsets tblKunden. Dabei ermittelt sie zunächst mit dem Aufruf von DLookup das aktuellste Datum einer Bestellung aus der Tabelle tblBestellungen für die Datensätze, deren Feld KundeID mit dem Kunden des aktuellen Datensatzes aus rstQuelle übereinstimmt.

Für den Fall, dass für den Kunden noch gar keine Bestellung vorliegt, soll datLetzteBestellung den Wert 0 erhalten, was die Nz-Funktion mit entsprechendem zweiten Parameter bewerkstelligt.

Die folgende If...Then-Bedingung prüft, ob das in datLetzteBestellung gespeicherte Datum kleiner als datStichtag 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.

Erst danach wird der neue Datensatz angelegt, und zwar mit der AddNew-Methode des Recordset-Objekts rstZiel. Die folgenden Anweisungen tragen jeweils den Wert eines Feldes der Quelltabelle in das entsprechende Feld der Zieltabelle ein.

Danach fügt die Funktion noch dem Feld ArchiviertAm das aktuelle Datum und die aktuelle Uhrzeit hinzu. Die Update-Methode sorgt für das Speichern des neu angelegten Datensatzes in der Tabelle tblKunden_Archiv.

Schließlich erhöht die Prozedur den Wert der Variablen intAnzahl, welche die Anzahl der übertragenen Datensätze zählen soll, um 1. Die MoveNext-Methode bewegt den Datensatzzeiger zum nächsten Datensatz, die Loop-Anweisung lässt die Funktion mit einem weiteren Durchlauf der Do While-Schleife beginnen.

Schließlich liefert die Funktion die in intAnzahl gespeicherte Anzahl betroffener Datensätze zurück.

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.

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.

Optimierung 1: DMax auflösen

Die obige Funktion muss wirklich viel Arbeit leisten, weil sie jeden einzelnen Datensatz der Herkunftstabelle tblKunden 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 gespeichert ist. Dabei ist für jeden Datensatz der Aufruf der DMax-Funktion fällig.

Wir wollen in kleinen Schritten optimieren und versuchen zunächst, die DMax-Funktion aus der Schleife herauszunehmen. Die Datenherkunft besteht bisher aus der Tabelle tblKunden. Deren Feld KundeID verwenden wir, um per DMax das Datum der letzten Bestellung zu ermitteln.

Wie bekommen wir nun die DMax-Anweisung aus der Do While-Schleife heraus? Der erste Schritt dazu ist einfach: Wir erstellen einfach eine Abfrage auf Basis der Tabelle tblKunden und fügen ein weiteres Feld hinzu, welches das Datum der neuesten Bestellung ermittelt – dies wiederum durch den Einsatz der DMax-Funktion. Der Ausdruck für dieses Feld sieht so aus, die komplette Abfrage qryKundenNachBestelldatum finden Sie in Bild 3:

Sie haben das Ende des frei verfügbaren Teils des Artikels erreicht. Lesen Sie weiter, um zu erfahren, wie Sie den vollständigen Artikel lesen und auf viele hundert weitere Artikel zugreifen können.

Sind Sie Abonnent?Jetzt einloggen ...
 

Kompletten Artikel lesen?

Einfach für den Newsletter anmelden, dann lesen Sie schon in einer Minute den kompletten Artikel und erhalten die Beispieldatenbanken.

E-Mail:

© 2003-2015 André Minhorst Alle Rechte vorbehalten.