Non-immediate upadate of Access DB

[Jaco]

Hello,
I'm a beginner in VB, earlier I used to programm in Access. I have such a problem. I have two ADO recordsets based on the same query (so they contain the same data). When I make changes in one of the recordset and reqeury the second rs I don't se the changes I've made in the first rs. When, after a few seconds, I make a requery again the chnges are finally visible. Why is it so?? I've heard that ADO is a bit slow with Jet databases but I didn't it's that slow. How do you deal with such a problem??

Regards

Jaco, Poland

 

[keithl]

I have a similar problem. 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

 

[NickISD]

Hi,
Access is a real pain with this problem. It's to do with a cache setting within the registry, even if you change it, it makes no difference. To get round it make a reference in your VB project to Microsoft Jet and Replication objects.

Dim JRO As Jro.JetEngine

' After updates, executes etc addin the following and data will be updated.

JRO.RefreshCache (MyConnection)

Hope this helps.

Regards, Nick

 

[Jaco]

I've managed it this way that I use the same connection for controls when it's necessary to see immediately in one control updates made in other control. Ehhh I hope I've made myself clear...

 

[CCLINT]

Also, an MDB is a file. On some servers the server's read and write cache for files may be set high. The problem exists on the latest versions of Novell (5+) where an advanced file caching is used. Turning off the servers file write cache corrects the problem. Using the same connection, as described above, can solve the problem because the connection is directed to the same cache.
On Nt or W2000 server I haven't seen this problem. But that could be because MS excludes an MDB from the file cache.

 

[Jaco]

OK, I have one more question (particulary for Nick)
I've applied your solution (with RefreshCache method) and it works with &quot;normal&quot; connections but if I try to refresh a connetcion which belongs to the DataEnvironment it generates an error. Do you have any idea how to get round it?

Regards,

Jaco