Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1202
  • Last Modified:

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?
0
DymaxionDeveloper
Asked:
DymaxionDeveloper
  • 4
  • 3
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

<<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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
What process monitor are you using ?

mx
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

 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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
DymaxionDeveloperAuthor Commented:
Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now