CODEWITHSUNDEEP
excelvbams-accessdao
PL200
PL200

Reputation: 741

How to speed up bulk 'upsert' in Microsoft Access

I am performing an 'upsert' in Access using a VBA dictionary. I am looping my dictionary keys, and if the key exists in the Access table then I update it. If not, it inserts it. However my table contains 200k+ rows, and this makes the code perform incredibly slow (e.g., 5 minutes and not even 5% had done) as iteration requires that the whole table be searched for the 'LOC' (my key).

Is there a way for me to speed up this process significantly? My code is below, any help is appreciated.

Sub UpdateDatabase(dict As Object)

Dim db As Database
Dim rs As DAO.Recordset

Set db = OpenDatabase("C:\XXX\myDB.accdb")
Set rs = db.OpenRecordset("MyTable", dbOpenDynaset)


For Each varKey In dict.Keys()

    'Table is searched for key
    rs.FindFirst "[LOCID] = '" & varKey & "'"
    If rs.NoMatch Then
        'If the key was not found, insert it
        rs.AddNew
        rs!LOCID = varKey
        rs![Status] = "To Start"
        rs.Update
    Else
        'If the key was found, update its status
        rs.Edit
        rs![Status] = "Done"
        rs.Update
    End If
Next

rs.Close
db.Close

Application.StatusBar = False
End Sub

EDIT:

I have found the bottleneck in the above code. It is the line:

rs.FindFirst "[LOCID] = '" & varKey & "'"

This is used to find whether the key is in the database or not. Removing this (and simple inserting the new data) speeds up the process and it is done in seconds. Is there a quick way to determine whether a value is already in a table?

Upvotes: 0

Views: 263

Answers (2)

Cahaba Data
Cahaba Data

Reputation: 622

ditto others' advice; fundamentally don't do a loop. do an action query (append or update).

it is the most frequently seen difference between a 'programmer' and a 'database developer'......

Upvotes: 0

Albert D. Kallal
Albert D. Kallal

Reputation: 49089

First up: How large is the dict collection. (5, 10, or 1000???).

Next up, is the LOCID a indexed column in that table “MyTable”.

If LOCID is already aN indexed column, then you can use the following code. It should speed things up by about 100, or more times:

It is assumed that the table is NOT a linked table, but in fact a table in the database.

Note that you must open the table in question as “actable” – that is the default, so I removed the dbOpenDynaset)

This code will do the trick:

Dim db        As DAO.Database
Dim rs        As DAO.Recordset


Set db = OpenDatabase("C:\XXX\myDB.accdb")
Set rs = db.OpenRecordset("MyTable")     ' <--- note this!!!

rs.Index = "LOCID"

For Each varKey In dict.Keys()

  'Table is searched for key
  rs.Seek "=", varKey
  If rs.NoMatch = True Then
      'If the key was not found, insert it
      rs.AddNew
      rs!LOCID = varKey
      rs![Status] = "To Start"
      rs.Update
  Else
      'If the key was found, update its status
      rs.Edit
      rs![Status] = "Done"
      rs.Update
  End If
Next

rs.Close
db.Close

You will also need to determine the name of the actual index on column LOCID. I used LOCID in above, but you best open the database with access, flip the table into design mode, and then hit the ribbon "indexs" button. If LOCID is the primary key, then a good possibility is the index is named PrimaryKey. So you NEED the name of the index.

Upvotes: 3

Related Questions