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:
- 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.
- 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:
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.
- Start the program (or press the F5 key). Close the form to end the
program.