Lesson 2: Data Access Methods

Data Access Objects

DAO is the COM interface to Jet. Although it is best suited for use with Microsoft Access, FoxPro, or dBase files, DAO will connect to ODBC-compliant databases. It was updated recently to provide better support for remote data sources. The enhanced feature, called ODBCDirect, will be discussed in this section.

Figure 7.2 How an application uses DAO to connect to remote databases

Connecting With DAO

To build a robust database application using any of the interfaces, you need to take a number of steps. To connect using DAO, you set the DAO reference, declare object variables , and open the connection.

---

Note

This sample code illustrates just one possible way to connect DAO to a database. This course is not intended to instruct you how to build complete solutions.

---

Setting the DAO Reference

To use DAO, you must first set a reference to the DAO object library. At the time of writing, this library is Microsoft DAO 3.5 Object Library . The object library contains all the information you need when writing code for DAO. The object types, such as the Recordset , used by DAO are not inherent to Visual Basic. If you fail to create an appropriate reference to DAO, you receive an error when you attempt to declare the necessary object variables.

The Workspace Object

The DAO Workspace object is used for setting environment information, such as user names or which database engine to use. When using DAO, you do not have to create a Workspace object. It is automatically provided for you. However, the Workspace object also controls whether you use the Microsoft Jet database engine or ODBCDirect to access external data.

Example

This example uses Visual Basis to declare an object variable as a Workspace :

 Dim wrkJet As Workspace ' Create Microsoft Jet Workspace object. Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet) 

The Database Object

A Database object represents an open database. Use the Database object and its methods and properties to manipulate an open database.

Example

This example uses Visual Basic to declare a Database object variable:

 ' Declare the database object variable Dim db as Database 

Opening a Connection

Once your application has created a Database object variable (and optionally built a Workspace), use the OpenDatabase method to create a connection.

Example

This example uses the db object variable created earlier to open a connection to the Northwind database in Visual Basic:

 ' Open a database using the OpenDatabase method Set db = OpenDatabase("C:\DataBases\Northwind.mdb") 

Once a connection has been established with the database, your application can begin requesting data, adding new records, deleting records, or updating data. Use the Recordset object to manage data in the database.