为何未执行Recordset.Update，AddNew却更新了Access数据库？

我在Excel中编写了一个VBA函数，用于向Access数据库上传记录。模拟执行Recordset.AddNew后、rs.Update前触发错误的场景，按预期未执行Update时数据库不应保存记录，但实际记录已被保存。以下是复现问题的VBA代码（需提前创建名为'EAS'的Access数据库，包含'Statements'表及'GroupID'字段）：

模块变量 #

'Connection
Const PWORD As String = "password"
Dim sSQL As String 'SQL string
Dim cnn As New ADODB.Connection 'ADODB Connection object
Dim rs As New ADODB.Recordset 'ADODB Recordset object

'Paths
Const EAS_DB As String = "J:\yourpath\EAS.accdb"

打开数据库连接 #

Private Sub openConnect()
'opens the DB connection, let errors occur

Dim cnnString As String
    cnnString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & EAS_DB & ";" & _
                "Jet OLEDB:Database Password='" & PWORD & "';"
    cnn.Open cnnString
End Sub

关闭数据库连接 #

Private Sub closeConnect()
'closes recordset and connection cleanly, handle errors


Dim rsFail As Boolean
    rsFail = True

On Error GoTo eh:
    If Not rs Is Nothing Then
        If rs.State = adStateOpen Then
            rs.Close
            Set rs = Nothing
        End If
    End If      

closeCnn:
    rsFail = False
    If Not cnn Is Nothing Then
        If cnn.State = adStateOpen Then
            cnn.Close
            Set cnn = Nothing
        End If
    End If
Exit Sub
   
eh:
    If rsFail Then
        GoTo closeCnn:
    Else
        MsgBox Err.Source & vbNewLine & vbNewLine & Err.Description, , "Error in closeConnect"
    End If
End Sub

数据库记录写入函数 #

Private Function createPendingRecord(ByVal idarray As Variant, ByVal amount As Double) As Boolean
'Attempts to create a new pending record in the database, returns boolean result

Dim data As Variant
Dim flds As Variant  

'Get data and field arrays for upload
data(0) = "12345"
flds(0) = "GroupID"

On Error GoTo eh:
  
    openConnect
    rs.Open "Statements", cnn, adOpenDynamic, adLockOptimistic
    rs.AddNew flds, data
    Error 0
    rs.Update
    createPendingRecord = True

cleanExit:
    closeConnect
    Exit Function

eh:
    MsgBox Err.Source & vbNewLine & vbNewLine & Err.Description, , "Error in createPendingRecord"
    Resume cleanExit
   
End Function

原因解释 #

• 问题核心在于带参数的rs.AddNew flds, data调用：ADODB Recordset的这个重载方法会在执行时自动完成记录的新增和提交，不需要再手动调用rs.Update。也就是说，你执行这条语句的时候，数据已经被写入数据库了，后续的rs.Update属于冗余操作。
• 你在AddNew之后触发错误跳过了手动Update，但此时带参数的AddNew已经完成了记录的保存，所以数据库中会出现这条记录。
• 如果要实现“先准备数据，手动控制提交时机”的逻辑，应该使用无参数的AddNew，单独给字段赋值后再调用Update，示例如下：

rs.AddNew
rs("GroupID") = "12345"
' 此处触发错误的话，不会执行Update，记录不会被保存
rs.Update

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