Using DAO for data access in your VB apps

There's more than one way to get at data from Visual Basic. DAO was the first, and it's still viable. We kick off our series on data access with VB by looking at how you can implement DAO in your applications.

When it comes to implementing a data access solution in your VB applications, you currently have three choices: Data Access Objects (DAO), Remote Data Objects (RDO), and ActiveX Data Objects (ADO). In this series of articles, we will examine each of these options, noting their similarities and differences. We'll also look at some cases where one is better suited for a specific task than another. Let's start with a look at DAO.

DAO basicsDAO, which was created before RDO and ADO, is a set of objects that enables client applications to programmatically access data. But DAO doesn't just let you access data—it also lets you control and manage local and remote databases in various formats. Using DAO, you can create and modify the database structure; create tables, queries, relationships, and indexes; retrieve, add, update, and remove data; implement security; work with different file formats; and link tables to other tables.

The DBEngine is the highest-level object in the DAO object model. It contains all other objects and collections. The Database object is the member of the Databases collection of the default Workspace object, which is a member of the Workspaces collection of the DBEngine object.

With DAO, objects you use to work with the data in the database are generally not saved but are created every time you need them. Objects that represent the structure of the database are saved with the database. When you create a new DAO object to be saved in the database, you have to append it to the appropriate collection using the collection's Append method. Keep in mind that all DAO objects are indexed beginning with zero.

DAO lets you work with three database formats:

· Microsoft Jet (all databases that are created with the Microsoft Jet database engine)

· Installable ISAM format

· ODBC data sources

Jet and ISAM data use the Microsoft Jet object model; however, with ODBC data, you can use either Microsoft Jet or ODBCDirect. There are some limitations in accessing ODBC data using Jet, although you can use it if you need to take advantage of a particular Jet feature. But ODBCDirect is more flexible, allowing you to run queries or stored procedures against the backend server and perform batch updates and asynchronous queries. It also makes your application run faster because it allows you to bypass Microsoft Jet's middle layer.

The DBEngine object contains two collections: Workspaces and Errors. The Workspaces collection is the default collection of the DBEngine, so you don't have to refer to it explicitly. When you don't specifically create a new Workspace object, DAO will create one for you. The setting of the DefaultType property of DBEngine determines what type of workspace is created for Microsoft Jet or ODBCDirect. The default value of this property is dbUseJet, but you can explicitly set it to dbUseODBC as the type argument of the CreateWorkspace method.

The Workspace object defines a session for a user based on users' permissions and allows managing of the current session. It also contains open databases and offers mechanisms for simultaneous transactions and for securing the application. The Fields collection is the default collection for TableDef, QueryDef, Index, Relation, and Recordset objects. Recordset objects can be of the following types: Table, Dynaset, Snapshot, or Forward-Only.

DAO with ODBCDirectThe DAO ODBCDirect object model includes a subset of the objects in a Microsoft Jet workspace and the Connection object, as shown in Figure C.

Figure C

DAO with ODBCDirect object model (Source: Microsoft)

To establish a connection using ODBCDirect, you have to use the OpenConnection method on a new Connection object or the OpenDatabase method to open a new Database object. A Connection object represents a connection to an ODBC database in an ODBCDirect workspace. The Connections collection contains all currently open Connection objects. When you open a Connection object, it is automatically appended to the Connections collection of the Workspace object. When you close a Connection object with the Close method, the Connections object is removed from the Connections collection.

In addition to the Table, Dynaset, Snapshot, and Forward-Only types of Recordsets, ODBCDirect offers the Dynamic type.

Advantages and disadvantages of using DAOOn the plus side, DAO is fairly easy to use. And since DAO has been around longer than RDO or ADO and has been used in more projects, it pays to know how DAO works. Furthermore, if your application is running in a 16-bit environment, DAO is your only choice.

But DAO is older technology, and it doesn't offer as much functionality as RDO and ADO. For instance, ADO can provide an interface to e-mail and file systems and custom business objects, as well as other sources. Microsoft is now focusing most of its improvements and advances on ADO, as well.

Generally, it's better to use DAO for accessing local databases where the speed is not the top priority and the number of users is limited, and to use either RDO or ADO for accessing remote databases and for larger scale projects.

2. Go to Project References and select Microsoft DAO 3.6 Object Library, as shown in Figure D. (Depending on the version of VB you are using, you will have a corresponding DAO Object Library version, so if you don't have DAO 3.6, use an earlier version instead.)

Figure D

DAO object library selected in Project References

3. Add two combo boxes (cboLastNameJet and cboLastNameODBCDirect) and two command buttons (cmdGetDataJet and cmdGetDataODBCDirect) to the form.

6. Add the code shown in Listing B to the Private SubcmdGetDataODBCDirect_Click() event.

7. Modify strLocation to reflect the location of the Northwind database on your machine or use another .mdb database and modify Set dbJet = wrkJet.OpenDatabase(strLocation & "Northwind.mdb") to reflect the name of the database.

8. Modify strConn to reflect the DSN name, password, and UID of a remote database.

9. Modify the query in Set rsODBCDirect = conODBCDirect.OpenRecordset("SELECT LastName FROM Employees", dbOpenDynamic) to reflect the query you'd like to run.

10. Press [Ctrl][F5] to run the project.

11. Click the Get Data Jet button and the Get Data ODBC Direct button to obtain data using Microsoft Jet and ODBCDirect, respectively.

12. You should see a screen like the one shown in Figure F.

Figure F

Results of clicking a Get Data button

SummaryIn this article, we've examined DAO objects and the object models of Microsoft Jet and ODBCDirect. We also created a simple VB project that showed how you might use DAO with Jet and ODBCDirect to access data.

In our next installment, we'll turn our attention to RDO. We'll look at its object model and discuss a few advantages and disadvantages, and then we'll work through some code samples illustrating RDO's use.