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

I don't understand what the second part of the code is for. The records are committed with the update method.

Hi Pam,

well this is just the thing that confuses me.

I've found that with the above code, the table is not always updated straight away. It can take up to a few seconds and if the code moves on before this has happened I get into trouble later on.

Incidentally, I have since changed the Recordset.AddNew .Update method with a DoCmd.RunSQL(INSERT INTO...) type statement and this seems to speed the process up enormously.

I still seem to need the little routine after this to check that the data has been committed before moving on.

Could it be a problem with my Jet Engine? It seems to be working asynchronously and operates slower than whatever parses the VBA code, thus getting me into this data commit problem whereby the VBA has run off and carried on while the Jet Engine is still labouring over the INSERT statement.

Does any of this make any sense to anyone?

Apologies if not.

Rob