Using Table Objects Versus Dynaset/Snapshot Objects in VB

- Professional Edition of Microsoft Visual Basic for Windows, version 3.0

SUMMARY

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.

MORE INFORMATION

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 Environments

Table 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.

Dynaset Objects:

• 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 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.

1. Start a new project in Visual Basic. Form1 is created by default.

2. Double-click the form to open the code window. Add the following code to the Form Load event:

      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
   
      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.
   
   

3. Start the program (or press the F5 key). Close the form to end the program.

REFERENCES

• Visual Basic online Help for the Table, Dynaset, and Snapshot objects, and their methods and properties.
• Microsoft Visual Basic version 3.0 "Professional Features Book 2," Data Access Guide section, Chapter 3.
• The VISDATA.MAK file installed in the VB3\SAMPLES\VISDATA directory loads extensive examples of data access. The VISDATA sample program uses every data access function in Visual Basic. You can refer to the VISDATA source code for examples of how to use each data access function.