Language: Deutsch English















Last Update: 2017 - 11 - 29





Wie man Transaktionen in Microsoft Access verwendet

von Philipp Stiefel, ursprünglich veröffentlicht 01. März 2016
, zuletzt aktualisiert 01. Dezember 2017


Blick in den Himmel aus einerm Innenhof, article header image

Was sind Transaktionen?

Transaktionen sind ein fundamentales Konzept relationaler Datenbanken. Transaktionen fassen mehrere Operationen in einer Datenbank zu einer Arbeitseinheit zusammen. Diese Arbeitseinheit kann entweder als Ganzes erfolgreich sein oder als Ganzes fehlschlagen. Wenn sie erfolgreich ist werden alle Änderungen, die im Rahmen der Transaktion durchgeführt wurden, endgültig gespeichert (commit) und damit dauerhaft in die Datenbank geschrieben. Wenn nur ein Teil der Transaktion fehlschlägt, werden die Änderungen zurückgerollt (rollback) und alle Daten, die innerhalb der Transaktion geändert wurden, sind immer noch in genau dem Zustand in dem sie vor der Transaktion waren.

Ein klassisches Beispiel für eine Transaktion ist der Transfer von Geld von einem Bankkonto zu einem anderen. Es darf absolut niemals passieren, dass Geld von einem Konto abgebucht wird ohne dass es dem Gegenkonto gutgeschrieben wird. Um dieses Verhalten sicherzustellen, müssen die Änderungen an den beiden Konten in einer Transaktion zusammengefasst werden.

Unmengen wurden bereits über Transaktionen und das ACID-Paradigma geschrieben, daher werde ich hier nicht tiefer in die generelle Datenbanktheorie eintauchen.

Transaktionen in Access

Access, oder genauer die Jet-/ACE-Datenbank-Engine, unterstützt ebenfalls Transaktionen. Sie werden sogar standardmäßig für jede Aktionsfrage genutzt. Allerdings wird dann nur eine einzelne Abfrage innerhalb einer Transaktion ausgeführt, so dass Du es kaum bemerkst, dass eine Transaktion involviert ist. Dennoch stellt die Transaktion sicher, dass alle Änderungen, die innerhalb der Transaktion vorgenommen wurden, im Falle eines Fehlers komplett rückgängig gemacht werden können. (Du kannst die Verwendung von Transaktionen für eine bestimmte Abfrage deaktivieren, indem du die „Transaktion verwenden“-Eigenschaft auf „Nein“ setzt.)

Eigenschaftsblatt einer Abfrage mit der 'Transaktion verwenden' Eigenschaft

Du kannst auch im VBA-Code explizite Transaktionen verwenden. Damit kannst du mehrere verschiedene Abfragen in einer einzigen Transaktion zusammenfassen und diese Transaktion dann als Ganzes entweder speichern oder alle Änderungen zurückrollen. Da du dieses Verhalten mit deinem VBA-Code bestimmst, kannst du dies unabhängig von dem Erfolg oder Misserfolg einzelner Abfragen innerhalb der Transaktion tun.

Transaktionen in VBA-Code implementieren

Lass uns jetzt anschauen, wie genau man explizite Transaktionen in VBA implementiert. (Du kannst Transaktionen nicht mit Makros verwenden.)

Die Umsetzung ist relativ einfach. Bevor du die erste Aktionsabfrage, die zu der Transaktion gehören soll, ausführst, weist du die DBEngine mit der BeginTrans-Methode an, eine Transaktion zu beginnen. Dann führst du alle Abfragen aus, die du in der Transaktion zusammenfassen willst.

Wenn kein Fehler aufgetreten ist, wirst du normalerweise die Transaktion committen, indem du die CommitTans-Methode aufrufst und damit alle Änderungen der Aktionsabfragen dauerhaft in die Datenbank schreibst. Andernfalls kannst du alle Änderungen der Transaktion mit der Rollback-Methode rückgängig machen.

Commit und Rollback sind bei der Jet-/ACE-Engine unabhängig davon, ob ein Fehler aufgetreten ist. Du kannst also auch eine Transaktion mit einem Commit abschließen, wenn nicht alle Änderungen erfolgreich waren. Ebenso kann man auch eine Transaktion zurückrollen, wenn alle ihre Abfragen erfolgreich waren.

