insert full ADO Recordset into existing ACCESS table WITHOUT LOOP

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.CloseEnd SubFunction 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 = rvEnd Function

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

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