Choosing the Right Data Access Technology

Almost all applications require some form of data access. For standalone desktop applications, local data access is typically easy to implement with little or no programming effort. For enterprise applications, data access is considerably more complex, often involving remote data sources with different data formats and storage mechanisms.

Developers often ask the question, "Which data access technology should I use to build this enterprise application?" To answer this question, developers need to keep two critical points in mind: the importance of code reuse and developers' ability to implement the chosen interface. Often, developers implement an exotic data access solution in a quest for better performance or more control, only to create an application that is an expensive maintenance burden. The newer data access technologies typically reduce development time, simplify code, and yet still provide high performance while exposing all required functionality.

Developers can effectively use virtually all of the Microsoft data access technologies in most situations. Nevertheless, each data access technology has its relative strengths. If their applications require data access, developers will want to understand the unique data access implementation and usage issues specific to each data access method.

When to Use ADO

ADO is Microsoft's premier data access technology. The ADO data access technology and its partner OLE DB comprise the recommended solution for all data access. If their team is developing a new application, developers should definitely use ADO.

If the team is considering migration to ADO, they have to decide if characteristics and benefits of ADO are enough to justify the cost of converting existing software. Older code written in RDO and DAO will not automatically convert to ADO code. However, whatever solutions developers previously developed using other data access strategies can definitely be implemented using ADO. In the long run, ADO should be used.

When to Use RDO

If the team has an RDO application that works well today, there's no reason to change it. If the application needs to be extended to access other kinds of data, consider reengineering to use ADO. As mentioned above, new applications should use ADO.

When to Use ODBCDirect

ODBCDirect is an acceptable choice if the application must run queries or stored procedures against an ODBC relational database, or if our application needs only the specific capabilities of ODBC, such as batch updates or asynchronous queries. However, every feature in ODBCDirect is also available in ADO.

If developers have a working knowledge of ODBCDirect and have large amounts of existing ODBCDirect code, or just need to extend an existing application that already uses it, ODBCDirect will still work for the application. The drawback is that ODBCDirect cannot provide all data access if the application requires other types of non-ODBC data sources. Eventually, the team could take advantage of design, coding, and performance benefits provided by ADO.

When to Use DAO

DAO is the only data access technology that supports 16-bit operations. If the application must run within a 16-bit environment, DAO is the only logical choice.

If the application must access both native Microsoft Jet and ODBC resources, DAO will work; however OLE DB or ADO will provide faster access and require fewer resources.

If the team is experienced in using DAO and has large amounts of existing DAO code, or just needs to extend an existing application that uses a Microsoft Jet database, DAO may still work for the application. Once again, the drawback comes as an application requires other types of data sources, and DAO cannot provide data access. Eventually, the team will want to take advantage of ADO.

When to Use ODBC

Several factors influence choosing the ODBC approach, including a requirement of high performance, more granular control over the interface, and a small footprint.

The ODBC API is considerably harder to code than the object-based interfaces, but provides a finer degree of control over the data source. Unlike other data access technologies (such as ADO, RDO, or ODBCDirect), the ODBC API has not been made "bullet proof." While it's fairly easy to create ODBC errors during development, the ODBC API provides excellent error handling with detailed error messages. In general, developing, debugging, and supporting an ODBC API application requires a tremendous amount of knowledge, experience, and many lines of code. As a general rule, developers prefer to access data by using a simpler, higher-level object interface such as ADO.

ODBC is not suitable for nonrelational data such as ISAM (Indexed Sequential Access Method) data because it has no interfaces for seeking records, setting ranges, or browsing indexes. ODBC simply was not designed to access ISAM data. While developers can use the Microsoft Jet ODBC driver to handle ISAM and the native Microsoft Jet engine data, what is really happening is that the Microsoft Jet database engine converts the ISAM data to relational data and then provides limited ISAM functionality. Performance in this situation is slow due to the extra layer imposed by the Microsoft Jet engine.

If the application requires very fast access to existing ODBC data, and if developers are willing to write many lines of complex code (or already have a log of ODBC code available for reuse), ODBC can be a good choice.