Increase speed of multiple inserts into Access DB from VBA Dictionary

Question

I am attempting to take a VBA Dictionary and either:

Insert a new row into the database if it does not exist
Update the row if it does

While my current code works for this, it runs extremely slowly for the thousands of records I may need to update, and other solutions I have found on this site do not really achieve what I am after. Could anyone help me achieve this? My code so far is below:

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

Any help is appreciated.

Depending on how many inserts vs updates you have, you could first run the update and check on the "records affected" value, then if that's zero run the insert. https://stackoverflow.com/questions/12676747/how-to-get-the-affected-rows-in-vba-ado-execute — Tim Williams, Jan 02 '19 at 06:35
I am surprised code works. Should use `[ ]` to delimit table and field names, not apostrophes: `[All SAMs Backlog]`. If the LocID is common key, can't you join tables? — June7, Jan 02 '19 at 07:11
Possible duplicate of [Upserting in MS-access](https://stackoverflow.com/questions/6199417/upserting-in-ms-access) — June7, Jan 02 '19 at 07:11

score 1 · Accepted Answer · answered Jan 02 '19 at 07:22

1

Either:

Write the content of the dictionary to a temp table, then run a query as described here:

Update or insert data in table

or:

Open [All SAMs Backlog] as a recordset, loop the dictionary to add or edit records as needed, then close the recordset.

answered Jan 02 '19 at 07:22

Gustav

53,498
7
29
55

Increase speed of multiple inserts into Access DB from VBA Dictionary

1 Answers1