VB6 DAO Open connection to Database, constantly polling (FASTIO_*)

I have created a small application as a test harness to troubleshoot a larger potential problem.

Here is my harness:

- VB6 application, with 1 form, and a .bas module referencing DAO 360
- MS Access 2003 DB, with one table (Employees) containing 2 records.
- Process Monitor running, with the filter set to my exe name


In the .bas module I have created public variables for the workspace, database and recordset objects.

In a command button on the main form I create the workspace, open the database, create a recordset, itterate through the recordset displaying the field values, all is well.

The problem is that until the database is closed I can see (Via Process Monitor) that there are 2 hits against the DB every 5 seconds (FASTIO_READ and FASTIO_CHECK_IF_POSSIBLE)

Can anyone off insight as to why this is happening, and is this by design in a DAO Setup?
DymaxionDeveloperAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<It basically says that the data is not actually updated in the database for 5 seconds (by default). I changed this value in my code to 60000 ms (a minute) and sure enough this is the case. (Update a record on Machine A, and the new value is not displayed on Machine B until the pagetimeout is reached)>>

  Yes and that's what told me it was the PageTimeout at work that you were seeing.

<<I'm shocked that this works this way, basically user on Machine B may never be looking at/working with current data. We treat all of our data as stale anyway, and issue a new query against the DB whenever we want a fresh copy of the data, but even then it may not even be accurate :-(>>
 
  Yes and no; it's simply 5 seconds out of date.  But it is still a consistent picture of the data at a given point in time.

  Got to remember that JET is a client based RDBMS.  There is no server side process or one central point that is handling everything, so a consistent picture of the database that includes all changes at a specific instant is not something you can have except in a single user situation.

<<Is it possible to update a record immediately and not have the constant disk access (I came across begintransaction/commit transaction)?>>

  Put the PageTimeout to a very high value.  Then after saving a record, do:

 dbEngine.Idle, dbRefreshCache

  That will flush everything to the OS level (you might have caching at the OS and disk levels, so an immediate write to disk is not a guarentee, but as far as JET is concerned the record(s) have been written), but you won't have the constant page timeout at work.

  Transactioning won't help; all it is saying is that whatever commits you have will be done as a unit or not at all (it's meant for updating multiple tables).  But it doesn't do anything in regards to the cache.

JimD.

0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

<<Can anyone off insight as to why this is happening, and is this by design in a DAO Setup?>>

  It is by design.  JET maintains a local page cache and what your seeing is it maintaining that cache.

  Opening exclusive my help, as it then flags you as the only user in the DB.

JimD.
0
 
DymaxionDeveloperAuthor Commented:
Thanks Jim, Do you know if there ia any way that  I can adjust the polling interval?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
What process monitor are you using ?

mx
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

 Use the dbEngines SetOption method and adjust the PageTimeout:

   dbEngine.Setoption dbPageTimeout      , 10000

  would be 10 seconds (page timeout is set in milli-seconds).

JimD.
0
 
DymaxionDeveloperAuthor Commented:
"Process Monitor" (ProcMon) from sysinternals!
0
 
DymaxionDeveloperAuthor Commented:
Thanks for the great information! I did a Google search and came across this MS KB article:

http://support.microsoft.com/kb/149618

It basically says that the data is not actually updated in the database for 5 seconds (by default). I changed this value in my code to 60000 ms (a minute) and sure enough this is the case. (Update a record on Machine A, and the new value is not displayed on Machine B until the pagetimeout is reached)

I'm shocked that this works this way, basically user on Machine B may never be looking at/working with current data. We treat all of our data as stale anyway, and issue a new query against the DB whenever we want a fresh copy of the data, but even then it may not even be accurate :-(

Is it possible to update a record immediately and not have the constant disk access (I came across begintransaction/commit transaction)?

Any other input would be greatly appreciated!

TIA!
0
 
DymaxionDeveloperAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.