Microsoft Jet maintains an internal cache of
records for each Microsoft Jet session. Caching records provides a significant
performance improvement, but it means that other sessions may not immediately
see changes.
In DAO a session is associated with a DBEngine object. As each application can only have one DBEngine object, it means that each application will have its
own session. A given application using DAO will always see its own changes, but
other applications may not see the changes immediately. In ADO a session is
associated with a Connection object. A single
application using ADO may have multiple Connection
objects. So within a single application, changes may not been seen immediately.
There may be instances where performance is less
important than guaranteeing that a Recordset contains
the latest data. In those instances, it makes sense to force a refresh of
Microsoft Jet's internal cache. Both DAO and JRO provide a mechanism for this.
In DAO, use the DBEngine object's Idle
method with dbRefreshCache to force Microsoft Jet to
refresh its cache. With JRO, use the JetEngine object's
RefreshCache method passing in the ADO connection as a
parameter.
DAO
Sub DAORefreshCache()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Refresh the cache to ensure that the latest data
' is available.
DBEngine.Idle dbRefreshCache
Set rst = db.OpenRecordset("SELECT * FROM Shippers")
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value;
Next
Debug.Print
rst.MoveNext
Loop
rst.Close
End Sub
ADO
Sub JRORefreshCache()
Dim cnn As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Dim je As New JRO.JetEngine
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Refresh the cache to ensure that the latest data
' is available.
je.RefreshCache cnn
' Open a recordset and read the data
Set rst = cnn.Execute("SELECT * FROM Shippers")
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value;
Next
Debug.Print
rst.MoveNext
Loop
rst.Close
End Sub
This example above is somewhat contrived because
the cache will most likely already contain the latest data as the Database and Connection are being
opened for the first time immediately before attempting to open the Recordset. The ability to refresh the cache is generally more
useful when a Database or Connection is
opened when the application is first launched and then at some later point a Recordset with the latest data needs to be opened.
EmoticonEmoticon