Daten bearbeiten: Execute vs. Recordset in DAO

Es kommt regelmäßig vor, dass wir Daten in den Tabellen unserer Datenbank bearbeiten müssen. Normalerweise geschieht das über die Benutzeroberfläche. Aber es gibt auch Konstellationen, in denen wir automatisiert Daten zu einer Tabelle hinzufügen oder diese ändern wollen. Manchmal legen wir vollständige Hierarchien an inklusive Daten in verknüpften Tabellen, oder wir ändern auch nur den Wert eines einzelnen Feldes in einem Datensatz. Dazu können wir verschiedene Techniken nutzen, die wir in diesem Beitrag einmal vorstellen und vergleichen wollen. Dabei konzentrieren wir uns auf das Hinzufügen oder Bearbeiten von einzelnen Datensätzen und schauen uns zwei verschiedene Ansätze an: Das Anlegen oder Aktualisieren von Daten mit INSERT INTO oder UPDATE-Abfragen, die wir per VBA zusammenstellen und dann mit der Execute-Methode ausführen oder das Anlegen mit der Recordset-Methode AddNew/Update und das Bearbeiten mit der Edit-Methode.

DAO oder ADODB?

Mit den eingangs erwähnten beiden Möglichkeiten der Anlage und Änderung von Daten mit Execute beziehungsweise AddNew/Edit und Update decken wir die Optionen ab, die uns die DAO-Bibliothek bietet. Wir können dies auch noch mit den Methoden der ADODB-Bibliothek erledigen.

Wie wir diese Aufgaben mit ADODB erledigen, beschreiben wir in einem weiteren Beitrag namens Daten bearbeiten: Execute vs. Recordset in ADODB (www.access-im-unternehmen.de/****).

Unterschied Execute vs. AddNew/Update

Mit der Execute-Anweisung, der wir eine INSERT INTO-SQL-Abfrage übergeben und der Kombination aus AddNew und Update eines Recordset-Objekts erreichen wir grundsätzlich das Gleiche: Wir fügen einer Tabelle einen Datensatz hinzu, der die gewünschten Werte enthält.

Das gilt auch für das Ändern von Datensätzen, Wir können dies mit einer UPDATE-SQL-Abfrage erledigen, die wir über die Execute-Methode absetzen, oder wir verwenden die Edit-Methode, führen dann die Änderungen an den gewünschten Feldern durch und speichern diese mit der Update-Methode in der Tabelle.

Mit beiden Methoden können wir bei der Neuanlage eines Datensatzes anschließend die ID des Primärschlüsselwertes auslesen, sofern für dieses Feld die Autowert-Funktion aktiviert ist.

Die weiteren Unterschiede, die wir in den folgenden Abschnitten besprechen werden, beziehen sich im Komfort, der sich beim Zusammenstellen der jeweiligen Codezeilen ergibt.

Wenn wir die Execute-Methode verwenden wollen, müssen wir uns grundlegend mit der Schreibweise von SQL-Anweisungen auskennen, zumindest für die SQL-Abfragen INSERT INTO und UPDATE.

Außerdem sind hier im Gegensatz zur Verwendung von AddNew/Edit und Update noch einige Besonderheiten bei der Angabe der einzufügenden oder zu ändernden Feldwerte relevant: Wenn wir beispielsweise Textfelder füllen wollen, müssen wir diese in Hochkommata einfassen, bei Datumsfeldern müssen wir ein SQL-kompatibles Datumsformat verwenden und bei Zahlen mit Dezimaltrennzeichen müssen wir sicherstellen, dass das vom SQL Server verwendete Dezimaltrennzeichen verwendet wird.

Außerdem müssen wir auch die Werte für Ja/Nein-Felder entsprechend formatieren.

Die Methoden AddNew/Edit plus Update sind hier wesentlich einfacher in der Handhabung. Wir können alle Werte einfach übergeben, so wie wir auch in VBA damit arbeiten.

Beispieltabelle

Als Beispiel verwenden wir die Tabelle tblKunden aus Bild 1.

Bild 1: Beispieltabelle tblKunden

Diese enthält alle relevanten Datentypen, die wir für die unterschiedlichen Schreibweisen in INSERT INTO– und UPDATE-Abfragen benötigen: Kurzer Text, Datum, Ja/Nein und Währung (stellvertretend für alle Felddatentypen mit Nachkommastellen).

Einfügen von Datensätzen per AddNew/Update

Wir schauen uns zuerst das Einfügen eines Datensatzes mit der AddNew– und der Update-Methode eines Recordsets an.

Hier deklarieren wir als Erstes zwei Variablen. Mit db referenzieren wir das mit der CurrentDb-Funktion ermittelte Database-Objekt der aktuellen Datenbank. Mit rst holen wir uns ein Recordset-Objekt auf Basis der Tabelle tblKunden.

Dazu nutzen wir die OpenRecordset-Methode des Database-Objekts:

Public Sub Einfuegen_AddNew()
     Dim db As DAO.Database
     Dim rst As DAO.Recordset
     Set db = CurrentDb
     Set rst = db.OpenRecordset("tblKunden", dbOpenDynaset)

Danach können wir direkt mit dem Einfügen eines Datensatzes beginnen. Dazu versetzen wir das Recordset mit der AddNew-Methode in den Einfügemodus für einen neuen Datensatz:

     rst.AddNew

Dann weisen wir den einzelnen Feldern der Tabelle, die wir über das Ausrufezeichen angeben, die gewünschten Werte zu:

     rst!Vorname = "André"
     rst!Nachname = "Minhorst"
     rst!Geburtsdatum = "23.01.1971"
     rst!Aktiv = True
     rst!Jahresumsatz = 9999.99

Danach schließen wir das Anlegen des neuen Datensatzes ab, indem wir die Update-Methode aufrufen und damit den Datensatz in der dem Recordset zugrunde liegenden Tabelle speichern:

     rst.Update
End Sub

Damit legen wir den Datensatz aus Bild 2 in der Tabelle an.

Bild 2: Neuer Datensatz in der Tabelle tblKunden

Wo ist das Feld KundeID?

Das Feld KundeID haben wir in der Tabelle als Autowert-Feld definiert. Das heißt, dass wir es nicht nur nicht zu füllen brauchen – es wird automatisch mit dem durch die Autowert-Funktion ermittelten Wert gefüllt.

Diese entspricht immer dem zuletzt hinzugefügten Wert für dieses Feld plus 1.

Wir können aber auch das Feld KundeID übergeben, wenn wir einmal einen anderen Wert als den durch die Autowert-Funktion vorgegebenen Wert angeben wollen:

rst!KundeID = 111

Dabei sind folgende Dinge zu beachten:

• Der Wert darf noch nicht vergeben sein, sonst tritt der Fehler 3022 auf, weil das Primärschlüsselfeld jeden Wert nur einmal enthalten darf.
• Der Autowert zählt anschließend an dem Wert weiter, den wir manuell zugewiesen haben. Das kann zu Problemen führen, wenn die Tabelle vorher beispielsweise Datensätze mit den ID-Werten 1 und 3 enthalten hat und wir nun einen Datensatz mit dem ID-Wert 2 anlegen. Die Autowert-Funktion wird nun als nächsten Wert 3 nutzen, was wiederum zum Fehler 3022 führt.

Die manuelle Vorgabe eines Wertes für ein Autowert-Feld sollte also mit Bedacht durchgeführt werden.

Schreibweisen für das Datum

Wir haben hier das Datum einfach als Zeichenkette übergeben („23.01.1971“). Damit haben wir Potenzial für einen Laufzeitfehler geschaffen, denn das Datum muss unbedingt ein gültiges Datum sein. Wir könnten auch die folgende Schreibweise verwenden:

rst!Geburtsdatum = #1971-01-23#

Diese wird auf Systemen mit deutschen Lokaleinstellungen jedoch direkt in die folgende Zeile umgewandelt:

rst!Geburtsdatum = #1/23/1971#

Wie können aber auch die Zeichenkette „23.01.1971“ vorsichtshalber mit der CDate-Funktion in ein gültiges Datum umwandeln oder vorab mit IsDate prüfen, ob es sich um ein gültiges Datum handelt.

AddNew mit Variablen

Dies können wir auch erledigen, indem wir die Werte für die einzelnen Felder zuvor in Variablen speichern und diese dann den Feldern zuweisen. Wir starten wie zuvor:

Public Sub Einfuegen_AddNew_Variablen()
     Dim db As DAO.Database
     Dim rst As DAO.Recordset

Dann deklarieren wir die Variablen, die wir mit den einzufügenden Werten füllen wollen, und versehen diese gleich mit den entsprechenden Datentypen:

     Dim strVorname As String
     Dim strNachname As String
     Dim datGeburtsdatum As Date
     Dim bolAktiv As Boolean
     Dim curJahresumsatz As Currency

Das Database-Objekt und das Recordset-Objekt füllen wir wie zuvor:

     Set db = CurrentDb
     Set rst = db.OpenRecordset("tblKunden", dbOpenDynaset)

Dann weisen wir die Werte den Variablen zu, die wir gleich zum Einfügen nutzen wollen:

     strVorname = "Klaus"
     strNachname = "Müller"
     datGeburtsdatum = "01.01.2000"
     bolAktiv = False
     curJahresumsatz = 8888.88

Schließlich rufen wir AddNew auf, weisen die Werte aus den Variablen den einzelnen Feldern zu und speichern den Datensatz mit der Update-Methode:

     rst.AddNew
     rst!Vorname = strVorname
     rst!Nachname = strNachname
     rst!Geburtsdatum = datGeburtsdatum
     rst!Aktiv = bolAktiv
     rst!Jahresumsatz = curJahresumsatz
     rst.Update
End Sub

Dies ist erst einmal wesentlich mehr Schreibarbeit, aber wir bereiten damit etwas vor, was in der Praxis wesentlich häufiger vorkommen wird als das Eintragen von fest im Code angegebenen Werten, nämlich das Übergeben der anzulegenden Informationen per Parameter. Damit können wir mit einem einzigen Aufruf unter Angabe der für den neuen Datensatz einzufügenden Werte einen neuen Datensatz in der gewünschten Tabelle anlegen.

AddNew mit Parametern

Dazu holen wir die Variablen einfach in die Parameterliste der Prozedur:

Public Sub Einfuegen_AddNew_Parameter( _
         strVorname As String, _
         strNachname As String, _
         datGeburtsdatum As Date, _
         bolAktiv As Boolean, _
         curJahresumsatz As Currency)

Die folgenden Schritte sind identisch mit denen der vorherigen Prozedur:

     Dim db As DAO.Database
     Dim rst As DAO.Recordset
     
     Set db = CurrentDb
     Set rst = db.OpenRecordset("tblKunden", dbOpenDynaset)
     
     rst.AddNew
     rst!Vorname = strVorname
     rst!Nachname = strNachname
     rst!Geburtsdatum = datGeburtsdatum
     rst!Aktiv = bolAktiv
     rst!Jahresumsatz = curJahresumsatz
     rst.Update
End Sub

Diese Funktion können wir nun von beliebiger Stelle innerhalb des VBA-Projekts wie folgt aufrufen und haben damit eine Wrapper-Funktion zum Anlegen eines neuen Datensatzes in die Tabelle tblKunden geschaffen:

Call Einfuegen_AddNew_Parameter("Theo", "Meier", "31.12.1999", True, 7777.77)

ID des neuen Datensatzes bei AddNew auslesen

Wenn wir wie zuvor beschrieben erst einen Kunden anlegen und dann eine Bestellung für diesen hinzufügen wollen, benötigen wir den Wert des Feldes KundeID für den neu hinzugefügten Kunden, um die neue Bestellung mit diesem verknüpfen zu können. Bei Verwendung von AddNew/Update ist das Ermitteln allerdings recht einfach.

Wir müssen lediglich den Wert des Feldes KundeID abfragen, bevor wir den Datensatz mit der Update-Methode speichern.

Warum vorher? Weil durch die Update-Methode der Datensatzzeiger nicht mehr auf dem angelegten Datensatz steht. Den Wert des Feldes KundeID lesen wir also wie folgt aus:

     ...
     rst!Jahresumsatz = 9999.99
     Debug.Print "Neuer Kunde: " & rst!KundeID
     rst.Update
     ...

Es gibt jedoch noch eine weitere Möglichkeit, die gerade bei Verwendung von SQL Server als Backend notwendig ist. Dabei setzen wir mit LastModified ein Bookmark auf den Datensatz, der zuletzt geändert wurde – in diesem Fall den zuletzt angelegten Datensatz.

Anschließend können wir damit wieder den Wert des Feldes KundeID für den neuen Datensatz auslesen:

     ...
     rst.Update
     rst.Bookmark = rst.LastModified
     Debug.Print "Neuer Kunde: " & rst!KundeID
     ...

Einfügen von Datensätzen mit Execute/INSERT INTO

Wenn wir die Execute-Methode des Database-Objekts nutzen wollen, um beispielsweise einen neuen Datensatz mit INSERT INTO einzufügen, benötigen wir im Unterschied zu AddNew/Update kein Recordset-Objekt.

Dafür müssen wir die auszuführende Abfrage aber direkt vollständig zusammenstellen, statt bequem die einzelnen Werte den Feldern zuzuweisen. Das sieht auf den ersten Blick unübersichtlicher aus, aber letztlich sind die gleichen Elemente enthalten.

Wie beginnen mit dem Definieren von Variablen für das Database-Objekt und für die zu verwendenden SQL-Anweisung:

Public Sub Einfuegen_INSERTINTO()
     Dim db As DAO.Database
     Dim strSQL As String

Die Variable strSQL benötigt man nicht zwangsläufig, aber es kann hilfreich sein, wenn man zu Testzwecken die verwendete SQL-Anweisung im Direktbereich ausgeben möchte. Außerdem wird die Lesbarkeit so verbessert.

Wir füllen wieder die Variable db mit dem Wert aus CurrentDb:

    Set db = CurrentDb

Dann stellen die SQL-Anweisung in strSQL zusammen (in einer Zeile eingeben):

     strSQL = "INSERT INTO tblKunden(Vorname, Nachname, Geburtsdatum, Aktiv, Jahresumsatz) VALUES(''André'', ''Minhorst'', #1971/01/23#, -1, 9999.99)"

INSERT INTO erwartet zunächst den Namen der Zieltabelle und dahinter in Klammern die Liste der Felder, die wir füllen möchten.

Dann folgt das VALUES-Schlüsselwort mit den in Klammern eingefassten Werten.

Hier sehen wir direkt die Unterschiede, die das Verwenden von Execute/INSERT INTO ein wenig komplizierter machen:

• Werte für Textfelder müssen in Hochkommata oder alternativ in doppelte Anführungszeichen eingefasst werden.
• Datumsangaben müssen in einem von SQL lesbaren Format angegeben werden, zum Beispiel #yyyy/mm/dd#. Wir können hier nicht einfach „23.01.1971“ angeben wie bei AddNew/Update.
• Bei Ja/Nein-Feldern müssen die Werte entweder als -1/0 angegeben werden oder als True/False. Besser noch ist True/False, denn wenn man einmal zum SQL Server wechselt, werden Ja/Nein-Felder in bit-Felder konvertiert. Das heißt, der Wert True entspricht hier dem Wert 1 und nicht mehr -1 wie unter Access (False ist in beiden Fällen 0). Wenn man nun in Abfragen beispielsweise -1 als Vergleichskriterium für ein bit-Feld nutzt, bekommt man nicht die gewünschten Ergebnisse, weil True hier als 1 gespeichert wird. Am besten verwendet man als in jeglichen SQL-Abfragen immer die Werte True und False. Zu einem weiteren Problem mit Ja/Nein-Feldern kommen wir gleich, wenn wir die Werte für den neuen Datensatz aus Variablen füllen.
• Werte mit Nachkommastellen müssen in SQL zwingend mit dem Punkt als Dezimaltrennzeichen angegeben werden. Tausendertrennzeichen sind nicht zulässig. Auch dies führt später beim Anlegen von Datensätzen unter Verwendung von Variablen zu Problemen – mehr dazu weiter unten.

Schließlich führen wir die Abfrage mit der Execute-Methode aus.

Dabei übergeben wir als ersten Parameter die SQL-Anweisung aus der Variablen strSQL und als zweiten den Wert dbFailOnError:

     db.Execute strSQL, dbFailOnError
End Sub

Warum Execute und nicht DoCmd.RunSQL?

Alternativ zur Execute-Methode des Database-Objekts können wir auch DoCmd.RunSQL verwenden. Auch dieser übergeben wir einfach die SQL-Anweisung als Parameter. Wir empfehlen allerdings, immer Execute zu verwenden.

Einer der wichtigsten Gründe ist, dass wir über die Variable db, in deren Kontext wir die Anweisung ausführen, später noch weitere Informationen erhalten können – beispielsweise die ID des neu hinzugefügten Datensatzes oder auch die Anzahl der durch die Abfrage betroffenen Datensätze. Mehr dazu lesen Sie weiter unten.

Außerdem kann es bei DoCmd.RunSQL vorkommen, dass bei der Ausführung auftretende Fehler nicht gemeldet werden, wenn zuvor mit DoCmd.SetWarnings False die Warnmeldungen deaktiviert wurden.

Und selbst wenn die Warnmeldungen aktiviert sind, erscheint so nur eine Systemmeldung wie die aus Bild 3.

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