Using Recordset Objects

You use Recordset objects to manipulate the data in a database at the record level. There are five types of Recordset objects — table, dynaset, snapshot, forward-only, and dynamic. Table-type Recordset objects are available only in Microsoft Jet workspaces. Dynamic-type Recordset objects are available only in ODBCDirect workspaces. You specify the type of recordset you want to open by using the type argument of the OpenRecordset method.

• A table-type Recordset object refers to either a local table in the current database, or to a linked table in an external database created with Microsoft Access or Microsoft Jet. When you create a table-type Recordset object, the database engine opens the actual table, and your subsequent data manipulations operate directly on table data. A table-type Recordset object can be opened against only one table; it cannot be opened against a join or union query.
  
  
• One of the biggest advantages of the table-type Recordset object is that it can be indexed by using an index created for the underlying table. This allows much faster sorting and filtering than is possible with the other Recordset object types. To locate specific records, use the Seek method, which is faster than the Find methods.
  
  
• A dynaset-type Recordset object refers to either local or linked tables, or to the result of a query. It’s actually a set of references to records in one or more tables. With a dynaset, you can extract and update data from more than one table, including linked tables from other databases. Heterogeneous joins are a unique feature of dynasets — they enable you to use updatable join queries against tables in different types of databases.
  
  
• One of the main benefits of the dynaset-type Recordset object is that a dynaset and its underlying tables update each other. Changes made to records in the dynaset are written to the underlying table, and changes made by other users to data in the underlying tables while the dynaset is open are reflected in the dynaset. The dynaset is the most flexible and powerful type of Recordset object, although running queries and manipulating data may take longer than they would with a table-type Recordset object.
  
  
• A snapshot-type Recordset object contains a static copy of the data as it exists at the time the snapshot is created. In a Microsoft Jet workspace, a snapshot can’t be updated. In an ODBCDirect workspace, a snapshot may be updatable, depending on the Open Database Connectivity (ODBC) driver. For more information about ODBCDirect, see Chapter 8, “Accessing External Data,” and Chapter 9, “Developing Client/Server Applications.”

  The main advantage of using a snapshot is that it creates less processing overhead than the other types, so it may execute queries and return data faster, especially when you are working with ODBC data sources. Note that for .mdb files, Long Binary (OLE Object) and Memo fields are represented in a snapshot by pointers, rather than the actual data.

• A forward-only-type Recordset object is identical to a snapshot-type Recordset object but you can only scroll forward through its records. A forward-only-type Recordset object can’t be updated. Using this type of Recordset object improves performance when you need to make only a single pass through the records.

  Figure 5.1 illustrates the difference between how data is stored in a snapshot-type or forward-only-type Recordset object and how it’s stored in a dynaset. The snapshot stores a copy of the entire record (except for Memo and Long Binary fields). The dynaset stores just the primary key for each record, copying the full record only when it’s needed for editing or display purposes.

  Figure 5.1 The difference between records stored in dynaset- and snapshot-type or forward-only-type Recordset objects.

  

• A dynamic-type Recordset object is available only in ODBCDirect workspaces. This Recordset object represents a query result set in which you can add, change, or delete records. In addition, changes made to data in the underlying tables by other users are reflected in the Recordset object.

The type of Recordset object you use depends on what you want to do and whether you want to change or simply view the data. For example, if you must sort the data or work with indexes, use a table-type Recordset object. Because they are indexed, table-type Recordset objects also provide the fastest way to locate data. For example, you can use the Seek method to quickly locate records in an indexed table-type Recordset object only. To search for records in other Recordset object types, you must use the FindFirst, FindLast, FindNext, or FindPrevious method. If you want to be able to update a set of records selected by a query, use a dynaset. If the table-type is unavailable in a specific situation and you only need to scan through a set of records, using a snapshot-type or a forward-only-type Recordset object may improve performance.

Using a table-type Recordset object, if that type is available, almost always results in the best performance.

Note In this chapter, the terms table, dynaset, snapshot, forward-only, and dynamic are often used for the sake of simplicity. Keep in mind, however, that these are all types of Recordset objects. For example, the term dynaset refers to a dynaset-type Recordset object.