ADO latency

[Jeff A via AccessMonster.com]

Hi,

I used to use ADO methods to update certain tables. The problem is sometimes,
it takes a second or two before the updates get committed to the database. It
especially becomes a problem when there's additional, immediate processing
that depends on the updated tables. For example, I used ADO to add new
records to a table and then a report was run to pick up the new records. More
often than not, the report couldn't "see" the records right away. Usually I
need a second run to pick up the records.

But if I use DoCmd.RunSQL, the table gets updated right away. Is there a way
to flush/write the ADO data immediately to the table rather than being
buffered? The ADO I used connected to another MS Access database. I tried
configuring and reconfiguring the ODBC connection (setting SafeTransactions
to 0, etc.) at the control panel and it still didn't solve the problem.

Any thoughts?

Thanks, jeff

 

[Brendan Reynolds]

From previous posts in the newsgroups, it appears that setting the ADO
Connection object to Nothing after updating the data and re-opening a new
connection before trying to read the changed data sometimes resolves these
problems.

A possible alternative would be to use DAO instead, and call DbEngine.Idle
dbRefreshCache before attempting to read the changed data.

 

[Jeff A via AccessMonster.com]

Yup, i did the first one (closing and re-opening an ADO connection).
sometimes it works, sometimes it doens't.

Ok, i'll try DAO instead.

thanks for the tip!

From previous posts in the newsgroups, it appears that setting the ADO
Connection object to Nothing after updating the data and re-opening a new
connection before trying to read the changed data sometimes resolves these
problems.

A possible alternative would be to use DAO instead, and call DbEngine.Idle
dbRefreshCache before attempting to read the changed data.

Hi,

[quoted text clipped - 21 lines]

Thanks, jeff

 

[Brendan Reynolds]

Just closing and re-opening? Or closing, setting to Nothing, and
re-instantiating and opening? For example ...

rst.Close
Set rst = Nothing
Set rst = New ADODB.Recordset
'set properties here
rst.Open

My understanding, from those previous newsgroup discussions, is that it is
the setting to Nothing that flushes the cache. Just closing and re-opening
without setting to Nothing may not be enough.

--
Brendan Reynolds

Yup, i did the first one (closing and re-opening an ADO connection).
sometimes it works, sometimes it doens't.

Ok, i'll try DAO instead.

thanks for the tip!

From previous posts in the newsgroups, it appears that setting the ADO
Connection object to Nothing after updating the data and re-opening a new
connection before trying to read the changed data sometimes resolves these
problems.

A possible alternative would be to use DAO instead, and call DbEngine.Idle
dbRefreshCache before attempting to read the changed data.

Hi,

[quoted text clipped - 21 lines]

Thanks, jeff