All geek questions in one place

MS Access (Jet) Transactions, Workspaces

I am having trouble committing a transaction (using Access 2003 DAO). It acts as if I never called BeginTrans - I get error 3034 on CommitTrans: "You tried to complete a transaction or cancel a transaction without starting a transaction"; and the changes are written to the database (presumably because they were never wrapped in a transaction). However, BeginTrans starts if you go through it.

  • I run it in an Access environment using the DBEngine (0) workspace.
  • The tables to which I add records are opened through a connection to the Jet database (in the same database) and using DAO.Recordset.AddNew / Update.
  • The connection opens before starting BeforeTrans.
  • I am not doing anything strange in the middle of a transaction, such as closing / opening connections or multiple workspaces, etc.
  • There are two levels of nested transactions. Basically it is packing multiple inserts into an external transaction, so if there is any failure, they all fail. Internal transactions are performed without errors; this is an external transaction that does not work.

Here are a few things I learned and ruled out:

  • A transaction is distributed using several methods, and BeginTrans and CommitTrans (and rollback) are in different places. But when I tried a simple test to complete a transaction this way, it doesn't seem to matter.

  • I thought the database connection closes when it leaves the local area, although I have another global reference to it (I never know what the DAO does with dbase connections, to be honest). But that doesn't seem to be the case - right before committing, the connection and its record sets are alive (I can check their properties, EOF = False, etc.)

  • My CommitTrans and rollback are done in callbacks. (Basically: the syntax program throws an onLoad event at the end of the parsing, which I process, either committing or rolling back the inserts that I made during processing, depending on whether any errors occurred.) However, again, trying a simple test, it seems like it doesn't matter.

Any ideas why this is not working for me?

Thanks.

EDIT May 25

Here is the (simplified) code. The key points associated with the transaction are:

  • The workspace is DBEngine (0), which is referenced by the public (global) variable APPSESSION .
  • The database connection is opened in LoadProcess.cache below, see the line Set db = APPSESSION.connectionTo(dbname_) .
  • BeginTrans is called in LoadProcess.cache.
  • CommitTrans is called in the process__onLoad callback.
  • A rollback in the call to process__onInvalid is called.
  • Recordset updates are performed in process__onLoadRow, logLoadInit, and logLoad

