vba 如何将 ADO 记录集插入 MS Access 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13712692/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert ADO Recordset into MS Access Table
提问by zach
PROBLEM
问题
I want to insert the current recordset row into a MS Access table. I am currently getting this error
我想将当前记录集行插入到 MS Access 表中。我目前收到此错误
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.
我不太确定我错过了什么。
采纳答案by HugoLemos
If the type fields in your table tblSummary_Appl_Usage_scoreare numbers, use this:
如果表tblSummary_Appl_Usage_score中的类型字段是数字，请使用：
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] & """)"
回答by HansUp
Open tblSummary_Appl_Usage_scoreas a DAO recordset. Then use its .AddNewmethod to create a new row and store the values from your ADO recordset.
打开tblSummary_Appl_Usage_score的DAO recordset。然后使用它的.AddNew方法创建一个新行并存储 ADO 记录集中的值。
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.
使用这种方法，您的代码不需要根据记录集字段数据类型进行不同的调整。只要匹配的字段是相同或兼容的数据类型，无论数据类型如何，它都会正常工作。
