Article ID: 109218 - View products that this article applies to.
This article was previously published under Q109218
This article discusses the advantages and disadvantages of using Table objects versus Dynaset and Snapshot objects for finding and updating data in a database table. This applies to the Microsoft Access database engine used in Visual Basic version 3.0.
The three types of recordsets are Tables, Dynasets, and Snapshots. All recordsets have records (rows) and fields (columns). The Professional Edition of Visual Basic lets you create object variables of type Dynaset, Snapshot, and Table. The Standard Edition supports Dynaset object variables but not Snapshot or Table object variables.
A table is a fundamental part of a Database and contains data about a particular subject. A Table object is a logical representation of a physical table.
To make a Snapshot or Dynaset, use the CreateSnapshot or CreateDynaset method on a Database or any recordset. A Snapshot is a static, read-only picture of a set of records that you can use to find data or generate reports. The records in a Snapshot cannot be updated (or modified), whereas records in a Dynaset can be updated.
The move methods (MoveFirst, MoveLast, MoveNext, and MoveLast) apply to all three types of recordsets (Dynasets, Snapshots, and Tables).
The find methods (FindFirst, FindLast, FindNext, and FindPrevious) apply to Dynaset objects and Snapshot objects, but not to Table objects. The Seek method applies only to Table objects.
For intensive searches, you may want to use both Table and Dynaset objects on the same base table. You can use the Seek method on the Table objects and the find methods on any open Dynasets.
Visual Basic data controls always use Dynasets. Data controls don't use Snapshot objects or Table objects.
Dynaset objects are a set of record pointers to those records which existed in the base table in the Database at the time the Dynaset was created. Your Dynaset also adds pointers to any new records which you add to the Dynaset, and deletes pointers of deleted records.
If you add a record to a base table, the record does not immediately appear in any currently existing Dynaset based on that table. You would need to re-create the Dynaset to see a new record that was added to the base table after the Dynaset was created. However, if you add a new record to a Dynaset, the record appears immediately in both the Dynaset and the base table. Deleting a record is reflected in a similar way.
Dynasets Versus Tables in Multiuser and Single-User EnvironmentsTable objects connect directly to base tables that are globally accessible to all users on a multiuser system. All users using Table object variables can see all records in the base table at all times. In contrast, Dynasets are local to each program. Your local additions and deletions are reflected in the Dynaset. Dynasets don't reflect records that other users added or deleted after the local Dynaset was created.
In a multiuser environment (computer network), Dynasets may not be suitable for updating shared tables. Data controls, because they use Dynasets, are unsuitable for such applications as a multiuser order entry system. NOTE: Two programs simultaneously using the same table on a single computer act as a multiuser environment.
If another user on a multiuser system updates a record for which you have a pointer in your Dynaset, you will see the changes whenever you request that record. If another user adds a record to the table, you cannot see that record because the current Dynaset doesn't contain a pointer to that record. If another user of the base table deletes a record that is in your Dynaset, your Dynaset keeps a pointer to that non-existent record. Your subsequent attempts to access that non-existent record will give an error.
Data controls are suitable for most types of data browsing (read-only access) and many types of simple data entry.
In a single-user environment, Table Objects and Dynaset Objects both update the base table in a similar fashion when records are added or deleted. Data controls are thus quite suitable for updating databases in single-user environments.
Example Showing Speed of Seek in a Table Versus SQL SELECT in a DynasetThe fastest way to find a specific record in a recordset is usually a Seek method on a Table object. The equivalent SQL SELECT statement on a Dynaset object is usually very close in performance, as long as the SELECT finds just one record. A SQL SELECT that finds more than one record may be slower.
Article ID: 109218 - Last Review: October 20, 2003 - Revision: 3.0