- Chapter 3: Making Access Project and Data Technologies Choices
- Using DAO Versus ADO Versus XML
- Summary
Using DAO Versus ADO Versus XML
How you design your application also varies with which front-end type you use. The choice of using DAO versus ADO is fast becoming a moot point because Microsoft is aiming that way, made even more apparent in Access 2002 since the company made only bug fixes to DAO, not really any enhancements.
As mentioned at the beginning of this chapter, DAO has been around for several versions of Access and has pretty well been the standard for database manipulation with Jet from VB and all Office products.
Then along came ADO. ADO came on the scene because DAO was written and optimized mainly for Jet. The idea is that with the way the world is going with the Internet and multiple data sources, an easier, more generic way to access that data was believed to be needed—hence, ADO.
NOTE
In Access 2000/2002, not all functionality is provided in ADO to replace DAO. For example, when using objects and methods such as Me!subForm.Requery, the DAO object model is still used under the covers.
Where using DAO requires a reference to one object model, ADO requires three to cover most of the same ground. You can see this in Figure 3.3, by choosing References from the Tools menu in the VBE.
){kind=link}
As just mentioned, DAO uses one library, Microsoft DAO 3.6 Object Library, whereas ADO uses three (see Table 3.2). Chapter 5, "Introducing ActiveX Data Objects," discusses each library in more detail.
Figure 3.3 When converting to ADO from DAO, you can have code that references both in the same application.
Table 3.2 ADO Libraries and Their Purpose
|
Library |
Purpose |
|---|---|
|
Microsoft ActiveX Objects 2.6 Library |
Data manipulation |
|
Microsoft ADO Ext. 2.6 for DDL and Security |
Data definition and security |
|
Microsoft Jet and Replication Objects 2.6 |
Replication |
TIP
Whichever version you want as the default, place it before the other. For example, if I want to use ADO recordsets as the default, I move its library up before DAO's. This is important because when you Dim something as a recordset, you want to get the right type. Otherwise, errors can occur. You can also use the Library.ClassName syntax:
Dim rstX As ADODB.Recordset Dim dynY As DAO.Recordset
NOTE
In revising this book for Access 2002, I decided to fall into line and switch all the code to ADO. However, if you want to see most of the examples in DAO, you can pick up a copy of my Microsoft Access 2000 Power Programming. Trying to cover both ADO and DAO to the extent of all the examples would make this book about 2,000 pages long.
Just because you're using SQL Server as a back end doesn't automatically mean that you should use an ADP with ADO. There are still good reasons to use Jet-linked table SQL Server applications in many situations:
Your application requires storage on the front end, such as user preferences or SQL strings in tables. In an ADP, you have to use the Registry or file system for such storage.
Your application requires users to be able to create and store ad hoc queries, and you can't or don't want to give the users permissions to create server objects.
Linked-table applications are still better at joining information from heterogeneous data sources (ISAM stuff).
Although I mentioned that ADO is harder to use than DAO in the previous edition of this book, it really is just a matter of getting used to the new syntax. Microsoft also has enhanced the ADO object model since it was first introduced.
The bottom line is that DAO is fading in future versions of Microsoft products. It probably will be supported in a couple more versions, but even DAO 3.6 hasn't had any enhancements made. So if you're using DAO now, start looking at converting your applications; however, if an application doesn't have major changes in store, I would hold off.
If you're just starting to develop applications in VBA and Office products, or even have to start a new application, jump on the ADO bandwagon. Just know that there are some issues you might have to work around.
Regarding XML, it's not really a choice of using either ADO or XML exclusively, but when to use each. As I mentioned, XML allows you to read and write data to and from other systems and applications. It was created originally to use with the Internet, but is also useful between different file formats locally.
So when you have a database application that needs to interface with another system, especially other file formats, you can use XML to perform this function. When you get the data into Access or SQL Server tables, use ADO. You can read more about XML and ADO in the chapters listed in the Summary section.