Very slow update query

[rob_levy]

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

 

[rob_levy]

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