How a Dynaset is Supposed to Operate...

Dynasets are essentially keysets. When you create a dynaset using something like :

  SELECT * FROM sometable WHERE somecondition

JET would actually perform a query like:

  SELECT keycolumns FROM sometable WHERE somecondition

and store the resulting table of keys into the work-station’s memory. As you navigated the dynaset (using the Movexxx methods) JET would issue another SELECT statement to retrieve the current non-key data for the new current row from the data source. This strategy results in an always-fresh view of the data, or at least the data for each row is fresh as you move onto that row.

In VB3, a dynaset was never buffered by the DAOs (Data Access Objects). Some third-party bound controls (like grids or list boxes) would buffer a few rows into the work-station’s buffers but your code could always rely on seeing the latest data as you moved around a dynaset ...this is no longer the case in VB4.

In VB4 (both 16 and 32 bit versions), there appears to be some buffering going on behind the scenes. If two separate programs create dynasets on the same underlying data source (whether on the same or different workstations), they will not immediately see changes made by the other program.

Try this. Create an app with a data control aimed at the Authors table, and link a few text boxes so you can see the contents. Compile to an exe and run two instances of it (or as I did, run the exe and another instance in the environment). Now in one side change a record and move off it to 'Post' the change.

In the other window, press next and previous several times. Eventually, you will see the changed data. On my setup (Win95, VB4/32), it will reliably show the change in the second window the sixth time you go back to it this way. Further experimentation shows that this applies as long as you do not move more that five rows away (ie. doing 5 Nexts followed by First will also display the new value the sixth time you do it).

Doing six Nexts followed by a First will show you the new value. Then I tried Last-First and found it was not predictable – cycling too fast lets you do maybe twenty cycles before you see the change; doing it slower will show the change on the sixth cycle. I never got it better than that.

Now obviously JET is doing some buffering somewhere and I suspect that this is in connection with freeing the locks. The reason I suspect this is the timing differences just noted. So I tried this. In the Data control’s Reposition event, I placed this single line of code;

  DBEngine.Idle dbFreeLocks

and repeated the experiment. Surprise, surprise! It then worked just like VB3 – changes made in one window will immediately be reflected in the other (well, when it next comes back to that row, anyway).

Never being content to leave 'well enough' alone, I took that statement out of the interpreted instance and re-ran the experiment. In this case, when changes are made in the program that does have this line, the other one does not see the changes immediately. If the changes are made in the program instance that does not have this code, it appears in the other one (ie. the one with the code) immediately.

This actually surprised me; I would have predicted the opposite behaviour although this behaviour is better.

All you have to do is place this line before any repositioning methods, if you are concerned with getting the absolute latest data. Even if the other program (the one that changed the data) does not do this, at least your application will work correctly.

OK, so what's going on? Well, we’re not too sure. Microsoft has been made aware of this problem and have confirmed this behaviour. They have not (yet) blamed the team at ByDesign for this one, so we can assume it is a bug. I suspect that the DAO team snuck in an optimisation (ie. caching some rows on dynasets) and forgot to tell everyone else. The magic number of 5 that appears on the tests is interesting in that it is also the documented minimum size of the ODBC dynaset cache used by JET. The Help file states quite clearly that the CacheSize property of a dynaset is only applicable to ODBC dynasets. However, VB4 does not complain if you set the cache-related parameters for non-ODBC dynasets. For example, if you do this :

  Set mDB = Workspaces(0).OpenDatabase(gsDatabasePath) 
  Set mDS = mDB.OpenRecordset ("Authors", dbOpenDynaset)
  mDS.CacheSize = 10 
  mDS.FillCache 
  Set Data1.Recordset = DS

you get no error at compile or run time in VB4, with gsDatabasePath pointing to BIBLIO.MDB, even though this is supposed to be illegal. The next step was obvious: the online help states “A setting of 0 turns off caching”. Unfortunately, setting the value of CacheSize to zero in the code above does not change the behaviour for non-ODBC dynasets anyway.

Stay tuned as we continue our investigation of this issue. For now, remember to add the call to the engine’s Idle method to every re-positioning code for dynasets where freshness of data is a concern.

---