Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-03-21
8
Medium Priority
?
1,188 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 58
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

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

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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

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!

Question has a verified solution.

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

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…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

688 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