insert full ADO Recordset into existing ACCESS table WITHOUT LOOP

Question

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)

Answer 1

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