vba 将完整的 ADO Recordset 插入到现有的 ACCESS 表中，无需循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32821618/
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
insert full ADO Recordset into existing ACCESS table WITHOUT LOOP
提问by Barrie van Boven
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.
我的 VBA 模块中有一个填充的 ADO 记录集。我在 ACCESS 中还有一个表，它的结构与记录集完全相同。
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)
我想知道：有没有办法将整个记录集插入访问表？（更重要的是：这会明显更快吗）
回答by Tim Williams
Here's a basic example (run from excel in this case) which illustrates using a disconnected recordset to add records.
这是一个基本示例（在这种情况下从 excel 运行），它说明了使用断开连接的记录集来添加记录。
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
回答by Parfait
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.
VBA 记录集实际上存在于运行时调用的内存中，直到它们包含到保存到硬盘的实际物理格式（即，csv、txt、xlsx、xml、数据库临时表）中。这类似于 R 或 Python pandas、SAS 数据集、PHP 数组和其他数据结构中的数据帧。
Consider exporting your ADO in such a format using CopyFromRecordsetmethods into an Excel spreadsheet to be saved as csv, txt, xlsx, or xml. Alternatively, you can use the Savemethod to save recordset in a persistent format type like xml.
考虑使用CopyFromRecordset方法以这种格式将 ADO 导出到 Excel 电子表格中，以保存为 csv、txt、xlsx 或 xml。或者，您可以使用Save方法以持久格式类型（如 xml）保存记录集。
Then, append resultant file to MS Access table with its automated data migration features:
然后，使用其自动数据迁移功能将生成的文件附加到 MS Access 表中：
- For spreadsheets:
DoCmd.TransferSpreadsheet - For txt, csv, or other delimited files:
DoCmd.TransferText - For xml files:
Application.ImportXML - For local or ODBC/OLEDB linked database tables:
INSERT INTOappend SQL query
- 对于电子表格：
DoCmd.TransferSpreadsheet - 对于 txt、csv 或其他分隔文件：
DoCmd.TransferText - 对于 xml 文件：
Application.ImportXML - 对于本地或 ODBC/OLEDB 链接的数据库表：
INSERT INTO追加 SQL 查询
回答by PV22
To accomplish this with a SQL statement you use the SELECT/INSERT... IN [Designate DB A; record posted to] or FROM... IN [Designate DB B; record original source]
要使用 SQL 语句完成此操作，请使用 SELECT/INSERT... IN [Designate DB A; 记录发布到] 或发自... IN [指定 DB B; 记录原始来源]
You can only use the IN statement once in a single query. Therefore you create the other connection using the ADODB connection to determine the other source connection.
在单个查询中只能使用一次 IN 语句。因此，您使用 ADODB 连接创建另一个连接以确定另一个源连接。
Function example()
Dim dB_External As String
Dim db_Local As String
Dim cnLocal As ADODB.Connection
Dim cnExternal As ADODB.Connection
Set cnLocal = CurrentProject.Connection
Set cnExternal = New ADODB.Connection
cnExternal .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\...accdb;Persist Security Info=False;"
dB_External = "C:\Users\...accdb"
db_LOCAL = "C:\Users\...accdb"
Example A:
strSQL = "INSERT INTO *Local table to receive records* (Column Designations)"
strSQL = strSQL & " SELECT ( *Corresponding records from external table* )"
strSQL = strSQL & " FROM *External table name* IN '" & dB_External & "'"
cnLocal.Execute (strSQL)
I use the above code, with the local ADODB connections if I select from a single external table.
我使用上面的代码，如果我从单个外部表中选择，则使用本地 ADODB 连接。
Example B:
strSQL = "INSERT INTO *Local table to receive records* (Column Designations) IN '" & dblocal & "'"
strSQL = strSQL & " ( *Corresponding records from external table* )"
strSQL = strSQL & " FROM *External table name*
cnExternal.Execute (strSQL)
I use the above code using the external ADODB connection, if I select involves joining multiple tables in the external db.
我使用外部 ADODB 连接使用上面的代码，如果我选择涉及加入外部数据库中的多个表。
回答by Gustav
No. There is no reverse equivalent - could be SetRows- to the method GetRows.
不。没有反向等价物——可能是SetRows——该方法GetRows。
