et
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
dbAppendOnly
Dynaset
You can specify this option to allow only the addition of new records. Because Jet can dispense with certain internal routines, this option allows a more efficient and fast recordset if all you want is to add data.
dbSeeChanges
Table, Dynaset
If you invoke the Edit method on a record and another user changes data in the record, but before the Update method was invoked, a run-time error will occur. This is helpful if multiple users have simultaneous read/write access to the same data.
dbDenyWrite
Dynaset, Snapshot
Prevents other users from modifying data in any of the tables that are included in the recordset.
dbDenyRead
Table
Prevents other users from reading data from the table
dbReadOnly
All
Prevents you from making changes in the recordset''''s underlying tables (implied on a snapshot)
dbForwardOnly
Snapshot
Creates a snapshot that you can only move forward through. This allows Jet to dispense with the creation and maintenance of internal buffers and structures, resulting in a more efficient recordset with faster access.
dbSQLPassThrough
Dynaset, Snapshot
Use this option to pass a SQL string directly to an ODBC database for processing. Jet does no internal processing of the query.
dbConsistent
Dynaset
Allows only consistent updates*
dbInconsistent
Dynaset
Allows inconsistent updates*
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] 下一页 [常用软件]Dao ne Deepnet 三合一的浏览器 浏览器 [Web开发]DAO RDO ADO ADO.NET [Sql Server]ASP编程入门进阶(廿一):DAO SQL之建立数据库表
|