Excel前端操作Access：加速批量INSERT语句及替代循环方案问询

嘿，针对你现在用循环单条INSERT导致效率低下的问题，我给你几个更高效的实现思路，不仅能大幅提升插入速度，还能顺畅获取生成的PayID并回写到其他表：

方法1：使用ADODB.Recordset批量添加（推荐，适合需实时获取每个PayID的场景） #

Recordset的批量操作能大幅减少与数据库的交互次数，比循环执行单条INSERT高效得多，还能直接获取每条新增记录的AutoNumber值：

Dim rst As ADODB.Recordset
Dim apNumber As String
Dim payIDs As Collection ' 存储生成的PayID
Dim x As Integer

apNumber = Sheets("LEDGERTEMPFORM").Range("B2").Value
Set payIDs = New Collection

' 开启事务，提升性能同时保证数据一致性
cnn.BeginTrans

Set rst = New ADODB.Recordset
' 打开可更新的PayPaymentID表记录集
rst.Open "PayPaymentID", cnn, adOpenKeyset, adLockOptimistic

' 批量添加记录
For x = 1 To PayIDnxtRow
    rst.AddNew
    rst!ApNumber = apNumber
    rst.Update ' 提交当前记录
    
    ' 立即获取Access自动生成的PayID
    payIDs.Add rst!PayID
Next x

' 提交事务完成批量插入
cnn.CommitTrans

rst.Close
Set rst = Nothing

' 将PayID写入Excel的PaySeries表
Dim ws As Worksheet
Set ws = Sheets("PaySeries")
ws.Range("B2").Resize(payIDs.Count, 1).ClearContents
For x = 1 To payIDs.Count
    ws.Range("B2").Offset(x - 1, 0).Value = payIDs(x)
Next x

' 批量将PayID插入到Access另一张表（示例表名：PayOtherTable）
If payIDs.Count > 0 Then
    Dim sqlBatchInsert As String
    sqlBatchInsert = "INSERT INTO PayOtherTable(PayID) VALUES "
    For x = 1 To payIDs.Count
        sqlBatchInsert = sqlBatchInsert & "(" & payIDs(x) & "),"
    Next x
    ' 移除末尾多余的逗号
    sqlBatchInsert = Left(sqlBatchInsert, Len(sqlBatchInsert) - 1)
    cnn.Execute sqlBatchInsert
End If

方法2：构造单条多值INSERT语句（适合纯批量插入，后续批量查询PayID） #

如果不需要实时获取每个PayID，而是插入完成后一次性查询，这种方法能把所有插入操作合并为一次数据库请求，效率拉满：

Dim apNumber As String
Dim sqlInsert As String
Dim x As Integer
Dim rst As ADODB.Recordset

apNumber = Sheets("LEDGERTEMPFORM").Range("B2").Value
sqlInsert = "INSERT INTO PayPaymentID(ApNumber) VALUES "

' 构造多值插入的SQL语句
For x = 1 To PayIDnxtRow
    sqlInsert = sqlInsert & "('" & apNumber & "'),"
Next x
' 移除最后多余的逗号
sqlInsert = Left(sqlInsert, Len(sqlInsert) - 1)

' 开启事务执行批量插入
cnn.BeginTrans
cnn.Execute sqlInsert
cnn.CommitTrans

' 一次性查询所有生成的PayID
Set rst = New ADODB.Recordset
rst.Open "SELECT PayID FROM PayPaymentID WHERE ApNumber = '" & apNumber & "' ORDER BY PayID", cnn, adOpenStatic
' 写入Excel的PaySeries表
Sheets("PaySeries").Range("B2").CopyFromRecordset rst

' 批量插入到Access另一张表
If Not rst.EOF Then
    rst.MoveFirst
    Dim sqlBatch As String
    sqlBatch = "INSERT INTO PayOtherTable(PayID) VALUES "
    Do While Not rst.EOF
        sqlBatch = sqlBatch & "(" & rst!PayID & "),"
        rst.MoveNext
    Loop
    sqlBatch = Left(sqlBatch, Len(sqlBatch) - 1)
    cnn.Execute sqlBatch
End If

rst.Close
Set rst = Nothing

关键优化点说明 #

• 事务处理：用cnn.BeginTrans和cnn.CommitTrans包裹批量操作，Access会将所有操作作为一个事务提交，大幅减少磁盘IO和网络往返开销，速度提升明显。
• 减少数据库交互次数：不管是Recordset批量添加还是多值INSERT，都把原来的N次数据库请求压缩为1次或少数几次，这是提升效率的核心。
• 规避循环单条SQL：循环执行单条INSERT会导致频繁的数据库交互，开销极大，批量操作能彻底解决这个问题。

如果PayIDnxtRow数值极大（比如上万条），多值INSERT可能会遇到SQL语句长度限制，此时用Recordset批量添加更稳妥，或者分批次执行多值INSERT（比如每1000条执行一次）。

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