DAO enables you to use a programming language to access and manipulate data in local or remote databases, and to manage databases, their objects, and their structure.
DAO supports two different database environments, or "workspaces."
Use the Microsoft Jet workspace when you open a Microsoft Jet database (.mdb file) or other desktop ISAM database, or when you need to take advantage of Microsoft Jet's unique features, such as the ability to join data from different database formats.
The ODBCDirect workspace provides an alternative when you only need to execute queries or stored procedures against a back-end server, such as Microsoft SQL Server, or when your client application needs the specific capabilities of ODBC, such as batch updates or asynchronous query execution.
DAO Objects
There are 17 different DAO object types. You can declare new DAO object variables for any of the object types.
For example, the following Visual Basic for Applications (VBA) code creates object variables for a Database object, a dynaset-type Recordset object, and a Field object:
Dim dbsExample As Database
Dim rstExample As Recordset
Dim fldExample As Field
Set dbsExample = OpenDatabase("Biblio.mdb")
Set rstExample = dbsExample.OpenRecordset("Authors", _ dbOpenDynaset)
Set fldExample = rstExample.Fields("Au_ID")
DAO Collections
Each DAO object type other than DBEngine also has a corresponding collection. A collection includes all the existing objects of that type. For example, the Recordsets collection contains all open Recordset objects. Each collection is "owned" by another object at the next higher level in the hierarchy. A Database object "owns" a Recordsets collection. Except for the Connection and Error objects, every DAO object has a Properties collection.
Most DAO objects have default collections and default properties. For example, the default collection of a Recordset object is the Fields collection and the default property of a Field object is the Value property. You can simplify your code by taking advantage of these defaults. For example, the following code sets the value of the PubID field in the current record:
rstExample!PubID=99
DBENGINE AND WORKSPACE OBJECTS
All DAO objects are derived from the DBEngine object. You can set the DefaultType property on the DBEngine object to determine the workspace type (Microsoft Jet or ODBCDirect) to create on subsequent CreateWorkspace method calls, or you can override this property with the type argument in the CreateWorkspace method itself. When your application creates a workspace, the appropriate library — the Microsoft Jet database engine or ODBC — is loaded into memory at that time.
You can open additional Workspace objects as needed. Each Workspace object has a user ID and password associated with it.
Opening a Database
To open a database, you simply open an existing Database object, or create a new one. This object can represent a Microsoft Jet database (.mdb file), an ISAM database (for example, Paradox), or an ODBC database connected through the Microsoft Jet database engine (also known as a "Microsoft Jet-connected ODBC database").
Data-Definition Language
You can use object variables and other DDL features to modify your database structure. For example, you can add a new Field object to an existing table with the following code:
Dim dbs As Database, tdf As TableDef, fld As Field
' Open a database.
Set dbs = OpenDatabase("Biblio.mdb")
' Open a TableDef.
Set tdf = dbs.TableDefs("Authors")
' Create a new field.
Set fld = tdf.CreateField("Address", dbText, 20)
' Append field to the TableDef Fields collection.
tdf.Fields.Append fld
This code creates a new object variable for a Field object and adds it to a TableDef object with the Append method. Because a TableDef object contains the definition of a table, the table now has a field named Address for entering data. In much the same way, you can create new tables and new indexes.
Data Manipulation
DAO provides an excellent set of data manipulation tools. You can create a Recordset object to conveniently query a database and manipulate the resulting set of records. The OpenRecordset method accepts an SQL string, or a QueryDef (stored query) name as a data source argument, or it can be opened from a QueryDef object or a TableDef object, using that object as its data source. The resulting Recordset object features an extremely rich set of properties and methods with which to browse and modify data.
The Recordset object is available in four different types — Table, Dynaset, Forward-Only, and Snapshot.
Transactions
All Database objects opened against a Workspace object share a common transaction scope. That is, when you use the BeginTrans method on a Workspace object, it applies to all open databases within that Workspace object. In the same way, when you use the CommitTrans method against the Workspace, it applies to all open databases in the Workspace object.
Replication
You can use database replication to create and maintain replicas of a master Microsoft Jet database, using the Synchronize method to periodically update all or part of the replicas, or to copy new data from one replica to another. You can also restrict the update to only selected records, using the ReplicaFilter property, and then synchronize those records with the PopulatePartial method.
Security
You can restrict access to one or more .mdb databases or their tables using security settings established and managed by the Microsoft Jet database engine. In your code, you can establish Group and User objects to define the scope and level of permissions available to individual users on an object-by-object basis. For example, you can establish permissions for a specific user to provide read-only access to one table and full access to another.
Connecting to a Database
A Connection object is similar to a Database object. In fact, a Connection object and a Database object represent different references to the same object, and properties on each of these two object types allow you to obtain a reference to the other corresponding object, which simplifies the task of converting ODBC client applications that use Microsoft Jet to use ODBCDirect instead. Use the OpenConnection method to connect to an ODBC data source. The resulting Connection object contains information about the connection, such as the server name, the data source name, and so on.
Queries
Although DAO does not support stored queres in an ODBCDirect workspace, a compiled query can be created as a QueryDef object and used to execute action queries, and can also be used to execute stored procedures on the server. The Prepare property lets you decide whether to create a private, temporary stored procedure on the server from a QueryDef before actually executing the query.
Parameter queries can also be passed to the server, using Parameter objects on the QueryDef. The Direction property lets you specify a Parameter as input, output, or both, or to accept a return value from a stored procedure.
Data Manipulation
Creating a Recordset object is a convenient way to query a database and manipulate the resulting set of records. The OpenRecordset method accepts an SQL string, or a QueryDef object (stored query) as a data source argument. The resulting Recordset object features an extremely rich set of properties and methods with which to browse and modify data.
The Recordset object is available in four different types — Dynamic, Dynaset, Forward-Only, and Snapshot — corresponding to ODBC cursor types — Dynamic, Keyset, Forward-only, and Static.
A batch update cursor library is available for client applications that need to work with a cursor without holding locks on the server or without issuing update requests one record at a time. Instead, the client stores update information on many records in a local buffer (or "batch"), and then issues a batch update.
Asynchronous Method Execution
The Execute, MoveLast, OpenConnection, and OpenRecordset methods feature the dbRunAsync option. This allows your client application to do other tasks (such as loading forms, for example) while the method is executing. You can check the StillExecuting property to see whether the task is complete, and terminate an asynchronous task with the Cancel method.