Gut, jetzt schauen wir uns den Code an, den wir benötigen um das in VBA umzusetzen.

Public Sub TransactionDemo() DAO.DBEngine.BeginTrans On Error GoTo tran_Err CurrentDb.Execute "INSERT INTO tblLog (Text1) VALUES ('New banana delivery')", dbFailOnError CurrentDb.Execute "UPDATE tblInfo SET InfoText = 'Banana count: 2983' WHERE ID = 6 ", dbFailOnError CurrentDb.Execute "DELETE FROM tblTemp", dbFailOnError DAO.DBEngine.CommitTrans Exit Sub tran_Err: DAO.DBEngine.Rollback MsgBox "Transaction failed. Error: " & Err.Description End Sub

Das ist nicht besonders kompliziert, oder? Aber es gibt dabei zwei wichtige Punkte, die du dabei beachten musst.

Es ist sehr wichtig, die Fehler zu behandeln, die in der Prozedur während der Ausführung der Abfragen auftreten können. Im Falle eines Fehlers musst du die Transaktion zurückrollen. Andernfalls bleiben die Datenbankseiten (eine interne Speichereinheit für einen oder mehrere Datensätze), die von der Transaktion betroffen sind, gesperrt gegen Änderungen von anderen Benutzern. Das würde einen Teil deiner Datenbank in einem schreibgeschützten Zustand versetzen bis die Instanz von Access, aus der die Transaktion gestartet wurde, beendet wird. – Was die Transaktion zurückrollt.

Der andere wichtige Punkt, der hier zu beachten ist, ist es die dbFailOnError-Option für die CurrentDb.Execute-Methode zu verwenden. Wenn du diese Option weglässt, können die Aktionsabfragen unbemerkt fehlschlagen. Z.B. wenn eine Abfrage eine Schlüsselverletzung oder die Verletzung einer Gültigkeitsregel auslöst, wenn werden die jeweiligen Datensätze nicht geändert, eingefügt oder gelöscht aber es wird kein Laufzeitfehler in VBA ausgelöst. Du könntest dann trotzdem die Transaktion committen, aber dann werden natürlich nur die Änderungen der anderen, erfolgreichen SQL-Befehle werden dann dauerhaft gespeichert. – Das ist genau das Problem, dass wir durch den Einsatz von Transaktionen verhindern wollen.

Du kannst eine beliebige Anzahl von Abfragen in einer Transaktion zusammenfassen. Aber denk immer daran: Die betroffenen Datensätze (genauer Datenbankseiten) bleiben gesperrt bis die Transaktion abgeschlossen ist. Die Aktionsabfragen oder manuellen Änderungen anderer Benutzer werden fehlschlagen solange die Datensatzsperre aus der Transaktion nicht wieder freigegeben wurden.

Fehlermeldung: Aktualisierung nicht möglich; momentan gesperrt

Wenn du mehrere Verschiedene Operationen, die dieselben Tabellen ändern, in Transaktionen zusammengefasst hast, dann ändere die Tabellen in allen Transaktion möglichst immer in derselben Reihenfolge. Andernfalls könnte ein Deadlock (zwei Transaktionen von denen jede jeweils auf die andere wartet) auftreten.

Keine Benutzerinteraktion innerhalb einer Transaktion!

Zum Abschluss möchte ich dir noch einen Konzeptionellen Ratschlag zu Transaktion geben. Es ist zwar technisch möglich, aber dennoch möchte ich dringend davon abraten eine Benutzerinteraktion („Möchten sie die Änderungen speichern?“ – „Ja/Nein“) in eine Transaktion zu integrieren.

Wie oben beschrieben, blockiert eine offene Transaktion Teile der Datenbank solange, bis sie entweder mit einem Commit oder einem Rollback abgeschlossen ist. Es ist eine schlechte Idee, auf eine Eingabe des Benutzers zu warten, um die Transaktion abzuschließen. Der Benutzer könnte gerade anderweitig beschäftigt sein und die Transaktion erstmal noch eine Weile offen lassen. Oder er könnte erstmal in die Mittagspause gehen, oder in seinen dreiwöchigen Jahresurlaub… - Und ein Teil der Datenbank ist Schreibgeschützt für andere Benutzer, bis er wieder da ist.

