How to insert ADO Recordset into MS Access Table

vba ms-access insert ms-access-2010
zach picture zach · Dec 4, 2012 · Viewed 33.6k times · Source

PROBLEM

I want to insert the current recordset row into a MS Access table. I am currently getting this error

Syntax error (missing operator) in query expression 'rs[columnname]'

CODE

Here is my current code, I am trying to grab all the columns and insert them into a new table.

DoCmd.RunSQL "INSERT INTO tblSummary_Appl_Usage_score VALUES (rs[Configuration], rs[User Input / Output])"

I am not quite sure what i am missing.

Answer

HansUp picture HansUp · Dec 4, 2012

Open tblSummary_Appl_Usage_score as a DAO recordset. Then use its .AddNew method to create a new row and store the values from your ADO recordset.

Dim db As DAO.database
Dim rsDao As DAO.Recordset
Set db = CurrentDb
Set rsDao = db.OpenRecordset("tblSummary_Appl_Usage_score", dbOpenTable, dbAppendOnly)
rsDao.AddNew
rsDao![Configuration] = rs![Configuration]
rsDao![User Input / Output] = rs![User Input / Output]
rsDao.Update

With this approach, your code needn't be adapted differently based on the recordset field data types. It will work correctly regardless of data type as long as the matching fields are both the same or compatible data types.

Related questions

Code to loop through all records in MS Access

I need a code to loop through all the records in a table so I can extract some data. In addition to this, is it also possible to loop through filtered records and, again, extract data? Thanks!

Read More
Get length of array?

I'm trying to get the length of an array, yet I keep getting this error: Object required Am I doing something wrong? Dim columns As Variant columns = Array( _ "A", "ID", _ "D", "Name") Debug.Print columns.Length ' Error: Object required

Read More
How to add default signature in Outlook

I am writing a VBA script in Access that creates and auto-populates a few dozen emails. It's been smooth coding so far, but I'm new to Outlook. After creating the mailitem object, how do I add the default signature to …

Read More