Access VBA通过ADODB更新SQL Server布尔字段从True改False失败

环境与症状 #

• 前端：Access 365（32位），运行于Windows 10/11的Access 365 Runtime（32位）
• 后端：AWS RDS上的Microsoft SQL Server Express（64位，版本15.0.4198.2）
• 技术栈：使用ADODB 2.8（VBA引用Microsoft ActiveX Data Objects 2.8 Library）操作Recordset
• 症状：新增将布尔字段从true改为false的代码后，触发错误#-2147217864，提示Row cannot be located for updating. Some values may have been changed since it was last read.
• 排查：已隔离为单元测试（代码如下），确认无其他代码修改记录集，尝试多种CursorType和LockType组合无效，SQL Server字段已设置默认值。

单元测试代码 #

Private Sub TestRelistingDataChangeProcess()
    On Error GoTo TestFail
    
    Dim itemSku As String
    itemSku = "1234"
       
    Dim verifySql As String
    verifySql = StrFormat("SELECT failedImport FROM dbo.myTable WHERE SKU = '{0}'", itemSku)

    Dim rsSql As String
    rsSql = StrFormat("UPDATE dbo.myTable SET failedImport = 0 WHERE SKU = '{1}'", itemSku)
    ExecuteCommandPassThrough rsSql
    
    rsSql = "PARAMETERS SKU Text ( 255 ); SELECT * FROM myTable WHERE SKU=[SKU]"
    
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = GetCurrentConnection()
    cmd.CommandText = rsSql

    Dim param As ADODB.Parameter
    Set param = cmd.CreateParameter(Name:="[SKU]", Type:=adLongVarChar, Value:=itemSku, Size:=Len(itemSku))
    cmd.Parameters.Append param

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open cmd, , adOpenDynamic, adLockOptimistic

    With rs
        Debug.Print "1. Setting field to TRUE."
        .Fields("failedImport") = True
        .Update
        Assert.IsTrue ExecuteScalarAsPassThrough(verifySql)

        Debug.Print "2. Setting field to FALSE."
        .Fields("failedImport") = False
        .Update
        Assert.IsFalse ExecuteScalarAsPassThrough(verifySql)
    End With
    
    Assert.Succeed

TestExit:
    Exit Sub

    TestFail:
    Assert.Fail "Test raised an error: #" & Err.Number & " - " & Err.Description
    Resume TestExit
End Sub

问题原因 #

1. VBA布尔值与SQL Server bit类型的映射冲突：
   SQL Server的bit字段仅存储0或1，但VBA中True对应数值-1，False对应0。当通过ADODB将True写入SQL Server时，SQL Server会自动将-1转换为1；但在乐观锁模式下，ADODB生成更新语句时，会用读取到的1（对应VBA的True）作为WHERE条件的一部分，当尝试将字段设为False（0）时，ADODB可能错误地用VBA原始的-1去匹配SQL Server中的1，导致条件不匹配，无法定位到要更新的行。

2. 乐观锁的字段匹配逻辑缺陷：
   使用adLockOptimistic时，ADODB会将Recordset读取时的所有字段值作为更新条件。即使只修改布尔字段，其他字段的类型转换差异、隐式变化字段（如timestamp）都会触发行找不到的错误。

3. ADODB 2.8对可空bit字段的处理兼容问题：
   即使设置了默认值，ADODB 2.8对SQL Server可空bit字段的状态切换处理存在bug，从非空状态改为0时，乐观锁的条件判断会出现逻辑错误。

解决办法 #

1. 直接使用参数化UPDATE语句替代Recordset更新 #

绕过Recordset的乐观锁逻辑，直接执行SQL更新，这是最可靠的方案：

Private Sub TestRelistingDataChangeProcess()
    On Error GoTo TestFail
    
    Dim itemSku As String
    itemSku = "1234"
       
    Dim verifySql As String
    verifySql = StrFormat("SELECT failedImport FROM dbo.myTable WHERE SKU = '{0}'", itemSku)

    ' 初始化字段为0
    Dim updateCmd As ADODB.Command
    Set updateCmd = New ADODB.Command
    updateCmd.ActiveConnection = GetCurrentConnection()
    updateCmd.CommandText = "UPDATE dbo.myTable SET failedImport = ? WHERE SKU = ?"
    
    ' 设置为True（对应SQL Server的1）
    updateCmd.Parameters.Append updateCmd.CreateParameter(, adInteger, adParamInput, , 1)
    updateCmd.Parameters.Append updateCmd.CreateParameter(, adLongVarChar, adParamInput, Len(itemSku), itemSku)
    updateCmd.Execute
    Assert.IsTrue ExecuteScalarAsPassThrough(verifySql)

    ' 设置为False（对应SQL Server的0）
    updateCmd.Parameters(0).Value = 0
    updateCmd.Execute
    Assert.IsFalse ExecuteScalarAsPassThrough(verifySql)
    
    Assert.Succeed

TestExit:
    Exit Sub

TestFail:
    Assert.Fail "Test raised an error: #" & Err.Number & " - " & Err.Description
    Resume TestExit
End Sub

2. 替换VBA布尔值为SQL Server兼容的数值 #

直接用0/1代替VBA的True/False赋值，避免类型转换冲突：

With rs
    Debug.Print "1. Setting field to TRUE."
    .Fields("failedImport") = 1 ' 代替True
    .Update
    Assert.IsTrue ExecuteScalarAsPassThrough(verifySql)

    Debug.Print "2. Setting field to FALSE."
    .Fields("failedImport") = 0 ' 代替False
    .Update
    Assert.IsFalse ExecuteScalarAsPassThrough(verifySql)
End With

3. 优化Recordset的查询与锁类型 #

• 仅查询需要更新的字段，减少乐观锁的条件字段数量：

  rsSql = "PARAMETERS SKU Text ( 255 ); SELECT SKU, failedImport FROM myTable WHERE SKU=[SKU]"
  

• 改用adLockPessimistic锁类型，避免乐观锁的条件匹配问题：

  rs.Open cmd, , adOpenDynamic, adLockPessimistic
  

4. 确保表有明确的主键或唯一约束 #

给myTable的SKU字段添加唯一非空约束，让ADODB优先用唯一字段定位行：

ALTER TABLE dbo.myTable ADD CONSTRAINT UQ_myTable_SKU UNIQUE (SKU);

5. 升级ADODB版本 #

将VBA引用从Microsoft ActiveX Data Objects 2.8 Library改为Microsoft ActiveX Data Objects 6.1 Library（或更高版本），新版本对SQL Server的类型映射处理更完善，能避免旧版本的兼容性bug。

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