The Ins and Outs of Data Access

Visual Basic (VB) is emerging as a viable option for industrial-strength, database-application development, and SQL Server's increased sophistication offers new interface methods. This growth increases data-access options for programmers using VB as a front end to SQL Server.

About once a year, Microsoft introduces another way of accessing SQL Server data. As customers demand better-performing systems, competitors offer improvements, and Microsoft's Component Object Model/Distributed Component Object Model (COM/DCOM) strategy evolves, the company responds by delivering performance-enhanced options. Microsoft enhances existing technologies and introduces new technology. As a result, programmers can choose from many database-access strategies.

If you use VB as a front end to SQL Server, choosing a data-access method isn't always straightforward. Microsoft offers five primary interfaces, and several of those have various flavors. Here's an overview of the interface options and tips for choosing the best one for your project.

Data Access Object

Before Microsoft released VB 3.0, it provided no data-access solution other than direct file I/O. A few third-party vendors offered proprietary drivers, often implemented as VB custom controls (VBXs) to indexed sequential access method (ISAM) databases. (ISAM databases use a flat file structure, which stores records one after the other, in their entirety. Examples of ISAM databases are dBase and FoxPro.) A couple of vendors offered rudimentary drivers to SQL Server and other relational databases.

In response to demand by the VB community, Microsoft provides easy database access in VB 3.0 by leveraging Joint Engine Technology (Jet), the engine behind Microsoft Access. Because VB and Access use the same underlying technology, Access developers can use VB for database access and vice versa. Jet provides Data Access Object (DAO), an object-based programming model. Jet also offers an API that developers rarely use because DAO is so easy to use.

A convenient Jet feature that's new in SQL Server 7.0 is the ability to do heterogeneous joins—collecting data from tables in disparate databases. Developers commonly construct VB and SQL Server applications to use attached tables, whereby an Access database caches the necessary connection information and Data Definition Language (DDL) for a SQL Server table. When connected to a network, the Access database provides access to SQL Server data. When disconnected, the local Access database caches transactions until the next connection is made.

Appropriately for VB as a graphical development tool, Microsoft provided the data control, a tool that you can drag to a VB form. You can then bind other user controls, such as a grid control, textbox, or label, to the data control. The data control references the database, and the bound control references the data control. After you set a few properties, data appears in the bound control. The data control simplifies basic programming tasks. However, the data control has performance drawbacks, and using DAO without the data control isn't much more difficult.

Microsoft has enhanced DAO with each release of VB, and once or twice in between. DAO 1.1, which shipped with VB 3.0, is obsolete. Available for VB 3.0, DAO 2.0 requires a compatibility layer. If you use VB 3.0 because it has better 16-bit performance than VB 4.0, DAO 2.0 is your best option. DAO 2.5, which shipped with VB 4.0, targets only 16-bit systems. Also shipped with VB 4.0, DAO 3.0 targets 32-bit systems. DAO 3.5, which shipped with VB 5.0 and which Microsoft first implemented in Access 97, includes support for Open Database Connectivity (ODBC).

Although DAO offers the wide variety of programming alternatives that Access developers have, DAO has drawbacks, such as the large amount of system resources it consumes. Also, DAO doesn't support some high-end features of SQL Server. For example, DAO provides no support for server-side cursors, stored-procedure output parameters, or return codes. DAO queries against SQL Server are synchronous—your program's execution stops until SQL Server returns the result—and you cannot stop a query that's in process. In addition, you cannot limit the number of rows returned unless you use a WHERE clause. If a user selects a set of criteria returning 1 million rows from a multitable join, the application appears to freeze, and sometimes the user attempts to fix the problem by rebooting the system. If you give users ad hoc query capability through a form you've designed, which can lead to this type of problem, consider using the English Language Query feature in SQL Server 7.0. Embeddable in any application that supports COM, this feature provides a more natural way for users to specify the information they want and offers a model-building paradigm that makes it easy for developers to include sophisticated search features.

Developers will continue to use DAO for a while because they're comfortable with it. But SQL Server 7.0's shift to the desktop decreases the need for a local Access database.

RDO/RDC

Although Jet provides many features, they come at the expense of memory. When developers complained about the increased size of applications and redistributables, and the workstation memory requirements for their DAO-based applications, Microsoft responded with Remote Data Object (RDO).

First introduced with VB 4.0, RDO is an objectified, thin wrapper of the ODBC APIs. RDO provides the DAO object model without ISAM support. In other words, Microsoft intended RDO, as its name implies, for remote databases—specifically, remote ODBC relational databases such as SQL Server. One reason RDO doesn't consume as much memory as DAO is that it doesn't have a local query processor. RDO relies on the remote database engine for query processing. Because the RDO model is almost identical to the DAO model, you can easily port to RDO applications originally implemented using DAO, if the application isn't using the ISAM features of DAO.

RDO 2.0 is fully asynchronous and event-driven. When an operation completes, RDO fires an event, and your program can do other business while waiting for the event to fire. Because RDO 2.0 is thread-safe, you can use it for multithreaded, headless components executed on a remote server. You can limit the number of returned rows, preset the fatal error threshold, use server-side cursors, and, in the rare case that you need direct access to the ODBC API, expose the underlying ODBC handles.

