Solved

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

Posted on 2011-03-21
8
1,122 Views
Last Modified: 2012-05-11
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
Comment
Question by:DymaxionDeveloper
  • 4
  • 3
8 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility

<<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
 

Author Comment

by:DymaxionDeveloper
Comment Utility
Thanks Jim, Do you know if there ia any way that  I can adjust the polling interval?
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
What process monitor are you using ?

mx
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility

 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:DymaxionDeveloper
Comment Utility
"Process Monitor" (ProcMon) from sysinternals!
0
 

Author Comment

by:DymaxionDeveloper
Comment Utility
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 125 total points
Comment Utility
<<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
 

Author Closing Comment

by:DymaxionDeveloper
Comment Utility
Thanks!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now