Access VBA 事务处理（上）：用 DAO 让一张单据要么全存，要么全不存
摘要： 主子表保存、一次改多张表，中间一步失败就会留下半截数据，这时候需要事务。本文用一张出库单做例子，讲 DAO 的 BeginTrans、CommitTrans、Rollback 怎么写，以及 dbFailOnError、RecordsAffected、绑定窗体和锁数量这几个容易出问题的地方。access开发|access培训|access框架|请添加edonsoft。
Hi，大家好！
在 Access 里写数据，只改一张表、一条记录的时候，成功就是成功，失败就是失败，用不着事务。
需要事务的，是一次保存要动好几张表的时候。最常见的是主子表：主表一条，明细好几条，两边必须同时存进去。再复杂一点，保存一张单据还要同时改库存、写流水、更新客户余额。这几步之间有先后，只要中间有一步失败，前面已经写进去的数据就成了半截，各张表之间对不上。
事务做的就是这件事：把多张表的写入捆成一次操作，要么都成功，要么都撤回。
这个话题我分两篇写，这一篇讲 DAO，下一篇讲 ADO 连 SQL Server。
事务是什么
事务（Transaction）是数据库里的一个工作单位。一组修改放进同一个事务，数据库就把它们当成一件事来处理：要么全部生效，要么全部不生效，不存在做了一半的状态。
开始事务、执行修改、提交或回滚，这是固定的流程。提交（Commit）之后，修改才正式写进数据库；回滚（Rollback）就是把这次事务里做过的修改全部撤销。
并发时的几种问题
多人同时操作同一批数据，如果隔离做得不够，会出现下面几种情况：
| 问题 | 现象 |
|---|
| 脏读 | 读到了别人还没提交的数据，对方后来回滚了，你读到的就是不存在的数 |
| 不可重复读 | 同一个事务里读两次同一条记录，中间被别人改了，两次结果不一样 |
| 幻读 | 同一个事务里按同一条件查两次，中间别人插入了新记录，第二次多出几行 |
| 丢失更新 | 两人先后读到库存 100，各扣 10 再写回，结果是 90 而不是 80 |
数据库用隔离级别来控制允许出现哪些问题。标准的四个级别从低到高是：读未提交、读已提交、可重复读、串行化。级别越高，问题越少，但锁得越多，并发越差。
ACE 数据库不能像 SQL Server 那样设置隔离级别，并发主要靠记录锁和页锁来处理。所以这一篇重点放在原子性上，隔离级别在下一篇讲 SQL Server 时还会提到。
最后一个丢失更新，Access 里很常见。先把库存读到变量里，减完再写回，就会有这个问题。后面代码里扣库存用的是 SET Qty = Qty - 5，由数据库在当前值上直接减，不经过 VBA 变量，就是为了避开它。
回到 Access 的单据
下面用一张出库单做例子。保存时要做三件事：写一条主表记录，写几条明细，按明细扣库存。
放进事务以后，全部成功就提交，中间任何一步失败，前面做过的也全部撤回，数据库回到保存之前的样子。用户看到的结果只有两种：这张单存好了，或者什么都没发生。
在 DAO 里，事务挂在 Workspace 对象上，就三个方法：
| 方法 | 作用 |
|---|
BeginTrans | 开始事务 |
CommitTrans | 提交，所有修改正式生效 |
Rollback | 回滚，撤销 BeginTrans 之后的修改 |
CurrentDb 属于默认工作区 DBEngine.Workspaces(0)，所以在当前数据库里写数据，就在这个工作区上开事务。
先把表建好
例子用四张表。前三张放在后端，最后一张是前端里的临时表，给明细子窗体用。
tblStock 库存表：
| 字段 | 类型 | 说明 |
|---|
ProductID | 数字（长整型） | 主键 |
Qty | 数字（长整型） | 当前库存 |
tblStockOut 出库主表：
| 字段 | 类型 | 说明 |
|---|
OutID | 自动编号 | 主键 |
OutNo | 短文本（20） | 出库单号，建唯一索引 |
OutDate | 日期/时间 | 出库日期 |
tblStockOutDetail 出库明细：
| 字段 | 类型 | 说明 |
|---|
DetailID | 自动编号 | 主键 |
OutID | 数字（长整型） | 对应主表 |
ProductID | 数字（长整型） | 产品 |
Qty | 数字（长整型） | 出库数量 |
tmpStockOutLine 临时明细，字段只要 ProductID 和 Qty，两个都设为必填。
为什么要多一张临时表，后面讲绑定窗体的时候再说。
保存出库单的完整代码
新建一个标准模块，命名为 modStockOut，放入下面的代码：
Option Compare Database
Option Explicit
Public Function SaveStockOut(ByVal strOutNo As String, ByVal dtOut As Date) As Boolean
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rsHead As DAO.Recordset
Dim rsLine As DAO.Recordset
Dim lngOutID As Long
Dim strSQL As String
Dim blnInTrans As Boolean
Dim lngErr As Long
Dim strErr As String
On Error GoTo ErrHandler
Set ws = DBEngine.Workspaces(0)
Set db = CurrentDb
Set rsLine = db.OpenRecordset("SELECT ProductID, Qty FROM tmpStockOutLine", dbOpenSnapshot)
If rsLine.EOF Then
Err.Raise vbObjectError + 1001, , "出库明细为空。"
End If
ws.BeginTrans
blnInTrans = True
Set rsHead = db.OpenRecordset("tblStockOut", dbOpenDynaset)
rsHead.AddNew
rsHead!OutNo = strOutNo
rsHead!OutDate = dtOut
rsHead.Update
rsHead.Bookmark = rsHead.LastModified
lngOutID = rsHead!OutID
rsHead.Close
Set rsHead = Nothing
Do Until rsLine.EOF
strSQL = "INSERT INTO tblStockOutDetail (OutID, ProductID, Qty) VALUES (" & _
lngOutID & ", " & rsLine!ProductID & ", " & rsLine!Qty & ")"
db.Execute strSQL, dbFailOnError
strSQL = "UPDATE tblStock SET Qty = Qty - " & rsLine!Qty & _
" WHERE ProductID = " & rsLine!ProductID & _
" AND Qty >= " & rsLine!Qty
db.Execute strSQL, dbFailOnError
If db.RecordsAffected <> 1 Then
Err.Raise vbObjectError + 1002, , "产品 " & rsLine!ProductID & " 库存不足或不存在。"
End If
rsLine.MoveNext
Loop
ws.CommitTrans
blnInTrans = False
SaveStockOut = True
ExitHere:
On Error Resume Next
If blnInTrans Then ws.Rollback
If Not rsHead Is Nothing Then rsHead.Close
If Not rsLine Is Nothing Then rsLine.Close
Set rsHead = Nothing
Set rsLine = Nothing
Set db = Nothing
Set ws = Nothing
If lngErr <> 0 Then
MsgBox "出库单没有保存，所有修改已撤销。" & vbCrLf & strErr, vbExclamation
End If
Exit Function
ErrHandler:
lngErr = Err.Number
strErr = Err.Description
Resume ExitHere
End Function
明细里的 ProductID 和 Qty 都来自长整型字段，拼进 SQL 没有注入问题。单号是文本，我放在 Recordset 里赋值，没有拼字符串，单号里有单引号也不会出错。
出库窗体上的保存按钮这样调用：
Private Sub cmdSave_Click()
If IsNull(Me.txtOutNo) Or IsNull(Me.txtOutDate) Then
MsgBox "请填写单号和日期。", vbExclamation
Exit Sub
End If
If Me.sfrmLines.Form.Dirty Then Me.sfrmLines.Form.Dirty = False
If SaveStockOut(Me.txtOutNo, Me.txtOutDate) Then
CurrentDb.Execute "DELETE FROM tmpStockOutLine", dbFailOnError
Me.sfrmLines.Requery
MsgBox "出库单已保存。", vbInformation
End If
End Sub
sfrmLines 是明细子窗体控件的名称，源对象绑定 tmpStockOutLine。保存前先让子窗体把正在编辑的那一行写回临时表，不然最后一行会漏掉。
这段代码里几个要注意的地方
没有 dbFailOnError，事务等于白开
这是事务代码里最容易漏的一处。db.Execute strSQL 后面不加 dbFailOnError，语句碰到主键重复、违反索引、类型转换失败这类问题时，有的记录写不进去，VBA 却不报错，程序照常往下走，最后 CommitTrans，事务把一半的结果正式提交了。
事务只在出错时帮你回滚，前提是你得知道出错了。所以事务里的每一条 Execute，我都会加上 dbFailOnError。
库存不足要自己判断
UPDATE tblStock SET Qty = Qty - 5 WHERE ProductID = 3 AND Qty >= 5，库存不够时这条语句不会报错，只是一条记录都没改。对数据库来说这不是错误，对业务来说是。
所以每次扣完库存，我都会看一下 db.RecordsAffected，不是 1 就主动 Err.Raise，让流程走到回滚。
这里有个小坑：RecordsAffected 要用执行语句的那个 db 对象去读。写成 CurrentDb.Execute ... 再读 CurrentDb.RecordsAffected 是拿不到结果的，因为每次调用 CurrentDb 都会返回一个新的 Database 对象。
先回滚，再弹窗
注意 MsgBox 的位置。我把它放在 ws.Rollback 之后。
事务没结束之前，被修改过的数据页会一直锁着。如果在事务中间弹一个对话框，用户去倒杯水再回来点确定，这段时间里别人保存相关数据就可能遇到锁定错误。事务里不要等用户，开始到结束越短越好。
错误处理也用了 Resume ExitHere 这个写法：先记下错误号和说明，跳回清理段，再在 On Error Resume Next 下回滚和关闭对象。清理时就算某个对象已经关闭，也不会在错误处理里再报一次错。
新编号用 LastModified 取
主表是自动编号，明细要用这个编号。AddNew、Update 之后，记录指针并不停在新记录上，需要 rsHead.Bookmark = rsHead.LastModified 把指针移过去，再读 OutID。
回滚以后，这个自动编号就作废了，下一张单会跳过它。自动编号本来就不保证连续，单号不要依赖它，所以表里另有一个 OutNo。
绑定窗体不在你的事务里
这一点经常被问到：我在按钮里开了事务，为什么窗体上改的数据回滚不掉？
因为绑定窗体有自己的保存过程。用户在绑定窗体上改完一条记录，切换记录或者关窗体时，Access 就把这一条单独存了，它不会等你代码里的 CommitTrans。
所以上面的例子没有让子窗体直接绑定 tblStockOutDetail，而是先录到前端的临时表，点保存时再由代码在一个事务里统一写入正式表。录入阶段随便改、随便删，都碰不到正式数据。
拆分了前后端的话，每个用户各用一份前端，临时表放在前端里，互相不会干扰。
事务管不到的事
事务只管数据库里的修改。代码里如果顺手复制了文件、发了邮件、导出了 Excel，回滚时这些都不会撤回。
我的习惯是：事务里只做写库的事，别的操作放在 CommitTrans 成功之后再做。
DoCmd.RunSQL 我也不放进事务。它有自己的 UseTransaction 参数，还会弹确认提示，和 Workspace 事务混在一起，行为不好把握。事务里统一用 db.Execute。
一次改太多记录，会碰到锁数量上限
如果在一个事务里更新几万条记录，可能会遇到 3052 错误，提示超过了文件共享锁的数量。ACE 默认每个文件最多 9500 个锁，事务没结束，锁就一直不释放。
可以在执行前临时调大：
DBEngine.SetOption dbMaxLocksPerFile, 50000
这个设置只对当前会话有效，不改注册表。不过事务越大，占锁越多、时间越长，多人使用时也越容易互相等待。批量处理我更愿意按批次拆开，每一批单独提交。
嵌套事务，我一般不用
DAO 的 Access 工作区支持嵌套事务，最多五层。但内层的 CommitTrans 并不是真的落地，外层一旦 Rollback，内层提交过的也会一起撤销。
写成多层以后，读代码的人很难看出哪一步才算真正提交。我的做法是一个业务动作只开一层事务，由最外层的保存函数负责开始和结束，被调用的子过程只写数据、遇错就抛出去。
这一篇的范围
上面的写法适合 Access 自己的表，也就是 ACE 数据库，包括拆分后链接的 accdb 后端。
如果后端是 SQL Server，表通过 ODBC 链接进来，我不建议依赖 Workspace 事务来保证一致性，更稳妥的是直接连 SQL Server，在同一个连接上开事务，或者把整段逻辑放进存储过程。下一篇用 ADO 讲这一种。
技术培训
定制开发
技术支持