Microsoft added functionality to RDO 2.0 by supporting the new client-batch cursor library, which yields faster local cursors that you can dissociate from the connection and work on independently. To update the master information, you reassociate the rdoResultset with a connection and issue a batch update method.

To provide the same tradeoffs that the data control tool affords DAO, Microsoft provides the remote data control with RDO, which supports multiple result sets more easily than DAO does.

Microsoft claims that RDO provides performance within 5 percent of that achieved when you program directly to the ODBC API. Empirical evidence from programmers supports that claim. Because of RDO's small memory footprint and excellent performance, developers will likely use it for some time.

The RDO/RDC model exposes the ODBC handles you need for using direct ODBC calls from various data sources. If the interface doesn't support a required feature, you can use the ODBC API to implement it. Microsoft designed the RDO model to support an object hierarchy identical to the hierarchy that the ODBC API uses. This approach gives you more control over the user interface and the back-end interface, and more flexibility. However, this technique can be dangerous.

ODBC API

ODBC is the only fully supported native programmatic interface to SQL Server. But the ODBC API provides the worst risk and reward tradeoff as a method of accessing SQL Server from VB. It requires more code, is easier to misapply, and is more difficult to debug than other access methods. And, if you successfully deal with these issues, your only reward is an application that might run marginally faster.

ODBCDirect

Microsoft developed ODBCDirect for use in Office applications that can't use RDO because of licensing complications. Think of ODBCDirect as Microsoft's way of providing Office developers the advantages of RDO without requiring them to purchase VB, Enterprise Edition. Because ODBCDirect uses RDO, it's a solution for 32-bit systems only.

VB Library for SQL Server

In the beginning, only C programmers had programmatic access to SQL Server. SQL Server's native interface was DB-Library. Microsoft developed VB Library for SQL Server (VBSQL) to provide VB programmers access to SQL Server. VBSQL comprises a VBX (updated to an Object Linking and Embedding custom control—OCX—for VB 4.0 and later) and API declarations. The custom control provides some hooks that developers couldn't create using VB alone. A few versions ago, Microsoft added ODBC as a native interface to SQL Server.

VBSQL, like ODBC, provides an API paradigm. One feature previously unique to VBSQL was asynchronous error and message handling.

Microsoft has enhanced VBSQL with each release of SQL Server except 7.0. So, although you can still use VBSQL to access SQL Server 7.0, you cannot take advantage of any features unique to version 7.0.

At one time, you could argue that VBSQL provided functionality and performance sufficiently superior to other SQL Server access methods to justify VBSQL's relative difficulty in use. But that day is long past. Although programmers no longer use VBSQL for speed, it is a well-documented, well-tested access method. It's been around long enough that users have worked out most of its kinks.

OLE DB

If all these data-access technologies haven't made you dizzy yet, then you won't appreciate OLE DB, the result of Microsoft's vision of a uniform database access method. Probably not an end-all solution (developers and users will always dream up new features), OLE DB is a big step in the right direction.

Leveraging the heterogeneous data-access capabilities of DAO and the standards-based interface ODBC provides, OLE DB is a specification to which you can map any data. This functionality gives you a standard interface for accessing that data.

OLE DB is a low-level interface designed for use by driver vendors and proprietary database vendors who want to expose a data source to ADO-aware applications or by C++ developers who want to develop custom data components. You cannot call OLE DB directly from VB because it isn't COM automation-compatible.

As OLE DB becomes more prevalent, you'll hear the following terms more often (paraphrased from Microsoft literature):

ActiveX Data Object

ActiveX Data Object (ADO) is an object model on OLE DB, and Microsoft intended ADO to end the confusion over which data-access model to select. ADO will do it all.

ADO is to OLE DB as RDO is to ODBC. Whereas RDO is an abstraction that connects to any ODBC provider, ADO is an abstraction that connects to any OLE DB provider. Because ADO is COM-based, you can use it from any COM-compatible application, including VB. VB 6.0 includes ADO version 2.0 and the documentation on how to use it.

How important is ADO to VB developers? Consider that Microsoft has made VB designers, wizards, and even the debuggers ADO-aware. Is ADO a panacea? Not exactly. Besides requiring that you learn another programming model, ADO has the disadvantage of a memory footprint rivaled only by DAO. But Microsoft has explicitly stated that ADO is the future of data access for VB programmers.

Other Considerations

When selecting a database-access technology, first consider whether the target desktop is 16-bit or 32-bit. Because VB 5.0 and later exclusively target 32-bit systems, developers targeting 16-bit systems are constrained to VB 4.0 or VB 3.0, which limits their database-access options.

Also consider scalability. If you intend to use Microsoft Transaction Server (MTS), the data-access interface you choose must be executed on the server and must be capable of acting as a resource manager. This requirement precludes the use of DAO, but you can use the ODBC API, RDO, or ADO.

In addition, consider how you will distribute the application. Dealing with the interdependencies of redistributables is beyond the scope of this article, but support concerns might arise, so keep abreast of these factors and consider them when you select a data- access technology.

Although choosing a data-access method isn't straightforward, the expanded options offer flexibility for VB programmers.