Eric

 '------------------- 'Application globals '------------------- Public APPSESSION As DAOSession '------------------ ' Class LoadProcess '------------------ Private WithEvents process_ As EventedParser Private errs_ As New Collection Private dbname_ As String Private rawtable_ As String Private logtable_ As String Private isInTrans_ As Integer Private raw_ As DAO.Recordset Private log_ As DAO.Recordset Private logid_ As Variant Public Sub run '--- pre-load cache resetOnRun ' resets load state variables per run, omitted here logLoadInit Set process_ = New EventedParser '--- load process_.Load End Sub ' raised once per load() if any row invalid Public Sub process__onInvalid(filename As String) If isInTrans_ Then APPSESSION.Workspace.Rollback End Sub ' raised once per load() if all rows valid, after load Public Sub process__onLoad(filename As String) If errs_.Count > 0 Then logLoadFail filename, errs_ Else logLoadOK filename End If If isInTrans_ Then APPSESSION.Workspace.CommitTrans End Sub ' raised once per valid row ' append data to raw_ recordset Public Sub process__onLoadRow(row As Dictionary) On Error GoTo Err_ If raw_ Is Nothing Then GoTo Exit_ DAOext.appendFromHash raw_, row, , APPSESSION.Workspace Exit_: Exit Sub Err_: ' runtime error handling done here, code omitted Resume Exit_ End Sub Private Sub cache() Dim db As DAO.Database ' TODO raise error If Len(dbname_) = 0 Then GoTo Exit_ Set db = APPSESSION.connectionTo(dbname_) ' TODO raise error If db Is Nothing Then GoTo Exit_ Set raw_ = db.OpenRecordset(rawtable_), dbOpenDynaset) Set log_ = db.OpenRecordset(logtable_), dbOpenDynaset) APPSESSION.Workspace.BeginTrans isInTrans_ = True Exit_: Set db = Nothing End Sub ' Append initial record to log table Private Sub logLoadInit() Dim info As New Dictionary On Error GoTo Err_ ' TODO raise error? If log_ Is Nothing Then GoTo Exit_ With info .add "loadTime", Now .add "loadBy", CurrentUser End With logid_ = DAOext.appendFromHash(log_, info, , APPSESSION.Workspace) Exit_: Exit Sub Err_: ' runtime error handling done here, code omitted Resume Exit_ End Sub Private Sub logLoadOK(filename As String) logLoad logid_, True, filename, New Collection End Sub Private Sub logLoadFail(filename As String, _ errs As Collection) logLoad logid_, False, filename, errs End Sub ' Update log table record added in logLoadInit Private Sub logLoad(logID As Variant, _ isloaded As Boolean, _ filename As String, _ errs As Collection) Dim info As New Dictionary Dim er As Variant, strErrs As String Dim ks As Variant, k As Variant On Error GoTo Err_ ' TODO raise error? If log_ Is Nothing Then GoTo Exit_ If IsNull(logID) Then GoTo Exit_ For Each er In errs strErrs = strErrs & IIf(Len(strErrs) = 0, "", vbCrLf) & CStr(er) Next Er With info .add "loadTime", Now .add "loadBy", CurrentUser .add "loadRecs", nrecs .add "loadSuccess", isloaded .add "loadErrs", strErrs .add "origPath", filename End With log_.Requery log_.FindFirst "[logID]=" & Nz(logID) If log_.NoMatch Then 'TODO raise error Else log_.Edit ks = info.Keys For Each k In ks log_.Fields(k).Value = info(k) Next k log_.Update End If Exit_: Exit Sub Err_: ' runtime error handling done here, code omitted Resume Exit_ End Sub '------------- ' Class DAOExt '------------- ' append to recordset from Dictionary, return autonumber id of new record Public Function appendFromHash(rst As DAO.Recordset, _ rec As Dictionary, _ Optional map As Dictionary, _ Optional wrk As DAO.workspace) As Long Dim flds() As Variant, vals() As Variant, ifld As Long, k As Variant Dim f As DAO.Field, rst_id As DAO.Recordset Dim isInTrans As Boolean, isPersistWrk As Boolean On Error GoTo Err_ ' set up map (code omitted here) For Each k In rec.Keys If Not map.Exists(CStr(k)) Then _ Err.Raise 3265, "appendFromHash", "No field mapping found for [" & CStr(k) & "]" flds(ifld) = map(CStr(k)) vals(ifld) = rec(CStr(k)) ifld = ifld + 1 Next k If wrk Is Nothing Then isPersistWrk = False Set wrk = DBEngine(0) End If wrk.BeginTrans isInTrans = True rst.AddNew With rst For ifld = 0 To UBound(flds) .Fields(flds(ifld)).Value = vals(ifld) Next ifld End With rst.Update Set rst_id = wrk(0).OpenRecordset("SELECT @@Identity", DAO.dbOpenForwardOnly, DAO.dbReadOnly) appendFromHash = rst_id.Fields(0).Value wrk.CommitTrans isInTrans = False Exit_: On Error GoTo 0 If isInTrans And Not wrk Is Nothing Then wrk.Rollback If Not isPersistWrk Then Set wrk = Nothing Exit Function Err_: ' runtime error handling, code omitted here Resume Exit_ End Function '----------------- ' Class DAOSession (the part that deals with the workspace and dbase connections) '----------------- Private wrk_ As DAO.workspace Private connects_ As New Dictionary Private dbs_ As New Dictionary Public Property Get workspace() As DAO.workspace If wrk_ Is Nothing Then If DBEngine.Workspaces.Count > 0 Then Set wrk_ = DBEngine(0) End If End If Set workspace = wrk_ End Property Public Property Get connectionTo(dbname As String) As DAO.database connectTo dbname Set connectionTo = connects_(dbname) End Property Public Sub connectTo(dbname As String) Dim Cancel As Integer Dim cnn As DAO.database Dim opts As Dictionary Cancel = False ' if already connected, use cached reference If connects_.Exists(dbname) Then GoTo Exit_ If wrk_ Is Nothing Then _ Set wrk_ = DBEngine(0) ' note opts is a dictionary of connection options, code omitted here Set cnn = wrk_.OpenDatabase(dbs_(dbname), _ CInt(opts("DAO.OPTIONS")), _ CBool(opts("DAO.READONLY")), _ CStr(opts("DAO.CONNECT"))) ' Cache reference to dbase connection connects_.Add dbname, cnn Exit_: Set cnn = Nothing Exit Sub End Sub
+4
source share
3 answers

