Problems with Latency after Inserting a new record

[WHM]

Hi All!
I am having a very bizzare problem that I do not seem to be able to find a solution for:

When I add a record to a table using SQL (ie INSERT INTO...) and then I refresh a recordset that should contain the new record sometimes it shows up, sometimes I get a bookmark related error, and sometimes the record does not show up at all until I force another refresh.

Back Ground:
I am using VB6 as a Front End
I am using DAO (I know, but I am.)
I am using Access 97
I am using a dual P3 800 Server with Win 2000 pro
I have 2 Win 2000 Pro work stations
I have one Win 98SE Work Station

The problems are independant of the machine being used.

The problems began when I moved the database from the win 98 machine to the server.

The problems persist even when I move the database back to the Win 98 machine.

The affected forms and their code have remained relatively unchanged for the past five years; however I have added new forms and modules to the Front-End.

I had a large number of problems when I Upgraded (?) to VB6 from VB5, and I am still having problems attempting to upgrade to Access 2000, though these problems should not effect the compiled version of my front end accessing my Access 97 database.

I have created a routine that Kills time by executing a long series of math problems and "Do Events" in a lengthy Do Loop. If I extend the length of the loop so that it "Pauses" for a second or two the latency problem vanishes, but it is very iritating to "kill time" in this manner.

Thanks for any help!

Hunter

 

[gwinn7]

Just taking a wild stab at this...

The problem solution perhaps is with the JET Engine. Have you tried Dbengine.Idle in your loop? Here is an Access 97 Help File excerpt on this subject....

"The Idle method allows the Microsoft Jet database engine to perform background tasks that may not be up-to-date because of intense data processing. This is often true in multiuser, multitasking environments that don't have enough background processing time to keep all records in a Recordset current.

Usually, read locks are removed and data in local dynaset-type Recordset objects are updated only when no other actions (including mouse movements) occur. If you periodically use the Idle method, Microsoft Jet can catch up on background processing tasks by releasing unneeded read locks.

Specifying the optional dbRefreshCache argument forces any pending writes to .mdb files, and refreshes memory with the most current data from the .mdb file."

If nothing, does this give you any additional ideas?

Gary
gwinn7

 

[WHM]

Gary--gwinn7,
Thank you very much. I have put your idea into my loop, and it seems to be helping, but I am having to use numbers like 20,000 for <num>, and that seems a bit excessive, the delay is about 1 second....I feel that there has to be an underlying problem. If you have any other ideas I would love to give them a try.

Public Sub KillTime(num As Long)
Dim cnt As Long
Dim calc As Double

For cnt = 1 To num
calc = cnt ^ (1 / 3)
calc = cnt ^ (1 / 5)
calc = cnt ^ (1 / 7)
calc = cnt ^ (1 / 11)
calc = cnt ^ (1 / 13)
calc = cnt ^ (1 / 17)
calc = cnt ^ (1 / 23)
calc = cnt ^ (1 / 29)
calc = cnt ^ (1 / 31)
DBEngine.Idle (dbRefreshCache)
DBEngine.Idle
Next cnt

For cnt = 1 To num
DoEvents
calc = cnt ^ (1 / 3)
DoEvents
calc = cnt ^ (1 / 5)
DoEvents
calc = cnt ^ (1 / 7)
DoEvents
calc = cnt ^ (1 / 11)
DBEngine.Idle (dbRefreshCache)
DBEngine.Idle
Next cnt

End Sub