[AccessD] For the price of a recordset

I use caches to hold infrequently changing data.  I have always had an issue with being able to know 
when data changed back in the recordset behind the cache.  For example I use users / groups / 
usergroups in my PLSS.  If the administrator makes a change, adds a new user, group, adds a user to 
a group etc, how do you notify the application that has that info cached?

Today a light bulb went off.

We all know that in a bound form the selector displays a pencil pretty much instantaneously after 
someone begins an edit.  Well...

Suppose I open a recordset using the following code:

Dim rst As DAO.Recordset
Function TestfieldVal()
     If rst Is Nothing Then
         Dim db As DAO.Database
         Dim strSQL As String

         Set db = CurrentDb
         strSQL = "SELECT PLSSV_Val from usystblPLSSysVars WHERE PLSSV_Name = 'Test'"
         Set rst = db.OpenRecordset(strSQL)
         Set db = Nothing
     End If
End Function
Public Function cRst() As DAO.Recordset
     TestfieldVal
     Set cRst = rst
End Function

Now in the debug window I can do:

?cRst()!PLSSV_Val and see an edit made by another user.

IOW you can edit a specific value in a specific table on one machine and I can see that edit on my 
machine, through the open recordset.

Of course we all know this, but what this means to cache management is that I can use a central 
table to hold sysvars (I do this already).  I can have a sysvar named PLSSDataChanged and set the 
value of that to some value, perhaps #12/19/2010#.

In my cache system framework I can open a recordset and grab the value for that specific record, and 
hold the table open.  So as my application loads PLSS it gets a pointer to that value in that record.

My cache then loads the cache and sets a dtePLSLastRefreshed variable in the cache supervisor to 
Now() as the cache loads.

Now, my PLSS administrator updates some data, perhaps deleting a usergroup so that a specific user 
no longer belongs to a specific group.  My code that does that delete then updates this specific 
SysVar with a new datetime Now() and saves the value back into the sysvar record.

Every user in my application is more or less instantly notified of the change.  The next time that 
the PLSS needs to open a form it first checks this open record value against dtePLSLastRefreshed and 
if the value in this field is later than the value in the class I know I need to refresh the cache. 
  I do so and then set dtePLSLastRefreshed to Now() resetting the clock so to speak.

Instant notification of cache data changes.  The cost is holding a recordset open, but I already do 
that on an arbitrary table in every BE in order to work around the "get a lock" slowness issue.

This may be huge for my cache coherency issues.

Too bad I am running out of light bulbs in my old age.

-- 
John W. Colby
www.ColbyConsulting.com