Es gibt noch einen anderen Grund dafür, dass ich mich gegen Benutzerinteraktionen innerhalb von Transaktionen ausspreche. Dieser Grund betrifft nur Access (bzw. die Jet-/ACE-Engine), aber nicht Client-Server-Datenbanken, wie Microsoft SQL Server.

Falls durch ein technisches Problem, wie z.B. eine unterbrochene Netzwerkverbindung oder ein Stromausfall, die Access Frontend Anwendung während einer Transaktion von der Backend-Datenbank getrennt wird, dann besteht ein gewisses Risiko für eine Korruption der Datenbank. Wenn ein solcher Zwischenfall auftritt, besteht dieses Risiko gleichermaßen auch bei einer einzelnen Abfrage ohne Transaktion, oder bei einer Transaktion ohne Benutzerinteraktion. Nur wird jede Benutzerinteraktion, selbst wenn der Benutzer direkt reagiert, die Gesamtdauer der Transaktion deutlich verlängern und damit auch das Risiko deutlich erhöhen, dass es währenddessen zu einem Zwischenfalls kommt, der Datenbankkorruption verursachen könnte.

Also bitte sieh Transaktionen nur als eine technische Arbeitseinheit, die keine Benutzerinteraktion einschließt. Wenn die Operation, die du innerhalb der Transaktion ausführen möchtest, Eingaben des Benutzers erfordert, dann lass sie bereits eingeben und validiere sie, bevor du die Transaktion startest.

Transaktionen in Access Formularen - Du wurdest gewarnt!

Jetzt, nachdem ich meine Vorbehalte gegen Benutzerinteraktion in Transaktionen überdeutlich gemacht habe, werde ich dennoch erklären, wie du Transaktionen in Verbindung mit Benutzereingaben in Access Formularen verwenden kannst.

Wenn du Access Formulare mit der üblichen Datenbindung an eine Tabelle oder Abfrage verwendest, kannst du Transaktionen gar nicht verwenden. Die Daten derartig gebundenen Access Formularen werden außerhalb des Geltungsbereichs des programmatisch erreichbaren Arbeitsbereichs gespeichert. Also, selbst wenn du eine Transaktion beginnst, bevor du Daten in einem Formular änderst, hat ein Commit oder Rollback keinerlei Auswirkungen darauf.

Allerdings gibt es einen Weg, wie es funktioniert.

In Access 2002 und neueren Versionen kannst du ein Formular nicht nur an die Datenquelle binden, indem du die Recordsource-Eigenschaft des Formulars auf den Namen einer Tabelle oder Abfrage oder eine SQL-Anweisung einstellst, sondern auch indem du die Recordset-Eigenschaft auf ein im VBA-Code von dir erstelltes Recordset setzt. Wenn du das machst, sind die Datenänderungen im Geltungsbereich der Transaktion. Folglich kannst du dann eine Transaktion beginnen, den Benutzer Daten ändern lassen und dann entweder ein Commit oder Rollback der Transaktion über VBA-Code auslösen.

Die funktioniert sogar mit wenn das Formular ein Unterformular enthalt, dass Detaildatensätze zum Datensatz des Hauptformulars darstellt. Du muss nur sowohl das Hauptformular als auch das Unterformular an ein Recordset binden.

Es gibt jedoch noch eine geringfügige Einschränkung. Aus Gründen, die ich nicht verstehe, funktioniert das nicht, wenn du die LinkMasterFields/LinkChildFields-Eigenschaften verwendest, um die beiden Formulare zu verknüpfen. Du musst stattdessen das Detailformular in deinem eigenen Code mit dem Hauptformular synchronisieren. Dies schließt ein, dass du für neue Datensätze selbst den Wert des Fremdschlüssels des Detaildatensatzes auf den korrespondierenden Wert des Hauptdatensatzes setzt. Dies kann zum Beispiel über ein verstecktes Steuerelement passieren, dass an die Fremdschlüsselspalte gebunden ist und dessen DefaultValue du auf den Schlüsselwert aus dem Hauptformulars setzt.

Hier ist ein Screenshot eines einfachen Master-Detail-Forms und der korrespondierende VBA-Code um die Transaktion zu steuern.

