Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

ACC2000: DBEngine.Idle dbRefreshCache Refresh Is Not Immediate


View products that this article applies to.

This article was previously published under Q202097

↑ Back to the top


Symptoms

Application must wait (duration=PageTimeout) before being able to see changes made by other applications to a Jet database, even when using the dbRefreshCache flag of the DBEngine.Idle method.

↑ Back to the top


Cause

The database object was created from a Workspace object that is not in the DBEngine.Workspaces collection.

↑ Back to the top


Resolution

Add the Workspace object to the DBEngine.Workspaces collection or use the default Workspace.

↑ Back to the top


Status

This behavior is by design.

↑ Back to the top


More information

With Microsoft Jet database engine 4.0, issuing DBEngine.Idle dbRefreshCache only flushes the cache for Database objects opened from Workspace objects that are in the DBEngine.Workspaces collection. If the Workspace is not in the collection, then its cached records will not be flushed.

NOTE: In addition to refreshing the read cache, the program writing the data must flush the lazy-write cache in order to prevent delays. This can result in a slow-down in data access if done in areas where it is not required. To flush the lazy-write cache, wrap the code in a transaction, and commit using the dbForceOSFlush flag. For example:
DBEngine(0).BeginTrans
' Update operation here.
DBEngine(0).CommitTrans dbForceOSFlush
				
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. The example provided below uses a Visual Basic 5.0 out of process ActiveX server to provide the second instance of Jet while at the same time being able to be precisely synchronized.

Create the Out of Process Server

  1. Create a new ActiveX EXE project in Visual Basic 5.0.
  2. Add a Project Reference for Microsoft DAO 3.6 Object Library.
  3. Add the following code to the class module:
    Option Explicit
    
    Dim db As Database, rs As Recordset
    
    Private Sub Class_Initialize()
      Set db = DBEngine(0).OpenDatabase("nwind.mdb")
      Set rs = db.OpenRecordset("Customers")
    End Sub
    
    Private Sub Class_Terminate()
      rs.Close
      db.Close
    End Sub
    
    Public Sub UpdateTitle(ByVal NewTitle As String)
      DBEngine(0).BeginTrans
      rs.Edit
      rs!ContactTitle = NewTitle
      rs.Update
      DBEngine(0).CommitTrans dbForceOSFlush
    End Sub
    					
  4. Save the Project files.
  5. Run the project. While the project runs, start a second instance of Visual Basic.

Create the Test Application

  1. Create a new Standard EXE project in the second instance of Visual Basic and add a form with a command button (Command1).
  2. Change the caption of the button to Operator.
  3. Change the project name to Project2.
  4. Add a Project Reference for Project1 and the Microsoft DAO 3.6 Object Library.
  5. Add the following code:
    Option Explicit
    
    Dim ws As Workspace, db As Database, obj As Project1.Class1
    
    Private Sub Command1_Click()
       Dim rs As Recordset, Temp As String
       Dim fDone As Boolean, Counter As Long
       If Me!Command1.Caption = "Operator" Then
          Temp = "Programmer"
       Else
          Temp = "Operator"
       End If
       obj.UpdateTitle Temp
       fDone = False
       Counter = 0
       Do
          Counter = Counter + 1
          ' DBEngine.Idle dbRefreshCache
          Set rs = db.OpenRecordset("Customers")
          If rs!ContactTitle = Temp Then fDone = True
             rs.Close
       Loop Until fDone
       Me!Command1.Caption = Temp
       MsgBox "Took " & Counter & " retries to get the updated data."
    End Sub
    
    Private Sub Form_Load()
       Set ws = DBEngine.CreateWorkspace("", "Admin", "")
       ' Set ws = DBEngine.CreateWorkspace("JetTest", "Admin", "")
       ' DBEngine.Workspaces.Append ws
       Set db = ws.OpenDatabase("nwind")
       Set obj = New Project1.Class1
    End Sub
    
    Private Sub Form_Unload(Cancel As Integer)
       Set obj = Nothing
       db.Close
       ws.Close
    End Sub
    					
  6. Run the project and click the button on the form. It will take many retries to see the new data.
  7. End execution of the project, and then uncomment the "DBEngine.Idle dbRefreshCache" line in the Command1_Click procedure and try again. It will still take many tries to see the new data.
  8. End execution of the project, and then comment the "Set ws =" line in the Form_Load procedure and uncomment the two lines following it. This time, the new data will be read on the first try.
NOTE: You have to give the Workspace object a name in order to be able to append it to the Workspaces collection.

↑ Back to the top


Keywords: KB202097, kbprogramming, kbprb

↑ Back to the top

Article Info
Article ID : 202097
Revision : 5
Created on : 10/11/2006
Published on : 10/11/2006
Exists online : False
Views : 431