Very Slow Update Query

Hi all,

I'm doing the simplest little 'INSERT INTO' type operation here with recordsets in Access 97 (SR2). Inserting into a table whose name is stored in the Const TABLE_CANDIDS.

I have a little piece of code that doesn't move on until the changes are committed to the table (usually this is just a single long integer being added).

The transaction takes up to a second to commit, and sometime doesn't commit at all. I tried putting begintrans and committrans around the loop but it seems to make the problem worse.

Can anyone spot my error?

Thanks,
Rob

' Empty the TABLE_CANDIDS table then start filling it with the current
' list of Candidates
DoCmd.RunSQL "DELETE * FROM " & TABLE_CANDIDS
Set rstCandIDs = CurrentDb.OpenRecordset(TABLE_CANDIDS)

' Find the CandIDs for all selected items (default to select all)
For Each objListItem In objListView.ListItems
If objListItem.Selected Or Not blnSelectionOnly Then
rstCandIDs.AddNew
rstCandIDs!CandIDFiltered = CLng(objListItem.Text)
rstCandIDs.Update
lngCount = lngCount + 1
End If
Next objListItem
rstCandIDs.Close

' Wait for up to 1 second until the new value(s) are committed.
Screen.MousePointer = 11
datStart = Now()
Do While IsNull(DLookup("CandIDFiltered", TABLE_CANDIDS, "CandIDFiltered <> 0"))
If DateDiff("s", datStart, Now()) > 100 Then
Exit Do
End If
DoEvents
Loop
Screen.MousePointer = 0