Slow in retrieving Access DB record

[keithl]

I have a problem where a record is updated to a table (IVR_STATUS) from program A and retrieve from program B on the same computer. I discovered that when the record is updated by program A, it took 3-5 seconds for program B to detect that the record has been updated. Here's the spec of the computer - Intel P3-733Mhz, 128MB RAM, 20GB HDD, Windows 2000 Server. IVR_STATUS table contains only 4 records with 2 columns each.
The funny thing is, program A only took less than a second to retrieve the record after it had updated the table.
Here is code for program A (to update)-
***********************************************************
If Not IsNull(rstMsgQueue!IVR_channel) Or Trim(rstMsgQueue!IVR_channel) <> &quot;&quot; Then
' =>
Call Trace(&quot;Update IVR_Status table - Status: &quot; & cStatus)
Set rstIVR_Status = cDatabase.OpenRecordset(&quot;IVR_Status&quot
rstIVR_Status.Index = &quot;IVR01&quot;
rstIVR_Status.Seek &quot;=&quot;, rstMsgQueue!IVR_channel
If Not rstIVR_Status.NoMatch Then
rstIVR_Status.Edit
rstIVR_Status!Status = cStatus
rstIVR_Status.Update
' =>
Call Trace(&quot;IVR_Status table updated&quot
End If
rstIVR_Status.Close
End If

and the code for program B (to retrieve) - it's a bit long, sorry...
**********************************************************

Function ChkStatus()
Dim rsProcess_Status As Recordset
Dim tStartTime As Date
Dim intDelay As Integer
Dim strDummy As String
Dim tDelay As Date, cCounter As Integer

On Error Resume Next
ChkStatus = False
tStartTime = Time
intDelay = 25: cCounter = 0

Do
' =>
cCounter = cCounter + 1
Call Trace(CStr(cCounter)) ' & &quot; - Line &quot; & cLine)
strDummy = DoEvents()
Set rsProcess_Status = myDB.OpenRecordset(&quot;select * from ivr_status where channel = '&quot; + Format(Vbocx1.PhoneLine, &quot;00&quot + &quot;'&quot

If rsProcess_Status.EOF Then
rsProcess_Status.Close
Exit Do
Else
If Not IsNull(rsProcess_Status!Status) And Trim(rsProcess_Status!Status) <> &quot;&quot; Then
' =>
Call Trace(&quot;ivr_status!Status = &quot; & rsProcess_Status!Status)
If rsProcess_Status!Status = &quot;SUCCESS&quot; Then
ChkStatus = True
rsProcess_Status.Close
Exit Do
Else
rsProcess_Status.Close
Exit Do
End If
Else
rsProcess_Status.Close
End If
End If
tDelay = Time - tStartTime
Loop Until Second(tDelay) >= intDelay
End Function

Thanks very much in advance.

Keithl

 

[IFELSETHEN]

what's the database?

 

[bjd4jc]

Welcome to the world of Jet Limitations!

I was told my microsoft that there is no way around this... it goes with the territory of a file system database - or something like that.

Good luck!

 

[keithl]

We are using Access 97 database and DAO 3.51.