Microsoft Access: Snapshot and Dynaset recordset queries - what the heck are they?

Using Microsoft Access is a bit like being in an Indiana Jones movie. There are dark and mysterious ancient temples to explore, hidden treasures, snakes, spiders and the occasional death dealing trap. The scrolls that would explain everything are all losts in the mists of time, before there was a web.

It keeps me from boredom.

Yesterday a complex and powerful set of nested subqueries began retuning unpredictable results. Yes, I'd again broken Microsoft Access. It seems that Access was getting variable results depending on the sequence of subquery execution, such that it couldn't decide if a value were NULL or not.

So I tried to figure out if setting the sub-query(s) (recordset) to behave as a "Dynaset", "Dynaset (lazy update)" or "Snapshot"  would make any difference.

First, of course, I looked for documentation on what the differences are. It's not in the Access Help files. Google found precious little at first -- one of my page one results was a post I wrote in January 2006!)

This search worked better: recordset, dynaset, snapshot.

From it I got a few references:

O'Reilly Network -- Access Design and Programming Tips

But it is important to remember that DAO (Data Access Object) provides several forms of recordsets: table-type, dynaset, snapshot, and forward-only. The point here is that if a snapshot or forward-only recordset will suit your purposes, you should use it, because it will be more efficient and therefore your code will run more quickly.

Snapshot recordsets are faster to create and to access than dynaset recordsets, but they use more memory because the entire record is stored in memory. The downside is that you cannot update the records in a snapshot recordset.

Forward-only recordsets are like snapshot recordsets, but you can only move forward through the recordset.

Ultimately, the only way to tell whether you will save significant processing time is to try the various suitable recordset types in a given situation. The point here is to not fall into the habit of always creating a table-type or dynaset-type recordset.

and

DAO Object Model The Definitive Reference Chapter 8 Recordsets Collection and Recordset Object

...There are five types of recordsets: Table-type, Dynaset, Snapshot, Forward-only, and Dynamic. (See the Type property section for a discussion of recordset types.) Each Recordset object contains a collection of Fields that represents the fields in the underlying table(s). You can list the field names and values, but you will just get the values in the current record, unless you first go to a particular record. For example, the following code moves to the last record in a recordset and lists the field names and values for that record:...

Recordset Property Summary which shows almost NO difference between Snapshot and Dynaset.

But then we come to the precious table 8-13:

Dynaset: Represents a table or an updatable query. Supports the AddNew, Delete, and Find* methods, but not the Seek method.

Snapshot: A read-only recordset; useful for finding data or printing. Does not allow updating, except in the case of an updatable Snapshot in an ODBCDirect workspace. Supports the Find* methods.

Elsewhere I read

Dynaset:
1. Can be formed by large number of tables resulting in Virtual Tables.
2. Does not store on the Machine but occupy temporary memory that is RAM.
3. The changes made in the database can be reflected in the this.
4. Allows operations like edit and update.

SnapShot:
1. Creates table on the machine and occupies space of the memory.
2. Does not allow edit and update operations.
3. Can be used for small amount of data.

Another source (lost) said a Snapshot is often quite a bit faster in a query than a Dynaset, but the speed requires more memory use.

By extension of the above, I imagine "Dynaset (lazy update)" is an extension of Dynaset with some of the speed of a Snapshot but the possibility of doing updates (delayed or lazily).

So, after all that, did changing some of my subqueries to "Snapshot" fix the problem of the "Schrodinger's Cat" results that were sometimes NULL and sometimes NOT NULL?

Yes, it did. The results were much faster too.