Differences Between the Object Variables in VB Version 3.0 (103442)


The information in this article applies to:

  • Microsoft Visual Basic Standard Edition for Windows 3.0
This article was previously published under Q103442

SUMMARY

This article contains two references:

  • A revised version of the table outlined on the back of the "Professional Features Book 2" manual for Microsoft Visual Basic version 3.0 Programming System for Windows. This table outlines the differences in the properties and methods of the three main data access objects (table, dynaset, and snapshot) in Visual Basic version 3.0.
  • A brief list of the differences between table, database, dynaset, querydef, and snapshot objects.

MORE INFORMATION

Revised Table for the Back of "Professional Features Book 2"

The following table lists most of the properties and methods that apply to each of the database objects.

  • Yes means the object does contain the property or method in both the Standard and Professional Editions of Visual Basic version 3.0 for Windows.
  • No means the object does not contain the property or method in either the Standard or Professional Edition of Visual Basic version 3.0 for Windows.
  • Yes/PRO means the object contains the property or method only in the Professional Edition, not the Standard Edition, of Visual Basic version 3.0 for Windows.
  • (docerr) highlights that information as a correction to the information given in the original table shown on the back of the "Professional Features Book 2."
Properties      Table         Dynaset           Snapshot
--------------  ------------  ----------------  -----------------
BOF             Yes/PRO       Yes               Yes/PRO
BookMark        Yes/PRO       Yes               Yes/PRO
BookMarkable    Yes/PRO       Yes               Yes/PRO
DateCreated     Yes/PRO       No                No
EOF             Yes/PRO       Yes               Yes/PRO
Filter          No            Yes/PRO           Yes/PRO
Index           Yes/PRO       No                No
LastModified    Yes/PRO       Yes               No
LastUpdated     Yes/PRO       No                No
LockEdits       Yes/PRO       Yes               No
Name            Yes/PRO       Yes               Yes/PRO
NoMatch         Yes/PRO       Yes               Yes/PRO
RecordCount     Yes/PRO       Yes               Yes/PRO
Sort            No            Yes/PRO           Yes/PRO
Transactions    Yes/PRO       Yes               No
Updatable       Yes/PRO       Yes               No


Methods         Table         Dynaset           Snapshot
--------------  ------------  ----------------  -----------------
AddNew          Yes/PRO       Yes               No
Clone           Yes/PRO       Yes/PRO           Yes/PRO
Close           Yes/PRO       Yes               Yes/PRO
CreateDynaset   Yes/PRO       Yes/PRO(docerr)   No
CreateSnapshot  Yes/PRO       No                Yes/PRO(docerr)
Delete          Yes/PRO       Yes               No
Edit            Yes/PRO       Yes               No
FindFirst       No(docerr )   Yes               Yes/PRO
FindLast        No(docerr )   Yes               Yes/PRO
FindNext        No(docerr )   Yes               Yes/PRO
FindPrevious    No(docerr )   Yes               Yes/PRO
ListFields      Yes/PRO       Yes/PRO           Yes/PRO
ListIndexes     Yes/PRO       Yes/PRO           Yes/PRO
MoveFirst       Yes/PRO       Yes               Yes/PRO
MoveLast        Yes/PRO       Yes               Yes/PRO
MoveNext        Yes/PRO       Yes               Yes/PRO
MovePrevious    Yes/PRO       Yes               Yes/PRO
Seek            Yes/PRO       No                No
Update          Yes/PRO       Yes               No

List of Differences Between Data Access Objects

Below, object by object, is a list of differences, recommendations, and suggestions for each of the various data access objects. The page numbers refer to pages in the "Professional Features Book 2." Article Q numbers refer to other Microsoft Knowledge Base articles which give provide additional information.

