insert full ADO Recordset into existing ACCESS table WITHOUT LOOP

12
votes

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)

4
vbams-accessadorecordset

4 Answers

15
votes

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
2
votes

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
0
votes

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

0
votes

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.