Solved

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

Posted on 2011-03-21
8
1,165 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 57
ID: 35183517

<<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
ID: 35183553
Thanks Jim, Do you know if there ia any way that  I can adjust the polling interval?
0
 
LVL 75
ID: 35183673
What process monitor are you using ?

mx
0
Independent Software Vendors: 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!

 
LVL 57
ID: 35183698

 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
 

Author Comment

by:DymaxionDeveloper
ID: 35183703
"Process Monitor" (ProcMon) from sysinternals!
0
 

Author Comment

by:DymaxionDeveloper
ID: 35192342
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
ID: 35192810
<<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
ID: 36496180
Thanks!
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

739 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