DAO object Model

DAO provides two different object models, an object model that accesses data through the jet engine, as shown in the following illustration. Another object model is to access data through ODBCDirect. Both of these object models are hierarchically structured. The two object models are now described separately.
DBEngine is an Object-based object that contains two important sets (Collection), one is a errors collection and the other is a workspaces collection. The DAO operation will always produce some errors, every error, DAO generates an Error object, these error objects are placed in the Errors collection, you can use Errors.Count to calculate the number of errors. In fact, for each set, you can use Collection.count to find out the number of objects in the collection.
There can be only one DBEngine object per application, but there may be multiple Workspace objects, all of which are contained in the workspace collection. Each Workspace object contains a database object that contains a number of objects for manipulating the database. Some of these objects are specific to the Jet database, such as container, TableDef, and Relation objects, and some are useful for all databases, such as Recordset objects and QueryDef objects.

The following is a detailed description of the main objects in the object model.
1.DBEngine objects
The DBEngine object is unique, cannot be created, and cannot be declared. In general, you can use the properties of the DBEngine object to set security for database access by setting the default username and default password for accessing the database, such as:
Dim Dben as DAO. Dbengine=new DAO. DBEngine ()
Dben.defaultuser= "RTLinux"
Dben.defaultpassword= "AAA"
Because the Jet database engine allows users to define a workgroup, you can set different database access permissions for each user in the workgroup. You must tell the DAO the file that stores information about this workgroup by setting the "SystemDB" property of the DBEngine object, such as:
Dben.systemdb= "C:\" & "System.mdw"
When you use the Jet database engine, you must set the DBEngine object's "DefaultType" property to "Dbusejet".
The DBEngine object also provides a number of ways to manipulate workspaces (Workspace) and databases, such as: creat Workspace method to create a workspace, creatdatabase create a database, OpenDatabase open a database, CompactDatabase compresses a database, repairdatabase fixes a database, and so on.
2.Error objects
The Error object is a child object of the DBEngine object. In the event of a database operation error, you can use the standard VB Err object for error handling, or you can save the error message in the DAO's Error object. The Error object contains the following properties:
(1) Description property. This property contains the error warning message text, which appears on the screen without error handling.
(2) Number property. This property contains the error number that generated the error.
(3) The Source property. This property contains the name of the object that generated the error.
(4) HelpFile Properties and HelpContext properties. These two properties set Windows Help files and Help topics about this error.
3.Workspace objects
A Workspace object defines a database session. The session describes a series of features completed by Microsoft Jet, which form a transaction scope and are subject to the permissions determined by the user name and password. All the workspace objects are grouped together to form a workspace set. You can create a new workspace using the CreateWorkspace method of the DBEngine object, simply passing the name of the workspace and the user information to this method, such as:

