如何在Access VBA中一次性将数组写入数据表？

逐行执行AddNew/Update会频繁触发磁盘写入和索引维护，带来大量IO开销，以下几种方法可实现类似Excel数组写入的高效批量操作：

方法1：手动控制DAO事务，减少提交次数 #

DAO默认每次Update都会自动提交事务，手动开启事务后批量执行插入再一次性提交，能大幅降低IO操作频率：

Private Sub ArrayToTable_Transaction()
    Dim ArrData As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Integer
    
    ReDim ArrData(1 To 2, 1 To 2)
    ArrData(1, 1) = "Paul"
    ArrData(1, 2) = 27
    ArrData(2, 1) = "Helen"
    ArrData(2, 2) = 47
    
    Set db = CurrentDb
    ' 开启事务
    db.BeginTrans
    
    Set rs = db.OpenRecordset("tblMembers", dbOpenDynaset)
    With rs
        For i = 1 To UBound(ArrData, 1)
            .AddNew
            .Fields(0) = ArrData(i, 1)
            .Fields(1) = ArrData(i, 2)
            .Update
        Next i
        .Close
    End With
    
    ' 提交事务
    db.CommitTrans
    
    Set rs = Nothing
    Set db = Nothing
End Sub

方法2：批量拼接INSERT INTO SQL语句 #

直接生成包含所有数组数据的批量插入SQL，一次性执行，速度极快（注意：Access SQL单条语句有长度限制，数据量超万级时可分批次拼接）：

Private Sub ArrayToTable_BatchSQL()
    Dim ArrData As Variant
    Dim db As DAO.Database
    Dim i As Integer
    Dim sqlStr As String
    Dim valuesStr As String
    
    ReDim ArrData(1 To 2, 1 To 2)
    ArrData(1, 1) = "Paul"
    ArrData(1, 2) = 27
    ArrData(2, 1) = "Helen"
    ArrData(2, 2) = 47
    
    ' 拼接VALUES子句
    valuesStr = ""
    For i = 1 To UBound(ArrData, 1)
        ' 字符串类型需加单引号，内部单引号替换为双单引号避免语法错误
        valuesStr = valuesStr & "('" & Replace(ArrData(i, 1), "'", "''") & "', " & ArrData(i, 2) & "),"
    Next i
    ' 移除末尾多余逗号
    valuesStr = Left(valuesStr, Len(valuesStr) - 1)
    
    sqlStr = "INSERT INTO tblMembers (Name, Age) VALUES " & valuesStr
    
    Set db = CurrentDb
    db.Execute sqlStr, dbFailOnError
    
    Set db = Nothing
End Sub

提示：若数组包含日期类型，需用#包裹日期值；数值类型直接写入即可。

方法3：使用ADODB.Recordset批量更新 #

ADODB支持批量更新模式，适合超大量数据写入，能高效处理复杂数据类型：

Private Sub ArrayToTable_ADODB()
    Dim ArrData As Variant
    Dim rs As ADODB.Recordset
    Dim i As Integer
    
    ReDim ArrData(1 To 2, 1 To 2)
    ArrData(1, 1) = "Paul"
    ArrData(1, 2) = 27
    ArrData(2, 1) = "Helen"
    ArrData(2, 2) = 47
    
    Set rs = New ADODB.Recordset
    ' 打开数据表并设置批量更新锁模式
    rs.Open "tblMembers", CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic
    
    For i = 1 To UBound(ArrData, 1)
        rs.AddNew
        rs.Fields(0) = ArrData(i, 1)
        rs.Fields(1) = ArrData(i, 2)
    Next i
    
    ' 一次性提交所有新增数据
    rs.UpdateBatch
    
    rs.Close
    Set rs = Nothing
End Sub

注意：需提前在VBA编辑器「工具-引用」中勾选Microsoft ActiveX Data Objects x.x Library（建议选最新版本）

三种方法对比：批量SQL拼接速度最快，适合数据量适中的场景；ADODB批量更新适配超大量数据；DAO事务优化无需额外引用，修改原有代码即可实现显著提速。

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