vba批量写入mysql临时表_提高从VBA字典向Access DB中插入多个数据的速度

我正在尝试使用VBA字典,并且:

如果数据库不存在,则将新行插入数据库

如果行更新,则更新该行

虽然我目前的代码可以做到这一点,但对于我可能需要更新的数千条记录,它运行得非常缓慢,而且我在这个站点上找到的其他解决方案并不能真正实现我想要的。有人能帮我做到这一点吗?我的代码如下:

Sub UpdateDatabase(dict As Object)

Dim Conn As Object, StrSQL As String, Rs As Object

Dim hmm As ADODB.Recordset

Set Conn = CreateObject("ADODB.Connection")

Conn.Provider = "Microsoft.ACE.OLEDB.12.0"

Conn.Open "C:\XXXX\cfrv2.accdb"

dictCount = dict.Count

counter = 0

For Each varKey In dict.Keys()

Application.StatusBar = Str(counter) & "/" & Str(dictCount)

counter = counter + 1

StrSQL = "SELECT * FROM `All SAMs Backlog` WHERE [LOCID] = '" & varKey & "'"

Set hmm = Conn.Execute(StrSQL)

If hmm.BOF And hmm.EOF Then

StrSQL = "INSERT INTO `ALL SAMs Backlog` ([SAM], [LOCID], [RTC Date], [CFR Status], [CFR Completed Date], [CFR On Hold Reason], [MDU], [ICWB Issue], [Obsolete]) VALUES (dict.Item(varKey)(0), '" & varKey & "', '20/12/2018', '" & dict.Item(varKey)(1) & "', '02/01/2019', '" & dict.Item(varKey)(2) & "' , '" & dict.Item(varKey)(3) & "' , '" &dict.Item(varKey)(4) & "' , '" & dict.Item(varKey)(5) & "')"

Conn.Execute (StrSQL)

Else

'Update the LOC in the table

StrSQL = "UPDATE `All SAMs Backlog` SET ([CFR Status] = '" & dict.Item(varKey)(1) & "', [CFR On Hold Reason] = '" & dict.Item(varKey)(2) & "', [MDU] = '" & dict.Item(varKey)(3) & "', [ICWB Issue] = '" & dict.Item(varKey)(4) & "', [Obsolete] = '" & dict.Item(varKey)(5) & "')"

Conn.Execute (StrSQL)

End If

Next

Conn.Close

End Sub

感谢您的帮助。