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)
You can add a new record to a table-type or dynaset-type Recordset object by using the AddNew method. Use the AddNew method to create a record you can edit. Assign values to each of the record's fields. Use the Update method to save the new record.
You can use the MoveFirst, MoveLast, MoveNext, and MovePrevious methods; the Move method; and the AbsolutePosition, AbsolutePage, and Filter properties to reposition the current record, assuming the provider supports the relevant functionality. Forward-only Recordset objects support only the MoveNext method.
The default cursor for an ADO Recordset is a forward-only, read-only cursor located on the server. Using the Open method on a Recordset object opens a cursor that represents records from a base table, the results of a query, or a previously saved Recordset.
The ADO Recordset object is used to hold a set of records from a database table. A Recordset object consist of records and columns (fields). In ADO, this object is the most important and the one used most often to manipulate data from a database.
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
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:
DoCmd.TransferSpreadsheet
DoCmd.TransferText
Application.ImportXML
INSERT INTO
append SQL queryIf you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With