Dynaset-type Recordset objects differ from snapshot-type and forward-only-type Recordset objects in the way they retrieve and cache data. They also differ in performance and data accessibility. Frequently, the needs of your application will dictate which Recordset type you can use. Ask yourself the following questions about your application and use the answers that follow as a guide to help you choose the right Recordset type for your application and to help you understand the tradeoffs between the two types.
If you need to update the remote data, you must use a dynaset-type Recordset object. If you don’t need to update data or see changes made by other users, use a snapshot-type or forward-only-type Recordset object. Snapshot-type and forward-only-type Recordset objects are faster to open and scroll through than dynaset-type Recordset objects.
If your result set contains a large number of records, a dynaset-type Recordset object is faster and more efficient than a snapshot-type or forward-only-type Recordset object. The performance difference in large Recordset objects occurs because your application must retrieve all records in a snapshot-type or forward-only-type Recordset object when you move from the first record to the last. In a dynaset-type Recordset object, your application only needs to retrieve the bookmark fields initially. As each record, or group of records, in the dynaset-type Recordset object is visited, the application retrieves the data from the server that corresponds to the bookmarks.
If your result set contains a small number of records, a snapshot-type or forward-only-type Recordset object is faster and more efficient than a dynaset-type Recordset object. The performance difference in small Recordset objects occurs because your application can retrieve and hold a few hundred complete records more easily than it can retrieve and hold a few hundred bookmarks and subsequently retrieve the data from the server that corresponds to the bookmarks at the application’s request. This is especially true of scrolling speed: Snapshot-type or forward-only-type Recordset objects are faster because all the data is cached locally.
If your application doesn’t need all fields in the remote table, you can improve opening and scrolling performance in all types of Recordset objects by limiting the fields retrieved from the server.
See Also For more information about limiting the fields retrieved from the server, see “Limiting Returned Data” earlier in this chapter.
For dynaset-type Recordset objects, if your application retrieves records that include Long Binary (OLE Object) or Memo fields and you don’t need to view or update these objects, you can improve performance by ensuring that they aren’t retrieved. Microsoft Jet only retrieves Long Binary (OLE Object) and Memo records if they are visible through the user interface of your application (a Microsoft Access form or report, for example) when the rest of the record is retrieved. You can remove the objects from the form and add a button to expose the Long Binary (OLE Object) or Memo records as needed. This will let your users retrieve the Long Binary (OLE Object) or Memo records at will, without the burden of displaying this information when it isn’t needed.
It’s also important to understand that Memo fields are cached in a snapshot-type or forward-only-type Recordset object. Long Binary (OLE Object) fields are not.