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.
ActiveX Data Objects (ADO) are an easy-to-use yet extensible technology for adding database access to your Web pages. You can use ADO to write compact and scalable scripts for connecting to OLE DB compliant data sources, such as databases, spreadsheets, sequential data files, or e-mail directories.
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.
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.
If the type fields in your table tblSummary_Appl_Usage_score
are numbers, use this:
DoCmd.RunSQL "INSERT INTO tblSummary_Appl_Usage_score VALUES (" & rs![Configuration] & "," & rs![User Input / Output] & ")"
If the type is string, use this:
DoCmd.RunSQL "INSERT INTO tblSummary_Appl_Usage_score VALUES (""" & rs![Configuration] & """,""" & rs![User Input / Output] & """)"
If 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