将完整的 ADO Recordset 插入到现有的 ACCESS 表中，无需循环 [英] insert full ADO Recordset into existing ACCESS table WITHOUT LOOP

问题描述

我的 VBA 模块中有一个填充的 ADO 记录集.我在 ACCESS 中还有一个表，它的结构与记录集完全相同.

I have a filled ADO recordset in my VBA module. I also have a table in ACCESS that has exactly the same structure as the recordset.

现在我使用遍历每个数据集记录的循环(这很好)填充表格.

Now I fill the table using a loop (which is fine) going through each dataset record.

我想知道的是:有没有办法将整个记录集插入访问表?(更重要的是:这会更快吗)

What I am wondering: is there a way to insert an entire recordset into the access table? (and more importantly: would this be significantly faster)

推荐答案

这是一个基本示例(在这种情况下从 excel 运行)，它说明了使用断开连接的记录集添加记录.

Here's a basic example (run from excel in this case) which illustrates using a disconnected recordset to add records.

Sub Tester()

    Dim con As ADODB.Connection, rs As ADODB.Recordset
    Dim i As Long

    Set con = getConn()

    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient '<<<< important!

    'get an empty recordset to add new records to
    rs.Open "select * from Table1 where false", con, _
             adOpenDynamic, adLockBatchOptimistic

    'disconnect the recordset and close the connection
    Set rs.ActiveConnection = Nothing
    con.Close
    Set con = Nothing

    'add some new records to our test recordset
    For i = 1 To 100
        rs.AddNew
        rs("UserName") = "Newuser_" & i
    Next i

    'reconnect to update
    Set con = getConn()
    Set rs.ActiveConnection = con

    rs.UpdateBatch '<<< transfer to DB happens here: no loop!

    rs.Close 

    'requery to demonstrate insert was successful
    rs.Open "select * from Table1", con, _
            adOpenDynamic, adLockBatchOptimistic

    Do While Not rs.EOF
        Debug.Print rs("ID").Value, rs("UserName").Value
        rs.MoveNext
    Loop

    rs.Close
    con.Close
End Sub

Function getConn() As ADODB.Connection
    Dim rv As New ADODB.Connection
    Dim strConn As String

    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" _
     & "Data Source = " & ThisWorkbook.Path & "Test.accdb"

    rv.Open strConn
    Set getConn = rv
End Function

这篇关于将完整的 ADO Recordset 插入到现有的 ACCESS 表中，无需循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！