3.00
WINDOWS
kbprg
The information in this article applies to:
- Microsoft Visual Basic programming system for Windows, version 3.0
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 re-
create
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).
|