PRB: DBEngine.Idle dbRefreshCache Doesn't Immediately Refresh

• Microsoft Access versions 7.0, 97
• 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 3.5, 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.5 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 to Project1 and to the Microsoft DAO 3.5 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. 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. 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.

Additional query words: kbDSupport kbdse

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