将完整的ADO Recordset插入到现有的ACCESS表中而没有LOOP [英] insert full ADO Recordset into existing ACCESS table WITHOUT LOOP
本文介绍了将完整的ADO Recordset插入到现有的ACCESS表中而没有LOOP的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的VBA模块中有一个填充的ADO记录集.我在ACCESS中也有一个表,该表的结构与记录集完全相同.
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.
现在,我使用遍历每个数据集记录的循环(很好)来填充表格.
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)
推荐答案
这是一个基本示例(在这种情况下是从excel运行),该示例说明了使用断开的记录集添加记录.
Here's a basic example (run from excel in this case) which illustrates using a disconnected recordset to add records.
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
这篇关于将完整的ADO Recordset插入到现有的ACCESS表中而没有LOOP的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文