Note: When a new Workspace object is created, it is not automatically added to the workspace collection, and the workspace object must be added to the workspace collection using the Append method.
4.Database objects
Once you have created a database with CreateDatabase or opened a database with OpenDatabase, you have generated a DB object. All database objects are automatically added to the database collection. The following code uses the database collection to list the pathname of all the databases:
Db=ws. OpenDatabase (Txtmdbfile.text)
The database object has 5 child sets, namely the Recordsets collection, the QueryDefs collection, the TableDefs collection, the Relation collection, and the Containers collection, which are Recordset objects, QueryDef objects, respectively , a TableDef object, a Relation object, and a collection of container objects. Database objects provide methods to manipulate databases and create these objects.
(1) The Execute method. Executes an SQL statement. This SQL statement can not only manipulate the data in the database, but can also be a DLL to modify the structure of the database.
(2) OpenRecordset method. Executes a query in the database that may involve a table connection, and the result of the query is returned as a Recordset object.
(3) CreateQuery method. Create a stored procedure in the database and create a QueryDef object.
(4) CreateRelation method. Create a Relation object that defines the relationship between two TableDef or QueryDef.
(5) Creattabledef method. Creates a data table in the database and returns a TableDef object.
5.Recordset objects
A Recordset object is the most frequently used object that represents a record of a table or query result in a database. For example, the following statement adds a record to the first table in the database:
For each tempdb in Workspace (0). Databases
Debug.Write ("Databases" (x). Name= ", Temdb. Name)
Next
You can also move the location of the current record at any point in the recordset using the Recordset object MoveNext, MovePrevious, MoveFirst, and MoveLast methods.
The Recordset object also contains another object--field object that represents a field in a datasheet that can be used to access any field in the datasheet, such as: The following statement assigns the Name field value of the current record in the table to the variable sname:
Dim DB as DAO. Database=ws. OpenDatabase ("User", "AAA")
Sname=cstr (db.recordsets (0). Fields ("name"). Value)
The source parameter can be a table name, a name for a query, or an SQL statement to create a Recordset object. This parameter is required, and the other three parameters are optional.
The type parameter refers to the types of the recordset, where it is necessary to explain the difference between the dynaset (dynaset) and the snapshot (snapshot). Dynaset this Recordset object is powerful and flexible, and when the recordset is created, only the primary key of each record is fetched and cached locally, so the dynaset is created quickly because the primary key is always smaller than the entire record size. After the dynaset is created, if you want to query for records, the cached primary key is used to query. Instead, snapshot takes the entire record out of the local area, so it's slow, and if another user modifies the database, the user will not be able to see the change.
6.TableDef objects
The TableDef object is also a frequently used object that has two child objects: one is a Field object and the other is the Index object. You can use the TableDef object to access each field (Field object) and table index (Index object) for a single table. An important method of TableDef objects is CreateField. The Creatfield method is used to create a new field in a table whose syntax is:
Field=tabledef. CreateField (Fieldname,fieldtype,fieldlength)
Three parameters indicate the field name of the new field, the field type, and the field length, respectively.
This object is essential for dynamically creating a new table or dynamically modifying the structure of a table. For example, the following statement creates a new table with only fields:

7.QueryDef objects
The querydef object is used to define a query. It has two objects: one is a Field object and one is a parameter object. Use the CreateQueryDef method of the database object to create a QueryDef object. Users can use the querydef object in the Jet object model in this way:
(1) The SQL attribute of the QueryDef object is used to set or return the definition of the SQL statement of the query;
(2) To set or return the parameters of the query definition with the querydef object parameter;
(3) Set the type of the query with the type attribute of the QueryDef object, which includes: Fetching records from existing tables, creating a new table, inserting records from one table into another, deleting records, updating records, and so on;
(4) Using the object's MaxRecords property to limit the number of records returned by the query;
(5) The number of records returned by the query is obtained by using the Returnrecords property of the object;
(6) Execute the query with the object's Execute method;
(7) The RecordsAffected property of the object to return the number of records affected by this query;
(8) Using object Odbtimeout to set the maximum time spent querying ODBC data sources;
(9) Connect ODBC data source to query with object's Connect property;
(10) Using the object's OpenRecordset method to return the Recordset object and use this Recordset object to get the result of the query.
8.Relation objects
The Relation object is used to define the relationships between fields in different tables or in different queries. For example, define a primary key in one table as a foreign key to another table, define a one-to-one or one-to-many relationship between fields in one table, and so on. Use the CreateRelation method of the database object to create a Relation object.
9.Field objects
The Field object is the Bottommost object in the Jet object model, which represents a field in a table. You can set various properties for a field, such as the field type (the Type property), the field length (Size property), the value of the field in the current record (the "Value" property), the field's default value (the "DefaultValue" property), and whether the field can be empty (the "Required Wait
10.Group Objects and User objects
The user object represents one of the users of the database, and the group object contains a set of users with the same permissions. You can use these two objects to manage the users of your database. Here is a function to create a user, and you must have the right to create a user when you use this function.
ODBCDirect is a technology that allows you to operate an ODBC database server without loading the Microsoft Jet database engine. It is a new feature added after DAO3.5. ODBCDirect provides a mechanism that makes it possible for DAO to turn jet based access into a RDO access method. However, ODBCDirect does not completely replace RDO. To access a database using ODBCDirect in your application, just add the following words to the beginning of your application (before any use of DAO):
Dbengine.defaulttype=dbuseodbc
You can also specify DBUSERODBC parameters in the CreateWorkspace method to use ODBCDirect. Such as:
Dim wrkODBC as DAO. Workspace=new DAO. Workspaces (0)
Set wrkodbc=createworkspace ("Odbcworkspace", "admin", "" ", dbUseODBC)
Using the ODBCDirect DAO object model and the Jet object model are similar, the model's hierarchy diagram is as follows:

Compared to the Jet object model, ODBCDirect removes objects used for database definitions, such as TableDef objects and relation objects, but adds connection objects. The Connection object represents a connection to an ODBC data source. Generally, a Openconnect method is used to establish a connection and create a Connection object. The syntax for OpenConnection is as follows:
Conection=workspace. OpenConnection (Name,options,readonly,connect)
The meaning of the four parameters is the same as the meaning of the four parameters of the OpenDatabase method, which is no longer discussed here. Please refer to the OpenDatabase method discussed earlier.
Using ODBCDirect to manipulate ODBC data sources has the following advantages:
(1) ODBCDirect enables program code to perform faster and more efficiently by providing direct access to ODBC data sources. Because it does not need to load the Microsoft Jet database engine, it consumes only a small amount of resources on the client. An ODBC server can respond to all query processing.
(2) ODBCDirect provides the ability to access specific servers that Microsoft Jet cannot implement using ODBCA. For example, for a server that supports a cursor set, ODBCDirect allows the user to specify whether the cursor set location is local or on the server. In addition, you can interact with server-level stored procedures by specifying input values and checking return values, which is not possible in Microsoft Jet.
(3) ODBCDirect supports batch updates of data, you can store the recordset objects in the cache locally, and then the batch will export these changes to the server.
(4) Using ODBCDirect, you can create a simple set of cursor-free sets of results, or you can create a complex set of cursors. It can also perform queries that return multiple result sets, or limit the number of rows returned, and monitor the information and errors generated by all remote data sources, without affecting the performance of the query execution.
However, ODBCDirect also has some drawbacks. Some of the following features are ODBCDirect that cannot be completed, but Microsoft Jet can.
(1) Updatable connections. Only Microsoft Jet workspaces can be used to update data in a Recordset object that is based on a multiple-table connection.
(2) different kinds of connection. You can only use a Microsoft Jet workspace to perform table joins of different data sources.
(3) The operation of the data definition language (DDL). You can use a Microsoft Jet workspace only for DDL operations through DAO. ODBCDirect does not provide TableDef objects, you cannot use DAO to create or modify tables. However, you can use ODBCDirect to perform DDL operations by executing SQL DDL statements.
(4) Forms and controls are combined. If your application needs to combine data from an ODBC data source with a form, you must use Microsoft Jet, and the data accessed in the ODBCDirect workspace cannot be combined with a form or control.
If the application does not require the above functionality, you can use ODBCDirect.
Note: You can define Microsoft Jet and ODBCDirect workspaces in your application and combine them in a variety of ways. For example, in the same function, you can define a Microsoft Jet workspace to use DAO to perform DDL operations, and you can define ODBCDirect workspaces to perform asynchronous queries.