Snapshot Objects:

  • Snapshots return all of the selected data and Dynasets return only a set of keys that indirectly reference the database's records (page 57). Therefore when retrieving a small number of records in a recordset, you may want to use a dynaset instead of a snapshot unless this is the first time you are using a newly created snapshot or dynaset.
  • When either a snapshot or a dynaset is first created -- prior to any movelast operation -- both the snapshot and the dynaset return one page (2048 bytes) of data. The dynaset also fetches the keyset of the dynaset. This means that on first creation, snapshots, as the name implies, return faster. However, if you were to proceed record by record sequentially through the entire recordset, you'd find that the dynaset navigates faster -- approximately two times faster. This is because navigating by keyset instead of by local pointers is more efficient.
  • Snapshots return all the selected data when movelast is executed or when the entire recordset is completely navigated. Therefore, in these two cases, trying to retrieve a large amount of data (a large number of records) could take some time. It may take less time to use Dynasets instead of Snapshots in this scenario (page 57).
  • Snapshots can become outdated (the data is no longer current) quickly in a multiuser environment (page 57).
  • Snapshots cannot use the Transaction statements (BeginTrans, CommitTrans, and RollBack).
  • Snapshots or dynasets cannot use the Seek method because Seek applies only to table objects. However, snapshots or dynasets can use the Find method instead of the Seek method.
  • Snapshots cannot use Edit, AddNew, Delete, or Update properties that pertain to data changes made in records. Snapshot objects are a read-only type of dynaset.
  • Snapshot objects may be good for taking summary reports, since they contain a fixed copy of the data as it existed when the snapshot was created. If data is changed, a snapshot will not show the change until the snapshot is rebuilt (page 57).
  • Snapshots can be created from an existing dynaset or snapshot, but you cannot create a dynaset from an existing snapshot (page 56).
  • Snapshots can contain table name(s), attached tables, querydef objects or SQL statements(pg. 56).
  • Snapshot object membership is fixed (page 48).
Dynasets Objects:

  • Dynaset and snapshot objects can use the Sort property, but the table object and the data control cannot use the Sort property. To sort data with a data control, use the ORDER BY clause of an SQL statement or query. To sort a table object, set an Index property on a field that already has a Index specified (example shown on pages 50 and 75).
  • Dynasets are the most flexible of the three objects listed in the table above (page 51).
  • Dynasets are a dynamic (not fixed) subset of records. Dynasets can contain attached tables, table name(s), querydef object name or SQL query (page 51).
  • Filters are used to screen records to be brought back in dynasets or snapshots (page 53). Table objects cannot use filters.
  • Dynasets can be locked with a page-locking scheme with a page containing a maximum of 2K of data (page 54). Page 54 also mentions pessimistic and optimistic locking methods.
  • Dynasets that are formed because of a query or SQL string are suspended until the query or SQL string returns the first record (page 51).
  • Dynaset or snapshot objects can be filtered using the Filter property or sorted using the Sort property even further by using a second dynaset or snapshot object(page 53).
  • Dynaset or snapshot objects are used with querydef objects. Also, the ListParameters method returns a snapshot with one record for each parameter used by the query (pages 93 and 97).
  • Dynaset objects do not reflect changes made by others until you recreate the Dynaset variable or execute the CreateDynaset method with no arguments (page 55).
  • Dynaset object membership is fixed, you can add, change, and delete records, and a result is returned by a query (page 48).
  • Dynaset objects can create an inconsistent dynaset with the DB_INCONSISTENT flag. But it may be harder to keep referential integrity when this flag is specified (pages 58, 59, and 85).
  • To improve performance, you may want to add the option DB_READONLY if you are not writing to or allowing the users to make changes to database records (pages 58 and 59).
Table objects:

  • Table objects have direct access to the data records (page 49). The data in a table object variable always reflects all current changes, including the additions of new records and the deletions of existing records (page 50).
  • Table objects cannot be created from attached tables (page 50).
  • Table object membership can change. You can add, change, and delete records, but there is no result returned by a query (page 48).
  • Table objects cannot use the Find method (page 72).
  • Table, database and dynaset objects can be locked, but a snapshot object cannot be locked (pages 88 and 89).
  • Table objects provide the most up-to-date view of your data because the data in a table variable always reflects all current changes (page 50).
  • Table objects can be ordered on a Indexed field, the Index property does apply. But the Index does not apply to data controls, snapshots or dynasets (see example on page 75).
  • When looking for a single, specific record, you may want to use the Seek method with a table object because it is the fasted way to retrieve a single record (page 74).
QueryDef Objects:

  • querydef objects may be more efficient. For example, use a stored query of an SQL string as an argument to the recordset of querydef to produce a filtered dynaset or snapshot instead of creating a dynaset or snapshot and then filtering it (page 67).
  • querydef objects do not store data. They store the definition of a query used to retrieve data (page 91).
  • querydefs can be created only on a Microsoft Access or Visual Basic database (page 92).
  • querydefs require a name. You must supply a name for the query when you create it (page 92).
Modification Type:MinorLast Reviewed:1/8/2003
Keywords:KB103442
©2002 Microsoft Corporation. All rights reserved.