This section discusses the types of Recordset objects available through Microsoft Jet and how each type affects the performance and operation of your application. For information on ODBCDirect Recordset objects, see “Using Cursors in ODBCDirect Workspaces” later in this chapter.
There are four types of Recordset objects: table-type, which are a representation of actual database tables; dynaset-type, which can be updated; snapshot-type, which cannot be updated; and forward-only-type, which can’t be updated and can only be scrolled forward. Since you cannot create a table-type Recordset object based on a remote data source through ODBC, the remainder of the discussion focuses on dynaset-type, snapshot-type, and forward-only-type Recordset objects.
If you do not explicitly specify what type of Recordset objects to use in your code, by default Microsoft Jet uses the one with the most functionality. For remote databases, this is a dynaset-type Recordset object. If you don’t need to update your data, you will get better performance by explicitly creating a snapshot-type Recordset object.
The decision to use a dynaset-type versus a snapshot-type or forward-only-type Recordset object is based on resource, speed, and performance considerations in addition to the basic needs of the application (such as the ability to update remote data). For example, if your application doesn’t need update capabilities and the Recordset object you want to use contains fewer than 500 records, a snapshot-type Recordset object improves the overall data access speed of your application. However, if the Recordset object you’re using is large or contains Memo or Long Binary (OLE Object) fields, using a dynaset may be more efficient. Also, if you only need to move forward through the Recordset object, you can achieve even better performance by using a forward-only-type Recordset object.