Navigation:  Articles > Sep-1997 >

Which to Choose

Previous pageReturn to chapter overviewNext page

Peter Vogel          
With all the information in this issue, you still might not be sure which data access method you should use. Here's a summary of what works and where. Plus, here's a look at what Microsoft has in the works.

This was written in 1997 - Can we help you upgrade your database to Access 2007 or 2010     Garry Robinson, manager of this site.

Which data access method is right for your applications? Like so many things, that depends. In the long run, we'll all move to some tool (or tools) based on OLE DB. Unfortunately, you're going to need to decide when to move and what to do in the meantime. In this article, I'll sum up the pros and cons of moving to each method. I'll limit myself to discussing DAO, RDO, ADO, and ODBCDirect. I'm assuming that you don't want to bypass the benefits of object-oriented data access to use the raw ODBC API calls.
Before I begin, I should point out that all of these methods still use ODBC to retrieve data from remote database servers like Oracle and SQL Server. Even if you use OLE DB through its front end, ADO, eventually it's ODBC that retrieves your data. But ADO is different from the other data access methods in a fundamental way: ADO is really a model for accessing data rather than a complete data access solution. ADO exists to provide a way to access a variety of data sources. Currently the data provider for relational databases is ODBC, through a connection called Kagera, though this connection may wither as native OLE DB drivers appear.
Making a decision
One decision is out of your hands. If you're working in Access 2.0, your only choice is DAO. RDO, ODBCDirect, and ADO are available only on 32-bit systems. Another limitation of DAO is that it won't function under Microsoft's Transaction Server (MTS), so if you'll be using MTS, DAO is out.
Your second consideration is the kind of data that you're going after. If you're accessing desktop databases, such as Access MDBs or xBase DBFs, then DAO will perform better than the alternatives, and it will offer more features. However, if you're going after remote databases, such as SQL Server or Oracle, you'll probably come to resent the overhead that the Jet engine imposes on your processing. If this is the case, consider moving to RDO, ODBCDirect, or ADO.
Licensing is a problem with RDO. Currently, the only way to purchase RDO is with Visual Basic. Running RDO on your clients' computers requires installation of VB5 on their computers also. Having said that, RDO's event model style of data access is the one that will be followed by ADO, so learning RDO will give you a leg up on event-oriented data access with ADO.
Climbing the curve
You're probably familiar with DAO's Recordsets, QueryDefs, and other objects. One of the benefits of ODBCDirect is that you can convert to it and still stick with DAO's object model. RDO is slightly more difficult to move to because it uses different objects, methods, and properties than DAO. However, you should find the RDO model familiar: Just replace Database with Connection and Resultset with Recordset, and you're halfway there. Going to ADO is a bigger step than going to RDO because some things that you might take for granted (that the object model is hierarchical, for instance) just aren't true anymore. If you don't have time to pick up a new technology, ODBCDirect will work best for you. If you have the time to invest in your future, start learning ADO (even if you're not going to use it right away).
The amount of work you must do as a programmer also varies between the methods. DAO is slower than the other methods because the Jet engine takes care of a great deal of the processing. However, if you move to RDO or ADO, you'll need to become familiar with the details of how your data is managed. For instance, a key item with RDO (and, to a certain extent, ODBCDirect) is that you must decide what type of cursor you intend to use for your resultsets -- the wrong decisions can slow you down considerably.
Also, none of the DAO alternatives will bind to your forms (at least, not yet). As a result, if you move away from DAO you must expect to do more coding. In a Rapid Application Development environment, or one where you don't have time to learn a new technology, you might be smart to stick with DAO and take the performance hit associated with it.
All developers are concerned about speed. The method that gets the data fastest is frequently regarded as the best. One of the early complaints about ODBC was that it would add another layer of processing to database access, and so it would always be less efficient than the vendor's native database API. Microsoft claimed that this wasn't necessarily so, and history has shown that they were right.
Microsoft is making the same claim about ADO -- that the move to an OLE DB-based data access method shouldn't slow you down. Given that ADO is new, it would be unfair to compare its performance to, for instance, the more mature RDO. However, early results show that ADO could be an efficient way of retrieving data. If you take the default cursor types and create a "fire-hose" cursor (forward-only scrolling, read-only), ADO is up to three times faster than RDO. However, if you switch to a bi-directional cursor or add the ability to update, performance drops and RDO is faster -- in some circumstances, 500 percent faster.
With this exception noted, RDO and ODBCDirect are usually the fastest methods, followed by ADO, and, finally, DAO. Your mileage may vary, of course.
If you're considering going to ODBCDirect because it's faster than DAO but doesn't require you to change your object model, make sure you're aware of its limitations. You can't use ODBCDirect to update fields in a recordset based on more than one table, you can't join tables from different datasources, and you can't use the SQL Create or Drop commands. However, you do gain the ability to work with stored procedures, take control of what kind of cursor you're using, and execute queries asynchronously.
RDO offers the same features as ODBCDirect, plus the ability to update fields in recordsets with joined tables. If you have experience with using the ODBC API directly, RDO could be attractive. RDO exposes the various ODBC API required handles you need to make ODBC API calls directly. As a result, you can mix RDO's object-oriented data access and direct API calls -- something not possible with the other data access methods. The RDO object model also lets you use query names as methods of the RDO object -- a neat feature.
The first thing to notice about ADO's feature set is that it isn't all there yet. Since ADO is still new, it doesn't have all of the capabilities of the more mature technologies. RDO and ODBCDirect both allow you to do asynchronous queries, for instance, but ADO does not. RDO exposes more events than ADO or ODBCDirect. There's also an interesting wrinkle in batch updates. With RDO and ODBCDirect, you can commit all your recordset updates to happen in a batch after you've finished processing the data. When the update is complete, you can check to see which (if any) of the updates failed. Although you can do the same with ADO, if one update fails, all of the updates in the batch are backed out.
Even the new features of ADO aren't all in place yet. One of the more interesting features of ADO is that it will incorporate a cost model for commands. This will let you assess how "expensive" executing a command will be before you execute it. Typically, this cost describes how many reads and writes will take place and gives you a rough estimate of how long the command will run. Unfortunately, although this feature is in the ADO specification, it's not in ADO 1.0.
Nonetheless, expect ADO to be feature complete within 18 months and, when it is, to be a superset of the RDO technology. Should Microsoft succeed in porting COM to other platforms, OLE DB and ADO will follow along. The other data access methods will remain tied to the Windows environment.
What's coming
What's the best choice? If you're accessing remote data sources, DAO is your least attractive choice from a performance point of view, though it has benefits in its support for bound forms and heterogeneous joins. For the shortest learning curve, go to ODBCDirect. If some of the features of RDO are attractive to you (the event-driven model, for instance) and justify the time to become familiar with them, move to it after familiarizing yourself with the licensing issues involved. Do start becoming familiar with ADO and, if you don't need the features available in ODBCDirect or RDO, start using it now.
If you thought that paragraph would solve all of your problems in organizing Microsoft's data access strategy, well, I'm going to disappoint you. Microsoft's wizards involved in creating support for Web-based applications have come up with a different way of looking at data access. They're busy building and distributing a series of Data Source Objects (DSO). Like ADO, these objects have an OLE DB/COM interface and can be accessed from any VBA host using the familiar properties and methods metaphor. A DSO is free to use any method to access data and is completely responsible for all activities performed on the data. However, unlike the general solutions discussed so far, a DSO is written to handle a specific data need.
Two DSOs already exist: the Advanced Data Connector (ADC) and the Tabular Data Control (TDC). The ADC is designed to retrieve data from distant sources over slow links, such as the Internet. To do this, it builds a cooperative link between an object on the client computer and another object on the server. These two objects communicate using MIME-encoded tablegrams, passing data back and forth asynchronously. In addition, the object on the client computer caches the data retrieved from the server. This allows the client program to process the data (including updates) even when the connection to the server is lost. A data control exists to bind data-aware controls to the client-side object. The TDC is a considerably simpler DSO. Its purpose in life is to read comma-delimited files and create a table in HTML.
These two objects -- the advanced ADC and the simplistic TDC -- represent the range that can be expected in DSOs. It's easy to imagine creating DSOs that access only one data source or exist only to perform certain kinds of updates. With that kind of range, it's hard to decide whether Access programmers will find them useful.
However, DSOs might be the birth of a new attitude toward getting at data. In the bad old days, if you wanted to perform some programming task, you wrote all the code yourself. When Visual Basic came along, you stopped doing that. If you needed to perform some difficult task, the smart thing to do was first look to see whether there was a third-party control that would do the job. Once you bought the control, accomplishing the task consisted of using the control's methods and properties. DSOs might do the same thing for data access -- provide pre-built, task-specific objects that work in an object-oriented manner.
As with every other data access method that Microsoft has developed, this new technology will create more options, more solutions, and more confusion. For developers who understand them, these tools will spell improved performance, more efficient code, and job security. But the real challenge for you as a developer is not only using these tools well, but also staying up-to-date with what's going on. Hopefully, this article (and this issue) will help you do that.

We are good at Access and Excel Programming and UpgradesLoad Basecamp Backup XML to Access or SQLServerThe Smart Access PDFs and DownloadsThe Toolbox - Access KBData Mining Tool In AccessThe Access WorkbenchThese are the products and services that we sell