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)

Submaxillary

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

Ambroseambrosi

thanks, I use something similar. The question was how to avoid having to use a loop, and simply dump the entire recordset into the table such as you can do between two access tables (SELECT * INTO SomeTable FROM SomewhereElse) But useful nonetheless ! – Submaxillary

The only loops I have are to load the test recordset and to display the result after the update. The data loading all occurs in the single UpdateBatch Line. What loop are you referring to? – Ambroseambrosi

Oops realized that I missed the most important part. But I see now. That was what I was hoping to find. Very helpful thanks for clarifying – Submaxillary

@tim Weirdly I don't think your answer actually answers the question. Surely it was about building a recordset and then putting it back into a different or any! table. Parfait's answer outlines a method to do this, what's your opinion on this. Your answer put it back into the table it came from (which will cover 99.9% of scenarios including the OP's apparently!) – Dough

@Dough - OP's question begins "I have a filled ADO recordset", so it did not seem to me that the question was about filling the recordset, but about how to insert those records from the recordset the OP already has into a table without looping. The code I posted was an example of using the UpdateBatch method to do this. Since the OP didn't post any code related to creating their recordset, and I was feeling lazy, I created my example recordset using a "no records" select from the destination table, to ensure the fields matched exactly. – Ambroseambrosi

@Tim Ok. I liked your answer it was useful o me. However, I'm also interested in whether you could get data from one table into a recordset then put it back into another table. Or build a rs then put it into a chosen table. Is what parfait wrote the best /only way? – Dough

@Dough - you can build a disconnected recordset from scatch without having to query it from an existing table: databasejournal.com/features/msaccess/article.php/3846361/… If you then add records to that recordset and want to insert them into a table you can use the approach shown in my example. You just have to make sure the table and recordset field types match (and sometimes the easiest way to do this is to create the recordset as I did above instead of by hand...) – Ambroseambrosi

@Tim thanks. I see. I open in on the table I intend to insert it into. But what if I want to get records from one table or query, adjust them and then insert them into another table. The method you outlined used .Open to specify the destination table, which I suspect will "clear" the recordset. – Dough

@Dough - yes you are correct and that was something i hadn't really thought about too much when I wrote my answer: your disconnected recordset needs to have the "source" defined, so that when you call UpdateBatch the correct table gets updated... – Ambroseambrosi

A slight limitation then. I suppose you can copy all data in a RS to another RS (based on a different table) and get around it that way. I wouldn't know how having done little ADO, but I like what I'm hearing! – Dough

Hey Tim - just wanna say your answer is BEAUTIFUL and has helped me a lot. Have a good day :) – Andesine

Answer 2

VBA Recordsets exist virtually in memory called during runtime until they are contained into an actual physical format (i.e., csv, txt, xlsx, xml, database temp table) saved to hard disk. This is akin to data frames in R or Python pandas, SAS datasets, PHP arrays, and other data structures.

Consider exporting your ADO in such a format using CopyFromRecordset methods into an Excel spreadsheet to be saved as csv, txt, xlsx, or xml. Alternatively, you can use the Save method to save recordset in a persistent format type like xml.

Then, append resultant file to MS Access table with its automated data migration features:

• For spreadsheets: DoCmd.TransferSpreadsheet
• For txt, csv, or other delimited files: DoCmd.TransferText
• For xml files: Application.ImportXML
• For local or ODBC/OLEDB linked database tables: INSERT INTO append SQL query

---

Handicap

ADO to Excel to Access. Sounds like it will be quicker than a looping INSERT statement. It's shame that you can't write a SQL statement that INSERTS into the table using a SELECT statement taking data from a recordset (or perhaps an array.....Hmm. VALUES... – Dough

Answer 3

To accomplish this with a SQL statement you use the SELECT/INSERT... IN [Designate DB A; record posted to] or FROM... IN [Designate DB B; record original source]

You can only use the IN statement once in a single query. Therefore you create the other connection using the ADODB connection to determine the other source connection.

Function example()
Dim dB_External As String
Dim db_Local As String
Dim cnLocal As ADODB.Connection
Dim cnExternal As ADODB.Connection

Set cnLocal = CurrentProject.Connection
Set cnExternal = New ADODB.Connection
cnExternal .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\\...accdb;Persist Security Info=False;"

dB_External = "C:\Users\\...accdb"
db_LOCAL = "C:\Users\\...accdb"

Example A:
strSQL = "INSERT INTO *Local table to receive records* (Column Designations)"
strSQL = strSQL & " SELECT ( *Corresponding records from external table* )"
strSQL = strSQL & " FROM *External table name* IN '" & dB_External & "'"
cnLocal.Execute (strSQL)

I use the above code, with the local ADODB connections if I select from a single external table.

Example B:
strSQL = "INSERT INTO *Local table to receive records* (Column Designations) IN '" & dblocal & "'"
strSQL = strSQL & " ( *Corresponding records from external table* )"
strSQL = strSQL & " FROM *External table name*
cnExternal.Execute (strSQL)

I use the above code using the external ADODB connection, if I select involves joining multiple tables in the external db.

Gallfly

Answer 4

No. There is no reverse equivalent - could be SetRows - to the method GetRows.

Weitzel