ACC2000: DBEngine.Idle dbRefreshCache Refresh Is Not Immediate

• Microsoft Access 2000
• Microsoft Visual Basic Professional and Enterprise Editions for Windows, version 5.0

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.

CAUSE

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

RESOLUTION

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

STATUS

This behavior is by design.

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 

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

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.

REFERENCES

© Microsoft Corporation 1998, All Rights Reserved.
Contributions by Malcolm Stewart, Microsoft Corporation

Additional query words: kbDSupport kbdse prb

Keywords : kbdta AccCon KbVBA
Version : WINDOWS:2000,5.0
Platform : WINDOWS
Issue type : kbprb