ADODB - Slow update

[cwtseng]

Greeting,

I have 2 adodb.connections, and 1 recordset for each of the connections. Both recordsets open the same ms-access table.

while I update changes to one recordset and requery on the other recordset, the changes is not reflected on that other recordset immediately. Only after about 1 or 2 seconds then the changes appear when I requery again. That's the problem.

The problem will not occur if I open the connection only just before I want to make changes.

Is there any solution to this? Thanx in advance for reply...

If you do not understand the problem, here is the complete scenario:

1) Open connection A 2) Open recordset A on connection A 3) Open connection B 4) Open recordset B on connection B 5) User input (takes some time) 6) Update recordset B 7) Requery recordset A 8) PROBLEM - Recordset A does not reflect any changes 9) Recordset A requery again after 2 seconds 10) Changes reflected

The problem goes away if I change the procedures to: 1) Open connection A 2) Open recordset A on connection A 3) User input (takes some time) 4) Open connection B 5) Open recordset B on connection B 6) Update recordset B 7) Requery recordset A 8) SUCCESS - Recordset A reflects the changes

Thanx again very much for your time. Your help is greatly appreciated.

[JHausmann]

I'm not clear why you think you need two connections and two recordsets.

[Jaguar]

I'm not completely clear either?

A connection can hold more than one recordset, that is why .NextRecordset is available. In any case. If you are connecting to an Access database, you can add the "Jet and Replication" object reference to your project and then call the following code after major writes to the database and when you are shutting down the connection(s).

Public Sub RefreshCache()
    Dim oEng As JRO.JetEngine
    Set oEng = New JRO.JetEngine
    oEng.RefreshCache oConn
    Set oEng = Nothing
End Sub

Call this function after you do a call (Update/Insert/Delete, etc) to the database to ensure you are pushing the information out to the database so you have the most up-to-date information in the database as soon as possible.

Q200300 - HOWTO: Synchronize Writes and Reads with the Jet OLE DB Provider and ADO

OR

Q180223 HOWTO: Synchronizing Reads and Writes Between Two DAO Processes

If you are using another database system like SQL Server or Oracle, etc, I haven't found any information on this yet...

Anyone?