Transactions are used by defining the workspace (it should not be new), and then starting a transaction in this workspace, doing what you need to do with it, and then commit the transaction, if everything is in order. Skeleton Code:

  On Error GoTo errHandler Dim wrk As DAO.Workspace Set wrk = DBEngine(0) ' use default workspace wrk.BeginTrans [do whatever] If [conditions are met] Then wrk.CommitTrans Else wrk.Rollback End If errHandler: Set wrk = Nothing exitRoutine: ' do whatever you're going to do with errors wrk.Rollback Resume errHandler

Now, in the block where you [execute everything], you can disconnect the workspace, databases and recordsets for subprograms, but the top-level management structure should remain in one place.

Your code does not do this - instead, you depend on global variables. GLOBAL VARIABLES - EVIL. Do not use them. Instead, pass private variables as parameters to the routines you want to use for them. I would also say, never went through the workspace - skip only those objects that you created with the workspace.

As soon as you realize this, perhaps this will help you explain what your code should do (that I don’t have the most thoughtful idea to read it), and then we can advise you, I repeat it wrong.

+3
source

Well, after a very nasty debugging, I think I found an error in Jet transactions. In the end, this has nothing to do with my "extremely confusing" code or "evil global variables" :)

It seems that when the following is true, you get error message # 3034:

  • You open a set of snapshot type records
  • Record set is open until you start a transaction
  • The recordset is closed / dereferenced after , you start a transaction, but before committing or rolling back.

I did not check if this is known, although I cannot imagine that this is not so.

Of course, it’s strange to do everything in this order and ask for troubles, I don’t know why I did it. I moved the opening / closing set of snapshot records as part of the transaction, and everything works fine.

The following code shows the error:

 Public Sub run() Dim db As DAO.Database, qdf As DAO.QueryDef, rst As DAO.Recordset Dim wrk As DAO.Workspace, isInTrans As Boolean On Error GoTo Err_ Set wrk = DBEngine(0) Set db = wrk(0) Set rst = db.OpenRecordset("Table2", DAO.dbOpenSnapshot) wrk.BeginTrans isInTrans = True Set qdf = db.CreateQueryDef("", "INSERT INTO [Table1] (Field1, Field2) VALUES (""Blow"", ""Laugh"");") qdf.Execute dbFailOnError Exit_: Set rst = Nothing Set qdf = Nothing Set db = Nothing If isInTrans Then wrk.CommitTrans isInTrans = False Exit Sub Err_: MsgBox Err.Description If isInTrans Then wrk.Rollback isInTrans = False Resume Exit_ End Sub

And this fixes the error:

 Public Sub run() Dim db As DAO.Database, qdf As DAO.QueryDef, rst As DAO.Recordset Dim wrk As DAO.Workspace, isInTrans As Boolean On Error GoTo Err_ Set wrk = DBEngine(0) Set db = wrk(0) wrk.BeginTrans isInTrans = True ' NOTE THIS LINE MOVED WITHIN THE TRANSACTION Set rst = db.OpenRecordset("Table2", DAO.dbOpenSnapshot) Set qdf = db.CreateQueryDef("", "INSERT INTO [Table1] (Field1, Field2) VALUES (""Blow"", ""Laugh"");") qdf.Execute dbFailOnError Exit_: Set rst = Nothing Set qdf = Nothing Set db = Nothing If isInTrans Then wrk.CommitTrans isInTrans = False Exit Sub Err_: MsgBox Err.Description If isInTrans Then wrk.Rollback isInTrans = False Resume Exit_ End Sub
+2
source

For what it's worth, it seems a little more widespread than just access transactions. I just came across a similar situation using Access 2007 and DAO as the MySQL interface. With MySQL Autocommit=0 , SQL transactions would nonetheless mysteriously commit halfway through the transaction.

After 2 weeks of scratching my head, I came across this message and looked at my code again. Of course, MySQL inserts were performed using a stored procedure that was called from a module of the VBA class. This class class had dao.recordset , which was opened on module.initialize() and closed on terminate() . In addition, this set of records was used to collect the results of the stored procedure. So I have (in pseudo code ...)

 module.initialize - rs.open class properties set by external functions transaction.begins Mysql procedure.calls using class properties as parameters - commit(or rollback) rs.populate class properties.set properties used by external functions module terminate - rs.close

and transactions just didn't work. I tried everything imaginable for 2 weeks. As soon as I declared and closed rs inside the transaction, everything worked perfectly!

0
source

Source: https://habr.com/ru/post/1310802/

More articles: