This article discusses the advantages and disadvantages of using Tableobjects versus Dynaset and Snapshot objects for finding and updating datain a database table. This applies to the Microsoft Access database engineused in Visual Basic version 3.0.
The three types of recordsets are Tables, Dynasets, and Snapshots. Allrecordsets have records (rows) and fields (columns). The ProfessionalEdition of Visual Basic lets you create object variables of type Dynaset,Snapshot, and Table. The Standard Edition supports Dynaset object variablesbut not Snapshot or Table object variables.
A table is a fundamental part of a Database and contains data about aparticular subject. A Table object is a logical representation of aphysical table.
To make a Snapshot or Dynaset, use the CreateSnapshot or CreateDynasetmethod on a Database or any recordset. A Snapshot is a static, read-onlypicture of a set of records that you can use to find data or generatereports. The records in a Snapshot cannot be updated (or modified), whereasrecords in a Dynaset can be updated.
The move methods (MoveFirst, MoveLast, MoveNext, and MoveLast) apply to allthree types of recordsets (Dynasets, Snapshots, and Tables).
The find methods (FindFirst, FindLast, FindNext, and FindPrevious) applyto Dynaset objects and Snapshot objects, but not to Table objects. TheSeek method applies only to Table objects.
For intensive searches, you may want to use both Table and Dynaset objectson the same base table. You can use the Seek method on the Table objectsand the find methods on any open Dynasets.
Visual Basic data controls always use Dynasets. Data controls don't useSnapshot objects or Table objects.
Dynaset objects are a set of record pointers to those records which existedin the base table in the Database at the time the Dynaset was created. YourDynaset 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 appearin any currently existing Dynaset based on that table. You would need tore-create the Dynaset to see a new record that was added to the base tableafter the Dynaset was created. However, if you add a new record to aDynaset, the record appears immediately in both the Dynaset and the basetable. Deleting a record is reflected in a similar way.
Dynasets Versus Tables in Multiuser and Single-User Environments
Table objects connect directly to base tables that are globally accessibleto all users on a multiuser system. All users using Table object variablescan see all records in the base table at all times. In contrast, Dynasetsare local to each program. Your local additions and deletions are reflectedin the Dynaset. Dynasets don't reflect records that other users added ordeleted after the local Dynaset was created.
In a multiuser environment (computer network), Dynasets may not besuitable for updating shared tables. Data controls, because they useDynasets, are unsuitable for such applications as a multiuser order entrysystem. NOTE: Two programs simultaneously using the same table on a singlecomputer act as a multiuser environment.
If another user on a multiuser system updates a record for which you havea pointer in your Dynaset, you will see the changes whenever you requestthat record. If another user adds a record to the table, you cannot seethat record because the current Dynaset doesn't contain a pointer to thatrecord. If another user of the base table deletes a record that is in yourDynaset, your Dynaset keeps a pointer to that non-existent record. Yoursubsequent attempts to access that non-existent record will give an error.
Data controls are suitable for most types of data browsing (read-onlyaccess) and many types of simple data entry.
In a single-user environment, Table Objects and Dynaset Objects both updatethe base table in a similar fashion when records are added or deleted. Datacontrols are thus quite suitable for updating databases in single-userenvironments.
- Dynasets are set-oriented. You can create any arbitrary set of records from a single Table, or set of records joined from multiple Tables using an SQL SELECT statement. If you need to join tables or use subsets, a Dynaset is required. The only way to join more than one table is with a Dynaset object.
- When Visual Basic creates a Dynaset, the Dynaset's records are ordered using indexes for greater speed. After the Dynaset is created, find and move methods within a Dynaset are non-indexed, sequential, and relatively slow. Using the Dynaset will be faster if you limit its size to a small subset of the records in the base table. Recreating the Dynaset with a different subset of records is faster than creating a huge Dynaset and navigating it using find and move methods.
- You can sort a Dynaset on any arbitrary field, including expressions, such as mid([myfield],2,3), whether the field is indexed or not.
- Using a Dynaset, you can attach external database tables to a Microsoft Access format database, which is the format native to Visual Basic. An attached table is a table from an external database linked at run time to a Microsoft Access format database. You cannot create a Table object on an attached table.
- Table objects are record-oriented rather than set-oriented. The methods for Table objects let you only retrieve one row at a time, and only from one Table at a time. Table objects don't support SQL queries or subsets, unless you create a Dynaset or Snapshot from the Table.
- The Seek method finds a given record very quickly because it uses the Table's indexes. The Seek method is significantly faster than the find methods. For speed and flexibility, you can change the Index property of the Table object to change the order of the Seek. The Seek method can find values that are in indexed fields, but not in non-indexed fields.
- You can only order the data in Table objects based on existing indexes.
Example Showing Speed of Seek in a Table Versus SQL SELECT in a Dynaset
The fastest way to find a specific record in a recordset is usually a Seekmethod on a Table object. The equivalent SQL SELECT statement on a Dynasetobject is usually very close in performance, as long as the SELECT findsjust one record. A SQL SELECT that finds more than one record may beslower.
- Start a new project in Visual Basic. Form1 is created by default.
- Double-click the form to open the code window. Add the following code to the Form Load event:
NOTE: There is no customer name of WOLVH in NWIND.MDB for Access 2.0. Replace WOLVH with WOLZA if you are using Access 2.0.
Sub Form_Load () form1.Show ' In form Load event, must show form before Print works. Dim t As Table Dim ds As Dynaset Dim db As database Set db = OpenDatabase("C:\ACCESS\NWIND.MDB") Set t = db.OpenTable("Customers") t.Index = "PrimaryKey" ' The following Seek is about as fast as the SQL SELECT below: Print Time$ t.Seek "=", "WOLVH" Print Time$ Print t("Customer ID") 'Print Customer ID value of current record Print Time$ ' Enter the following two lines as one, single line: Set ds = db.CreateDynaset( "SELECT * FROM Customers WHERE [Customer ID] = 'WOLVH' ") Print Time$ End Sub
- Start the program (or press the F5 key). Close the form to end the program.