Microsoft Jet (through DAO) offers several options for recordset types. The type of recordset you choose should be based on your specific data access needs. The available recordset types are:

Table

This type of recordset refers to either a local table in the current database, or a linked table that resides in another database. When you use a Table type recordset, Jet opens the actual table itself, and any changes you make are made to the table directly. Note that a Table-type recordset can only be based on a single table-it cannot be based on a query that joins or unions two or more tables.

Use this type when you need direct access to indexes. For example, if you want to use the Seek method to locate data based on an index, you must use the Table type recordset, since it is the only one that directly supports indexes.

Dynaset

This is the most flexible type of recordset. A dynaset is a logical representation of data from one or more tables that is accessed internally through a set of ordered pointers and pages of data. You can use a dynaset to retrieve or update data based on a set of joined tables (as with a query). You can also represent heterogeneous joins in a recordset-the tables joined into the dynaset can be based on disparate data sources such as Jet databases, Paradox tables, SQL Server data, etc.

Use this type when you want a picture of data against multiple tables, and/or want to update the data in multiple tables.

Snapshot

The Snapshot type or recordset is a static, read-only picture of data. The data in the Snapshot is a fixed picture of the data as it existed when the Snapshot was created.

Use this type of recordset when you want a fixed picture of data, and don''''t need to make changes to the data.

Advanced Recordset Options

There are variety of options you can use when working with Dynaset and Snapshot recordsets. The following table shows these options:

Option

Applies To

Description

Inconsistent updates are those that violate the referential integrity of multiple tables represented in a multi-table dynaset. If you need to bypass referential integrity, use the dbInconsistent option and Jet will allow you to do so.

Microsoft Jet Transactions

Microsoft Jet implements a sophisticated transaction model that allows you define sets of data operations as an atomic unit. You can then tell Jet to commit or rollback the unit as a whole. There are several developer issues in working with transactions that you should be aware of.

Scoping of Transactions

Transactions are scoped at the DAO Workspace level. Because of this, transactions are global to the Workspace object, not to a specific database or recordset. If you change data in more than one database, or more than one recordset, and you issue a RollBack command, the Rollback affects all of the objects opened within the workspace.

Issues With the Temporary Database

Database transaction systems typically operate by buffering the data changes within a transaction to a temporary database. Then, once the transaction is told to commit, the contents of the temporary database are merged back into the original database. Jet uses a similar scheme-it buffers transactions in memory until cache memory is exhausted. It then creates a temporary Jet database and writes transaction changes there. This temporary database is created in the directory pointed to by your TEMP variable. If the disk space available for transactions is exhausted, a trappable runtime error occurs, allow you to handle the condition by either freeing up space, or issuing a RollBack to cancel the transaction.

Developing an Object Dictionary

As an example of the object and property interrogation capabilities of DAO, I have included two add-ins with this paper, one for Access version 2.0 and one for Access version 7.0. These add-ins allow you dump the entire DAO structure of a database to an ASCII file.

See the .WRI files that accompany these add-ins for complete information on how to install and use them.

The Access 2.0 Version

First, let''''s look at the Access 2.0 version and see how it works. All functionality is found in the following procedures found in the module behind the frmDAOStructureDumper form:

Option Compare Database   ''''Use database order for string comparisons
Option Explicit
Dim intFileOut As Integer
Dim fErrors As Integer
Dim intTabs As Integer
Dim lngLines As Long
Const FMSVersion = "2.0"
Sub cmdCancel_Click ()
  DoCmd Close
End Sub
Sub cmdClose_Click ()
  DoCmd Close
End Sub
Sub cmdNotepad_Click ()
  Dim x As Variant
  x = Shell("write.exe " & Me!txtFileName, 1)
End Sub
Sub cmdStart_Click ()
  Dim fOK As Integer
  If Me!txtFileName <> "" Then
    If Me!txtTabs <> "" Then
      intTabs = Me!txtTabs
    End If
    DoCmd Hourglass True
    DoCmd GoToPage 2
    fOK = fDumpDAO(CStr(Me!txtFileName), CInt(Me!chkProperties), CInt(Me!chkErrors))
    Me!cmdNotePad.enabled = True
    DoCmd Hourglass False
    Me!txtLines.Caption = lngLines & " lines were written to file: " & Me!txtFileName
    DoCmd GoToPage 3
  End If
End Sub
Function fDumpDAO (strFile As String, fProps As Integer, fErrors As I
上一页  [1] [2] [3] [4] [5]  下一页