How to insert ADO Recordset into MS access table

Question

PROBLEM

I want to insert the current row of a recordset into an 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 paste them into a new table.

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

I'm not quite sure what is missing.

Answer 1

If the type fields in the tblSummary_Appl_Usage_score table are numbers, use the following:

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

If the type is a string, use this:

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

Answer 2

Open tblSummary_Appl_Usage_score as a DAO recordset . Then use its .AddNew method to create a new line and save 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 does not have to be adapted differently based on the data types of the recordset fields. It will work correctly regardless of the data type if the corresponding fields are the same or compatible data types.