Microsoft Jet provides two kinds of recordsets: dynasets, which can be updated, and snapshots, which cannot. If you don't need to update data and the recordset contains fewer than 500 records, you can reduce the time it takes the recordset to open by requesting a snapshot using DB_OPEN_SNAPSHOT. This causes Jet to use a snapshot, which is generally faster than using a dynaset.
However, if the recordset you're using is large or contains Memo or OLE Object fields, using a dynaset is more efficient. If you use a dynaset, Jet retrieves only the primary key of each record; if you use a snapshot, it retrieves the entire record. A dynaset is also more efficient because Memo and OLE Object fields are retrieved only if they are referenced.
Rather than transferring the data in each column of the row during the fetch, Jet builds a dynaset by fetching the key values that uniquely identify each of the rows. The data from the bookmarked rows is fetched only when needed to populate the table or query datasheet view. Using key values speeds the chunking operation and minimizes network traffic, especially when browsing large tables. Data retrieval is also optimized in that Memo and OLE objects are not retrieved unless they need to be displayed.
A snapshot Recordset does not use bookmarks; instead, the data contained in each of the rows is fetched. Creating snapshot Recordset objects can be faster with small tables, but can be quite time-consuming when large tables with many columns are involved, and network traffic increases drastically. This is especially true if the table or query includes Memo fields and OLE objects. (Note, however, that .MDB snapshots containing Memo or OLE fields will not retrieve the Memo or OLE data into the snapshot until it is actually referenced.) You can further minimize the performance hit with snapshot Recordsets by ensuring you are returning only the columns you need.