Excel VBA连接Access出现“无法更新，当前已锁定”问题排查

核心问题分析 #

你的代码存在几个关键问题，直接导致了多用户场景下的锁定冲突：

• 全局连接长期占用资源：accessCon作为全局变量会持续保持连接打开状态，多个用户同时持有活跃连接时，Access的文件级锁定机制极易触发冲突，尤其是连接未及时释放时。
• 游标类型与锁定策略不匹配：adOpenStatic是静态游标，生成的是数据快照，本身不支持实时更新操作，即便设置adLockOptimistic也无法发挥乐观锁定的作用——乐观锁需要搭配支持更新的游标类型才能生效。
• 连接模式的潜在冲突：手动计算的模式值16 + 3虽然对应adModeShareDenyNone + adModeShareReadWrite，但直接用常量名更可靠，且Access对共享模式的处理需要确保没有其他连接持有排他锁。

修复步骤 #

1. 废弃全局连接，使用短生命周期连接 #

每次数据库操作时临时创建连接，操作完成后立即关闭释放，避免长期占用：

' 在操作函数内创建连接，替代全局accessCon
Dim accessFileLoc As String: accessFileLoc = ThisWorkbook.Path & "\DB\GrabDB.accdb"
Dim accessCon As Object
Set accessCon = CreateObject("ADODB.Connection")
' 使用常量值替代数值计算，避免出错
accessCon.Mode = 16 ' adModeShareDenyNone：允许所有用户访问，不拒绝任何连接
accessCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & accessFileLoc

' 执行记录集操作...

' 操作完成后立即关闭并释放连接
If accessCon.State = 1 Then accessCon.Close
Set accessCon = Nothing

2. 调整游标与锁定类型（按需选择） #

• 如果需要更新记录：改用支持更新的adOpenKeyset游标，配合乐观锁定：

Dim accessQuery As String
accessQuery = "SELECT [ID] FROM user_table"
Dim accessRS As Object
Set accessRS = CreateObject("ADODB.Recordset")

accessRS.CursorLocation = adUseClient
accessRS.CursorType = 1 ' adOpenKeyset：支持更新的游标类型
accessRS.LockType = 3 ' adLockOptimistic：乐观锁定

accessRS.Open accessQuery, accessCon

' 示例更新操作（如果需要）
' accessRS.AddNew
' accessRS!ID = NewID
' accessRS.Update

If accessRS.State = 1 Then accessRS.Close
Set accessRS = Nothing

• 如果仅需查询数据：改用只读模式，彻底减少锁定概率：

accessRS.CursorType = 0 ' adOpenForwardOnly：仅向前遍历的高效游标
accessRS.LockType = 1 ' adLockReadOnly：只读模式，无锁定

3. 检查数据库文件的共享配置 #

• 确认存放GrabDB.accdb的文件夹，所有用户都拥有读写权限；
• 打开数据库时不要勾选「独占打开」选项，确保数据库处于共享模式。

4. 优先使用SQL直接执行更新（如果涉及写操作） #

如果你的业务包含数据更新，尽量用Execute直接执行SQL语句，避免打开记录集带来的锁定：

' 示例：直接执行UPDATE，无需打开记录集
accessCon.Execute "UPDATE user_table SET [Field] = 'Value' WHERE [ID] = 123"

内容的提问来源于stack exchange，提问作者Geoff L