MS Access(Jet)事务，工作空间 [英] MS Access (Jet) transactions, workspaces

问题描述

我在提交事务时遇到了麻烦(使用Access 2003 DAO).就像我从未调用过BeginTrans一样—我在CommitTrans上收到错误3034，您试图先提交或回滚事务而没有先开始事务" ；并将更改写入数据库(大概是因为它们从未包装在事务中).但是，如果逐步执行BeginTrans .

I am having trouble with committing a transaction (using Access 2003 DAO). It's acting as if I never had called BeginTrans -- I get error 3034 on CommitTrans, "You tried to commit or rollback a transaction without first beginning a transaction"; and the changes are written to the database (presumably because they were never wrapped in a transaction). However, BeginTrans is run, if you step through it.

• 我正在使用DBEngine(0)工作区在Access环境中运行它.
• 我要添加记录的表都通过Jet数据库连接(到同一数据库)并使用DAO.Recordset.AddNew/Update打开.
• 在开始BeforeTrans之前打开连接.
• 在交易过程中，我没有做任何奇怪的事情，例如关闭/打开连接或多个工作区等.
• 有两个嵌套的事务级别.基本上，它在外部事务中包装了多个插入，因此，如果有任何失败，它们都会失败.内部事务运行没有错误，这是外部事务不起作用.

以下是我调查并排除的一些内容:

Here are a few things I've looked into and ruled out:

• 事务分散在几种方法中，BeginTrans和CommitTrans(和Rollback)都位于不同的位置.但是，当我尝试以这种方式运行事务的简单测试时，这似乎并不重要.

• The transaction is spread across several methods and BeginTrans and CommitTrans (and Rollback) are all in different places. But when I tried a simple test of running a transaction this way, it doesn't seem like this should matter.

我以为数据库连接超出本地范围可能会关闭，即使我有另一个全局"引用(老实说，我也不确定DAO对dbase连接做了什么).但这似乎并非如此-恰好在提交，连接及其记录集处于活动状态之前(我可以检查其属性，EOF = False等)

I thought maybe the database connection gets closed when it goes out of local scope, even though I have another 'global' reference to it (I'm never sure what DAO does with dbase connections to be honest). But this seems not to be the case -- right before the commit, the connection and its recordsets are alive (I can check their properties, EOF = False, etc.)

我的CommitTrans和Rollback在事件回调中完成. (基本上，非常重要:解析器程序在解析结束时会引发'onLoad'事件，我可以通过提交或回退在处理过程中插入的内容来进行处理，具体取决于是否发生任何错误.)但是，再次尝试一个简单的测试，似乎并不重要.

My CommitTrans and Rollback are done within event callbacks. (Very basically: a parser program is throwing an 'onLoad' event at the end of parsing, which I am handling by either committing or rolling back the inserts I made during processing, depending on if any errors occurred.) However, again, trying a simple test, it doesn't seem like this should matter.

有什么想法对我不起作用吗?

Any ideas why this isn't working for me?

谢谢.

编辑5月25日

这是(简体)代码.与交易有关的关键点是:

Here is the (simplified) code. The key points having to do with the transaction are:

• 工作空间是DBEngine(0)，在公共(全局)变量APPSESSION中引用.
• 在下面的LoadProcess.cache中打开数据库连接，请参见行Set db = APPSESSION.connectionTo(dbname_).
• BeginTrans在LoadProcess.cache中被调用.
• CommitTrans在process__onLoad回调中被调用.
• 在process__onInvalid回调中调用回滚.
• 记录集更新是在process__onLoadRow，logLoadInit和logLoad中完成的

• The workspace is DBEngine(0), referenced within 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.
• Rollback is called in the process__onInvalid callback.
• Recordset updates are done in process__onLoadRow, logLoadInit, and logLoad

埃里克

'------------------- 
'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

推荐答案

使用事务是通过定义一个工作空间(不一定是新工作空间)，然后在该工作空间上开始事务来完成所需的操作.这样做，然后在一切正常的情况下提交事务.骨架代码:

Transactions are used by defining a workspace (it doesn't have to be a new one) and then beginning the transaction on that workspace, doing what you need to do with it, and then commiting the transaction if all is well. Skeletal 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, within the block where you [do whatever], you can pass off the workspace and databases and recordsets to subroutines, but the top-level control structure should remain in one place.

您的代码无法做到这一点-相反，您依赖于全局变量.全球变量都是邪恶的.不要使用它们.而是将私有变量作为参数传递给要对其进行操作的子例程.我还要说，永远不要传递工作空间-只能传递您使用工作空间创建的对象.

Your code does not do that -- instead, you depend on global variables. GLOBAL VARIABLES ARE EVIL. Don't use them. Instead, pass private variables as parameters to the subroutines you want to operate on them. I would also say, never pass the workspace -- only pass the objects you've created with the workspace.

一旦您了解了这一点，也许它可以帮助您解释代码应该完成的工作(从阅读开始，我还没有最模糊的主意)，然后我们可以为您提供建议.做错了.

Once you've absorbed that, maybe it will help you explain what your code is supposed to accomplish (which I haven't the foggiest idea of from reading through it), and then we can advise you as to what you're doing wrong.

这篇关于MS Access(Jet)事务，工作空间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！