Access Master-Detail-Formular mit Transaktionsbehandlung
Private Sub btnBeginEdit_Click() DBEngine.BeginTrans End Sub Private Sub btnSaveChanges_Click() DBEngine.CommitTrans End Sub Private Sub btnUndoChanges_Click() DBEngine.Rollback End Sub Private Sub Form_Current() ' loading the child records for the current master record Set Me.subActorRatings.Form.Recordset = _ CurrentDb.OpenRecordset("SELECT * FROM tblActorRatings WHERE ActorId=" & Me!ActorId) ' Setting the correct default value for new records Me.subActorRatings.Form.Controls("txtActorIdHidden").DefaultValue = Me!ActorId End Sub Private Sub Form_Load() Set Me.Recordset = CurrentDb.OpenRecordset("SELECT * FROM tblActors") End Sub

Die Transaktionssteuerung hier ist komplett manuell um das Konzept zu verdeutlichen. Dieser Code ist keineswegs geeignet für einen produktiven Einsatz. In einem Produktivszenario müsstest du die Transaktion automatisch beginnen und dann alle möglichen Benutzeraktionen, wie Schließen des Formulars, Blättern durch die Datensätze, sowie Löschen und Hinzufügen von Datensätzen behandeln. Dies robust zu implementieren ist sicherlich eine Herausforderung.

Unglücklicherweise bietet die DAO Datenzugriffsbibliothek keine Eigenschaft oder Methode um den aktuellen Transaktionsstatus zu ermitteln. Es wird zwar einen Fehler geben, wenn du versuchst ein Commit oder Rollback einer nicht vorhandenen Transaktion vorzunehmen, aber nichts hält dich davon ab, unbeabsichtigt mehrere verschachtelte Transaktionen zur gleichen Zeit zu beginnen. Also, du muss äußerste Vorsicht walten lassen, wenn du deinen Code zu Transaktionsteuerung entwirfst, um zu verhindert, dass ich dort schwer zu findende Fehler einschleichen.

Da ich generell empfehle keine Benutzerinteraktionen in Transaktionen vorzusehen, habe ich den obigen Lösungsansatz noch nicht in einer produktiven Anwendung eingesetzt. Ich vermag nicht sagen, ob dies verlässlich mit mehreren Benutzers und über längere Zeit funktioniert.

Transaktionen und AutoWerte

Manche Leute mögen keine Lücken in ihren AutoWert-Reihen. Wenn du dazugehörst, hast du vielleicht die Idee Transaktionen zu verwenden um den „Verlust“ von Werten aus der AutoWert-Sequenz zu verhindern, indem du den Code der in einer Tabelle mit AutoWert neue Datensätze anlegt, in eine Transaktion kapselst.

Gute Idee! Leider funktioniert das nicht. Du bekommst deine AutoWerte auch dann nicht zurück, wenn sie innerhalb eine Transaktion, die zurückgerollt wurde, vergeben wurden. Diese Werte sind trotzdem „verloren“.

Die Logik die die AutoWert-Sequenzen verwaltet läuft außerhalb des Wirkungsbereichs von Transaktionen. Das muss so sein. Andernfalls würde eine Transaktion, die einen neuen AutoWert abgerufen hat, alle anderen Benutzer, die neue Datensätze in die selbe Tabelle einfügen wollen blockieren, bis die Transaktion beendet ist.

Workspaces

Du hast vielleicht schon andere Texte über Transaktionen in Microsoft Access gelesen. Diese habe wahrscheinlich zumindest in ihrem Beispielcode DAO Workspaces für die Transaktionssteuerung verwendet. Ich habe diese bisher noch gar nicht erwähnt und werde sie auch hier nicht weiter erklären. Warum das?

Mit Microsoft Access wirst du äußerst selten mehr als einen einzelnen Workspace verwenden. Solange du nicht wirklich mehrere Workspaces verwendest, kommst du wunderbar zurecht, wenn du diese komplett ignorierst und das DBEngine-Objekt für die Transaktionssteuerung verwendest, wie ich es oben gemacht habe.

Share this article: Share on Facebook Tweet Share on LinkedIn Share on XING

Abonniere meinen Newsletter

*

Ich werde Deine Email-Addresse niemals weitergeben. Du kannst den Newsletter jederzeit abbestellen.



© 1999 - 2017 by Philipp Stiefel