VBA可以将多个记录快速插入访问数据库 [英] VBA to insert many records into access DB fast

问题描述

OK，所以我有一个产生相当大量记录的电子表格（〜3500）

OK so I have a spreadsheet that produces a reasonably large amount of records (~3500)

我有以下脚本将它们插入到我的访问数据库中： / p>

I have the following script that inserts them into my access db:

Sub putinDB()
Dim Cn As ADODB.Connection, Rs As ADODB.Recordset
Dim MyConn, sSQL As String

Dim Rw As Long, c As Long
Dim MyField, Result
Dim x As Integer
Dim accName As String, AccNum As String, sector As String, holding As String,  holdingvalue As Double, holdingdate As Date
theend = lastRow("Holdings", 1) - 1
'Set source
MyConn = "S:\Docs\Harry\Engine Client\Engine3.accdb"
'Create query
Set r = Sheets("Holdings").Range("a2")
x = 0
Do
Application.StatusBar = "Inserting record " & x + 1 & " of " & theend
accName = r.Offset(x, 0)
AccNum = r.Offset(x, 4)
sector = r.Offset(x, 2)
holding = r.Offset(x, 1)
holdingvalue = r.Offset(x, 3)
holdingdate = r.Offset(x, 5)

sSQL = "INSERT INTO Holdings (AccName, AccNum, Sector, Holding, HoldingValue, HoldingDate)"
sSQL = sSQL & " VALUES ('" & Replace(accName, "'", "''") & "', '" & AccNum & "', '" & sector & "', '" & Replace(holding, "'", "''") & "', '" & holdingvalue & "', #" & holdingdate & "#)"
Debug.Print (sSQL)
 'Create RecordSet
Set Cn = New ADODB.Connection
With Cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .CursorLocation = adUseClient
    .Open MyConn
    Set Rs = .Execute(sSQL)
End With
x = x + 1
Loop While r.Offset(x, 0) <> "" Or x < 15
Application.StatusBar = False
End Sub

麻烦的是，是它逐个循环遍历每个记录，每次重建并执行查询，这会导致执行速度非常慢（在我的电脑上每秒约2-3个记录）

The trouble is, is that it loops through each record one-by-one, rebuilds and executes the query each time which results in very slow execution (about 2-3 records per second on my PC)

有没有办法让vba一次性将整个范围插入数据库，而不必循环？
谢谢

Is there a way to have vba insert the whole range into the DB in one go without having to loop through? Thanks

推荐答案

好的，傻我经过一点修补，结果是把

OK, silly me. After a bit of tinkering it turns out that putting the

Set Cn = New ADODB.Connection
With Cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.CursorLocation = adUseClient
.Open MyConn
End With

位于循环之外使得它更快。

bit outside the loop makes it far quicker.

这篇关于VBA可以将多个记录快速插入访问数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！