MS访问回滚错误处理回滚

Question

在我的程序中，我有一个函数，它做三件事:它在一些表中做几次插入，它创建一个pdf文档，它发送一封电子邮件，在附件中包含创建的文档。我在解决错误处理时遇到了问题。

    DAO.DBEngine.BeginTrans
    On Error GoTo failed
    
    CurrentDb.Execute "INSERT INTO tblClaimStatus ( ClaimID, ClaimStatusTypeID, UserID) " & _
        "VALUES (" & claimId & ", 2," & CurrentUserId & ");", dbFailOnError
    CurrentDb.Execute "Insert INTO tblClaimPartner (ClaimId, OriginalDeadline, NachfristDeadline) " & _
        "VALUES (" & claimId & ", '" & Me.OriginalDeadlineTextbox & "', '" & Me.NewDeadlineTextbox & "');", dbFailOnError
    
    WriteGraceDocument (claimId)
    SendEmail (claimId)
    DAO.DBEngine.CommitTrans

问题是，如果有任何事情失败，我希望能够回滚SQL-transaction。但我还需要在Subs和SendEmail中尚未提交的数据。

我该如何解决这个问题？

Answer 1

让您的函数返回成功或不成功

DAO.DBEngine.BeginTrans

' Validate the values before attempting to insert these.
Success = ValidateInserts(claimId, 2, CurrentUserId, Me!OriginalDeadlineTextbox.Value, Me!NewDeadlineTextbox.Value)

If Success Then    
    CurrentDb.Execute "INSERT INTO tblClaimStatus ( ClaimID, ClaimStatusTypeID, UserID) " & _
        "VALUES (" & claimId & ", 2," & CurrentUserId & ");", dbFailOnError
    CurrentDb.Execute "Insert INTO tblClaimPartner (ClaimId, OriginalDeadline, NachfristDeadline) " & _
        "VALUES (" & claimId & ", '" & Me.OriginalDeadlineTextbox & "', '" & Me.NewDeadlineTextbox & "');", dbFailOnError
End If
    
If Success Then
    Success = WriteGraceDocument(claimId)
End If
If Success Then
    Success = SendEmail (claimId)
End If

If Success Then
    DAO.DBEngine.CommitTrans
Else
    DAO.DBEngine.RollBack
End If