MS Access链接表更新重复问题技术求助

问题背景 #

使用MS Access前端连接Azure SQL Server，通过ADODB.Command执行MERGE语句更新库存数据，TblQuantities和TblTrnLines均为链接表。该操作大部分时间正常，但约1%的调整操作会出现无规律的重复更新：同一用户同一应用连接下，相同TransactionID对应的更新会在随机时间间隔重复执行多次。

执行MERGE的VBA代码如下：

Dim cmd As ADODB.Command
   
Set cmd = New ADODB.Command

Dim svr As String
Dim db As String
Dim drv As String
Dim con As ConnectionData
Dim QueryText As String
Dim TransactionID As Integer

TransactionID = Eval("Forms![FrmTransactions]![ID]")

QueryText = "MERGE TblQuantities t" & vbCrLf & _
           "USING (SELECT TblTrnLines.ProductID, TblTrnLines.[Type], Sum(TblTrnLines.Quantity) AS SumOfQuantity" & vbCrLf & _
           "FROM TblTrnLines" & vbCrLf & _
           "WHERE (((TblTrnLines.Deleted) = 0) And ((TblTrnLines.StockTransID) = " & TransactionID & "))" & vbCrLf & _
           "GROUP BY TblTrnLines.ProductID, TblTrnLines.[Type]) s" & vbCrLf & _
           "ON (s.ProductID = t.ProductID) and (s.[type] = t.StockTypeID) and (1 = t.StockLocationID)" & vbCrLf & _
           "WHEN MATCHED" & vbCrLf & _
           "THEN UPDATE set" & vbCrLf & _
           "t.Quantity = (t.Quantity + s.SumOfQuantity)" & vbCrLf & _
           "WHEN Not MATCHED" & vbCrLf & _
           "THEN INSERT (ProductID, StockTypeID, StockLocationID, Quantity)" & vbCrLf & _
           "VALUES (s.ProductID, s.[type], 1, s.SumOfQuantity);"

With cmd
  .ActiveConnection = "ODBC;DRIVER=ODBC Driver 13 for SQL Server;SERVER=[servername].database.windows.net;DATABASE=[dbname];UID=[userid];PWD=[userpassword]"
  .CommandType = adCmdText
  .CommandText = QueryText
  .Execute
End With

审计触发器记录的重复更新数据（简化后）：

重复更新的时间间隔无规律（本次案例为76秒、58秒、12秒、24秒），所有操作均来自同一用户连接。

可能原因 #

1. ODBC驱动自动重试：使用的ODBC Driver 13 for SQL Server在网络波动时，若客户端未收到Azure SQL的执行确认，会自动重试命令，导致重复更新。Azure SQL的网络环境偶尔出现的延迟或丢包会触发该逻辑。
2. MERGE缺乏幂等性：当前MERGE仅依赖StockTransID和Deleted=0过滤数据，若执行后未标记记录为已处理，后续表单刷新、关联逻辑等操作可能再次触发相同MERGE，导致重复更新。
3. 连接池会话复用异常：Azure SQL的连接池可能复用了未正确清理的会话，导致之前的MERGE命令被重复执行。
4. 前端事件重复触发：若触发MERGE的按钮存在事件绑定问题，或用户快速重复操作，可能导致命令多次提交（但从审计时间间隔看，该可能性较低）。

解决办法 #

1. 给MERGE添加幂等性控制 #

在TblTrnLines中新增Processed字段（bit类型，默认0），执行MERGE时仅处理未标记的记录，执行完成后标记为已处理，确保同一TransactionID的记录只会被处理一次：

修改后的SQL逻辑（含参数化查询，避免SQL注入）：

MERGE TblQuantities t
USING (
    SELECT ProductID, [Type], Sum(Quantity) AS SumOfQuantity
    FROM TblTrnLines
    WHERE Deleted = 0 AND StockTransID = ? AND Processed = 0
    GROUP BY ProductID, [Type]
) s
ON (s.ProductID = t.ProductID) AND (s.[Type] = t.StockTypeID) AND (t.StockLocationID = 1)
WHEN MATCHED THEN
    UPDATE SET t.Quantity = t.Quantity + s.SumOfQuantity
WHEN NOT MATCHED THEN
    INSERT (ProductID, StockTypeID, StockLocationID, Quantity)
    VALUES (s.ProductID, s.[Type], 1, s.SumOfQuantity);

UPDATE TblTrnLines
SET Processed = 1
WHERE Deleted = 0 AND StockTransID = ? AND Processed = 0;

对应的VBA代码调整：

QueryText = "MERGE TblQuantities t" & vbCrLf & _
           "USING (SELECT TblTrnLines.ProductID, TblTrnLines.[Type], Sum(TblTrnLines.Quantity) AS SumOfQuantity" & vbCrLf & _
           "FROM TblTrnLines" & vbCrLf & _
           "WHERE (((TblTrnLines.Deleted) = 0) And ((TblTrnLines.StockTransID) = ?) And ((TblTrnLines.Processed) = 0))" & vbCrLf & _
           "GROUP BY TblTrnLines.ProductID, TblTrnLines.[Type]) s" & vbCrLf & _
           "ON (s.ProductID = t.ProductID) and (s.[type] = t.StockTypeID) and (1 = t.StockLocationID)" & vbCrLf & _
           "WHEN MATCHED" & vbCrLf & _
           "THEN UPDATE set" & vbCrLf & _
           "t.Quantity = (t.Quantity + s.SumOfQuantity);" & vbCrLf & _
           "UPDATE TblTrnLines SET Processed = 1 WHERE Deleted=0 AND StockTransID=? AND Processed=0;"

With cmd
  .ActiveConnection = "[connection_string]"
  .CommandType = adCmdText
  .CommandText = QueryText
  ' 添加参数
  .Parameters.Append .CreateParameter("TransID", adInteger, adParamInput, , TransactionID)
  .Parameters.Append .CreateParameter("TransID2", adInteger, adParamInput, , TransactionID)
  .Execute
End With

2. 升级ODBC驱动 #

将ODBC Driver 13升级为ODBC Driver 18 for SQL Server，该版本修复了多个连接重试和稳定性问题，优化了Azure SQL适配性。修改连接字符串：

ODBC;DRIVER=ODBC Driver 18 for SQL Server;SERVER=[servername].database.windows.net;DATABASE=[dbname];UID=[userid];PWD=[userpassword];Encrypt=yes

3. 用事务保证原子性 #

将MERGE和标记操作放在事务中执行，确保要么全部成功，要么全部回滚，避免部分执行导致的重复触发：

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

On Error GoTo ErrorHandler

conn.Open "[connection_string]"
conn.BeginTrans

' 执行MERGE和更新操作
With cmd
  .ActiveConnection = conn
  .CommandType = adCmdText
  .CommandText = QueryText
  .Parameters.Append .CreateParameter("TransID", adInteger, adParamInput, , TransactionID)
  .Parameters.Append .CreateParameter("TransID2", adInteger, adParamInput, , TransactionID)
  .Execute
End With

conn.CommitTrans
Exit Sub

ErrorHandler:
conn.RollbackTrans
MsgBox "操作失败：" & Err.Description, vbCritical

4. 前端操作防抖 #

在Access表单中，触发MERGE的按钮点击事件执行时，先禁用按钮，执行完成后再启用，防止用户重复点击：

Private Sub btnUpdate_Click()
    Me.btnUpdate.Enabled = False
    ' 执行MERGE的代码逻辑
    ' ...
    Me.btnUpdate.Enabled = True
End Sub

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