Choosing the “Right” Visual Basic Data Access Interface

Microsoft Corporation

April 8, 1997

Introduction

The purpose of this paper is to help customers decide which Visual Basic® data access interface makes sense now and in the future for any given application. This paper is divided into two parts to address these issues separately. It is clear that there are plenty of “right” solutions from which to choose. The real question is, which does the job today and best positions your team and application for what is coming in the future? Developers find it especially difficult to work with “future” solutions that don’t meet today’s real problems. Fortunately, Visual Basic developers do not have to take that course when deciding among today’s solutions. To make a long story short, if you want to skip through the discussion, the following paragraphs summarize the recommendations:

Today, RDO 2.0 is Visual Basic’s flagship interface to relational ODBC data sources just as DAO/Jet is the interface of choice for Jet and ISAM data sources. Both are heavily integrated into Visual Basic, and form the core of Visual Basic’s comprehensive data access strategy. RDO 2.0 is dramatically faster than RDO 1.0 and both RDO and DAO reflect relatively mature technologies. So, if you need to access ODBC data sources, use RDO; if you need to access ISAM or Jet data sources, use DAO/Jet. Keep in mind that RDO is only available with selected Enterprise versions of Visual Studio™ development tools.

Over the next eighteen months, Active Data Objects (ADO) will emerge as another alternative that will, over time, replace the need for the other interfaces. However, coding to RDO today correctly positions your designs when it comes time to transition to ADO, as their architectures are very similar. ADO 1.0 is not included with Visual Basic version 5.0, but Visual Basic is certainly capable of hosting it and this integration will be provided in subsequent releases.

Question: should you attempt to use ADO today? While this is possible with Visual Basic 5.0, it might or might not make sense given ADO’s current level of implementation when compared to RDO 2.0 or DAO 3.5.

• Yes, Microsoft® wants customers to migrate to ADO if and when they feel comfortable doing so.
  
  
• Yes, that means existing interfaces will continue to be supported.
  
  
• Yes, this means that most (if not all) of the existing interfaces will not be further enhanced as Microsoft and their customers move toward ADO.

In the future, all data sources will be accessible via ADO and that future will arrive in stages over the next 18 months.

How We Got to This Point

Over the last few years, Visual Basic’s customers have created a variety of programs and components to build countless real-world business solutions. Over 80 percent of these applications are used to access data—everything from ASCII text files to Structured Query Language (SQL) databases to sophisticated mainframe databases. While the earliest Visual Basic data access applications were simple ASCII-file tools, quite a few Microsoft SQL Server™ front-end applications were written using a little-known interface called VBSQL. However, in the Visual Basic 3.0 timeframe, a significant amount of customer emphasis shifted to ISAM data access—especially where legacy data on remote data sources was involved. In response, Microsoft built the Microsoft Jet Database Engine (or Jet for short) and Data Access Objects (DAO) to let Visual Basic and Microsoft Access developers easily interface to Jet. Over the last two years, Visual Basic and its customer requirements have evolved to need faster access to remote data and better access to ODBC data sources.

When deciding which of the techniques discussed here is the “right” technology, you need to keep two critical points in mind: code re-use and the ability of the developer to implement the chosen interface. Too many times developers implement an exotic solution in a quest for better performance or more control only to create an application too complex to support or too fragile to maintain. Developers and their managers need to watch out for this tendency and capitalize on the work done by the Visual Basic team and the various database interface development teams. These teams have made a concerted effort to simplify and tune the data access interfaces provided in Visual Basic 5.0 to make them faster and safer to implement. Visual Basic 5.0 also includes the entirely new Query Connection designer to shift much of the burden of setting up complex procedure calls to a sharable design-time component builder. This technology alone can reduce code size by 10:1 over hand-coding—while still exposing all of the underlying functionality.

Now that Visual Basic 5.0 has arrived, developers can access any one of at least nine different interfaces when creating data access front-end applications or server-side components. Each of these interfaces was created to address specific customer needs—each one leveraging the strengths of the particular data source and the skills of the developer.

Where Are We Now?

The following table summarizes the interfaces currently available from Visual Basic 5.0. Some of these are simply applications programming interfaces (APIs), but most are COM interfaces—both of which can be invoked from virtually any language.

* ADO does not ship in the Visual Basic 5.0 box. It is available from the net or other sources, as discussed later.

The preceding table lists the programmatic data access interfaces, but many developers also use “bound” controls to eliminate many of the tedious steps required to capture user input and display data extracted from these interfaces. Visual Basic also supports several “Data Source” controls that act as an intermediary between Visual Basic and the data access COM interfaces. Basically, these controls provide a low-code path to basic connectivity at the price of reduced flexibility.

* The ADC does not ship in the Visual Basic 5.0 box. It is available from the net or other sources, as discussed later.

Over the years, ODBC has matured to be the primary means of accessing remote data while Jet has become the native Jet and ISAM interface of choice. While this means there is a degree of overlap, the reasoning behind this approach is clear—to meet customer needs for interfaces that address specific application requirements.

Don’t know what all of these acronyms mean? Not everyone does. The following table summarizes each of these and gives a brief, relatively unbiased view of each of them from the Visual Basic point of view.

Using the API Interfaces

The low-level API interfaces pose a conundrum in that they provide the performance that many developers crave, but at a price many development managers can’t afford. While the VBSQL “API” interface has been VB-ized to make it safer to use, the ODBC API has not, so coding must be done very carefully to avoid field overflows and other traps. The ODBC API also requires manual coding of UNICODE strings and complex array handling when building certain types of cursors and string datatypes. No, the ODBC API or VBSQL don’t provide much more performance than RDO—now that RDO 2.0 has arrived. The RDO interface also exposes all of the underlying handles so that when the need arises to perform some sort of obscure function developers can resort to the APIs to get it done.

ODBC

ODBC is an API interface to the ODBC Driver Manager and the selected underlying ODBC data source driver. This approach is no longer required when developing mainstream Visual Basic front-ends to databases. The only other instance where the ODBC API might seem attractive is when writing multi-threaded applications that need to be managed by MTS.

No, the ODBC API is not particularly well suited to access ISAM data because it has no interfaces for seeking, setting ranges, choosing or navigating indexes, or any of the common techniques used by ISAM developers. It simply was not designed to access ISAM data. Sure, you can use the Jet ODBC driver, but this simply loads the Jet database engine to convert the ISAM data to relational data—and then exposes only part of Jet’s real potential to handle ISAM and native Jet databases. This makes ODBC a burdensome layer for ISAM databases.

ODBC provides API calls that can be used to adapt the application to the specific features of the data source. For example, these APIs can poll the server to determine the types of locking or cursors that can be created. The ODBC API can also be used to access resultsets of virtually any type including complex multiple result set stored procedures and is capable of creating server-side cursors against ODBC drivers that support this feature. This API is considerably harder to code than the object-based interfaces, but provides a finer degree of control over the data source. It has not been made Visual Basic–friendly and requires special Visual Basic declare statements to expose several of the more complex APIs.

VBSQL

VBSQL has been the primary interface for many developers over the years and continues to play a role in older applications—especially 16-bit applications that have not yet been migrated to 32-bit platforms. Question: should one simply move existing 16-bit applications over to the 32-bit version of the VBSQL OCX? Well, that is fairly easy, but not entirely painless as there are a handful of issues to deal with—but most are “search and replace” conversions. Moving to RDO from VBSQL will require some re-engineering as well as the new object-based interfaces do not map 1:1 with VBSQL’s architecture.

Using the Data Source Controls

For the most part, data source controls like the DAO/Jet Data Control or the RDO RemoteData control permit developers to display resultset data quickly and with very little code. However, these controls are not a substitute for sound design. In many cases, using these controls against remote data sources can be problematic if the developer does not keep close control over resources. These controls can consume a number of connections and often depend quite heavily on cursors rather than limited-scope resultsets. However, when working with pictures or other graphics, data source controls are an invaluable aid. In addition, when working with scrolling resultsets they can dramatically reduce the amount of required code and simplify development.

Using Today’s COM Interfaces

The COM interfaces are certainly the currently accepted way to write applications that are “right the first time.” Because Visual Basic is so closely integrated with the architecture of COM components, it makes it easy to build and distribute applications using this technology.

DAO/Jet

DAO is the COM interface to the Jet database engine and with DAO 3.5, an interface to RDO 2.0. Basically, DAO/Jet is primarily designed to access ISAM databases as it supports most common ISAM data access techniques. Therefore DAO/Jet is the solution when it comes to accessing native Jet (.MDB) or ISAM data sources such as Btrieve, FoxPro, Paradox, and dBase.

Using the Jet API from Visual Basic is uncalled for (so to speak) as the DAO interface is far, far easier to use and much better suited for Visual Basic development. However, when working with remote data sources, the Jet database engine consumes more resources than the API interfaces or RDO. While DAO/Jet is capable of accessing ODBC data sources, it has limited stored procedure management and multiple result set functionality. It cannot build queries using server-side cursors or leverage much of the potential of these remote DBMS systems.

ODBCDirect

Originally, DAO was inexorably tied to Jet, but with DAO 3.5, the DAO object interface was separated from Jet and developers were provided with an alternative—to create ODBCDirect workspaces that did not require Jet. This interface is most useful when invoked from Microsoft Office platforms that need the power, speed, and flexibility of RDO, but can’t access RDO because of licensing issues.

RDO

RDO is specifically designed to deal with remote intelligent data sources as opposed to ISAM databases—so it does not support some of the DAO table-based interfaces or DDL. However, while RDO can execute ordinary table-based queries, it is especially adept at building and executing queries against stored procedures and handling all types of result sets—including those generated by multiple result set procedures, those returning output arguments and return status, and those requiring complex input parameters. RDO 2.0 provides an even higher level of control over remote data sources so that the need to resort to the exposed ODBC interface handles is not required—except in the most unusual cases. RDO also includes the ability to create local cursors as well as dissociate resultsets and connections. RDO’s performance is, in most cases, virtually identical to the ODBC API.

RDO has matured to be the single fastest way to build efficient, intelligent, and powerful front-end applications as well as lightweight components to access relational databases. It is every bit as fast as the ODBC API—at least within five percent of its fastest performance—and can radically reduce coding time. Coupled with the Query Connection designer, you can also leverage RDO’s power to expose queries of all kinds—including complex stored procedures as simple methods on the new UserConnection object.

RDO also supports the new client batch cursor library written by the FoxPro® team. This means that you can build faster local cursors and, if necessary, dissociate them from the connection and work on them independently. When it comes time to update the rows, you can re-associate the rdoResultset with a connection and issue a batch update method to post the changes.

RDO is also fully asynchronous and event-driven. Unlike RDO 1.0, you no longer have to poll for operation completion as an event is fired whether or not the operation succeeds. Using this technology, you can leverage the ability of Windows® 95 or Windows NT® to run multiple threads of execution. RDO 2.0 is also thread-safe so it is suitable for use in multi-threaded headless components executed on a remote server.

With the introduction of VB5, a new weapon has been added to the burgeoning arsenal already available to RDO developers. This is the Query Connection designer. It is a design-time wizard-like interface that creates UserConnection class objects. These can be used to gather the properties and options needed to create an RDO connection and one or more associated queries. Once created, UserConnection objects can be shared between applications simply by including the .DSR file. The UserConnection object can then be opened with a single parameterless method call and each of the underlying queries can be invoked as methods—passing parameters to and from the query as arguments to the method. This technology can dramatically reduce the amount of coding needed to call complex stored procedures or user-defined queries. There is even a link to MS Query so queries can be created interactively. In addition, developers can also paste in queries developed with Microsoft Visual Database Tools. The Microsoft Visual Object Manager can also help manage and share the UserConnection objects throughout the team.

RDO should be considered today’s primary means of access when working with SQL Server, Oracle, or any relational database that is exposed with an ODBC driver—regardless of its ODBC compliance level. When you build applications to RDO today, those applications will be easily converted to ADO applications when the time comes.

What’s In the Future?

Developers and architects designing new applications don’t want to get halfway through the implementation phase only to discover that a new Microsoft-promoted technology solves their problems with less code and with better functionality. With this in mind, consider that ADO is certain to play a very important role in future data access strategies. As such ADO should always be considered before committing to any long-term strategy. While ADO is still under development, we know that it will eventually have all of RDO 2.0’s functionality. Much of that work is expected to be done in the next few quarters.

So, if a new design is underway, consider ADO. If its current implementation is adequate, given the state of the software as described below, then use ADO. If, however, it falls short and you can’t wait, use RDO for relational data sources and DAO for ISAM data sources.

The following diagram shows how ADO uses OLE DB, very much as RDO uses ODBC to access its data sources.

Now let’s take a closer look at ADO and how it is implemented.

ADO

ADO responds to the customer desire to have one common yet extensible programming model for accessing data—thus eliminating the need to choose between DAO and RDO and all of the others. ADO is not a specific “implementation” of a data access interface like RDO or DAO, but a data access programming model. An OLE DB implementation of ADO currently ships in IIS. In the future, there will be extensions to ADO to provide specific functionality provided by specific data sources—such as the Jet database engine.

ADO is designed to eventually replace the need for all other interfaces. ADO is not specifically designed for relational or ISAM databases, but as an object interface to any data source—those defined and understood today and those to come in the future. Sure, ADO can access relational databases, or ISAM, or text, or hierarchical, or any type of data source—as long as there is a data access provider for it.

ADO is built around a set of “core” functions that all data sources are expected to implement. To this core, the ADO teams are adding components to access the unique features of specific data sources through general OLE DB service providers. ADO is implemented to consume native OLE DB data sources, including a specific OLE DB provider named “Kagera” which provides access to ODBC drivers. This ADO implementation was first introduced in Internet Information Server (IIS) where it can be used in conjunction with Active Server Pages. It is also shipping (free of charge and freely distributable) in the OLE DB SDK, downloadable from http://www.microsoft.com/msdn/sdk/.

When Should You Use ADO?

Whether or not to use the ADO programming interface is a complex issue. First, ADO is not (currently) shipping with Visual Basic version 5.0. It is included with Visual InterDev™ and Visual C++®. It’s not that Visual Basic can’t use ADO—it can. It’s just that ADO was not far enough along in its design and implementation to make the cutoff date for inclusion in the package. However, ADO will join Visual Basic in a future release or even earlier via Web-based distribution.

Although ADO functionality and performance is destined to overtake DAO and RDO (and VBSQL and ODBC), that degree of implementation is not in today’s ADO. The 1.0 version of ADO only exposes a subset of RDO 2.0 functionality, but it’s on the clear path of future object-based data source interfaces. For example, ADO 1.0 does not (currently) expose any of the following features as implemented in RDO 2.0.

• Events on the Engine, Connection, Resultset, and Column objects
  
  
• Asynchronous operations
  
  
• Queries as methods
  
  
• Enhanced batch-mode error and contingency handling
  
  
• Tight integration with Visual Basic—as in the Query Connection designer and TSQL debugger

However, ADO 2.0 will eventually provide a superset of RDO 2.0 functionality and provide a far more sophisticated interface—in addition to an easier programming model. Keep in mind that programming directly to OLE DB is not an option for Visual Basic developers.

While IIS is ADO’s first home, this isn’t its only intended residence. ADO is designed to be a general-purpose object model for all types of data access programming. For Visual Basic developers, ADO is Microsoft’s strategic data access model—eventually replacing all of the other interfaces.

The Visual Basic team is working with the teams producing all of the new data access technologies such as ADO, ADC, and OLE DB. Much of this work involves ensuring that these technologies meet existing, evolving customer needs, and the high bar for functionality and performance we've set with RDO 2.0. As these new data technologies solidify plans and schedules, the Visual Basic 5.0 team will consider which (single or multiple) solutions best meet the needs of the VB developer and their customers.

Since ADO is a COM-based component, any application or language capable of working with COM objects can use it—including Visual Basic. At this time there is no licensing restriction for ADO so it can be used as appropriate. In any case, if you are starting from scratch, it might make abundant sense to use ADO if it does the job. It will be simpler to convert from ADO 1.0 to ADO 2.0 when it arrives than to write to RDO 2.0 now and convert to ADO 2.0 later.

OLE DB

ODBC is to heterogeneous access to relational data what OLE DB is to heterogeneous access to heterogeneous data. That is, OLE DB is designed to provide a heterogeneous interface to an infinite variety of data sources—not just ISAM or relational DBMSs like DAO or ODBC. ODBC relies on the SQL language to define its functionality and to implement access to the underlying data source. OLE DB relies on an object model to represent data in general. The problem for Visual Basic developers is that OLE DB is inaccessible from Visual Basic. ADO deals with this by exposing a COM-based “dual-interface”. This allows ADO to expose OLE DB from all languages and from scripting languages like Visual Basic.

What are Active Server Pages?

Active Server Pages is a server-side extension for the Microsoft Internet Information Server (IIS), which lets developers of Web-based applications add server-side scripts written in VBScript or other scripting languages to dramatically extend the capabilities of your Web-based applications. These high-level language scripts are run on the server so they are not dependent on any client-side operating system or browser. For example, using a VBScript, you can enable database row-by-row scrolling, editing, and deleting using ADO. This gives the user much greater functionality and control, which is potentially very useful for certain kinds of database applications. Denali also supports other thread-safe (stateless) interfaces like RDO.

Microsoft Access 97 provides a Publish to the Web wizard which creates three kinds of output files: static HTML, HTX/IDC (Internet Database Connector) files, and Active Server Pages (ASP) which connect Web browsers to databases through IIS.

What Else Should Be Considered?

There are a plethora of considerations that need to be taken into account when choosing which interface you should use. Let’s outline some of these:

• Is there an ODBC driver for your DBMS? Visual Basic version 5.0 includes drivers for SQL Server and Oracle as well as a limited-functionality driver for Jet databases. As a note for Oracle developers, the Visual Basic 5.0 version of the Oracle ODBC driver was recently tuned to work optimally with Microsoft languages and COM interfaces like RDO. Since the ODBC driver plays such an important role in these interfaces, it is essential that the best driver possible be obtained and rigorously tested before a commitment is made to this strategy. Also, be sure to research other sites that have used the driver in the way that you intend to use it.
  
  
• Does the application have to function on both 16- and 32-bit platforms? While the world is evolving its desktops to 32-bit operating systems, not all systems are there yet, so for the time being, it is still necessary to consider legacy systems in your design. Because of the features and performance of the new 32-bit (only) tools, it might make more sense to consider a fundamental redesign of your application instead of trying to make a single application do double duty. You should also consider that by implementing a three-tiered remote component design you might be able to continue using 16-bit OS platforms for a while longer as the new 32-bit components can be accessed from 16-bit clients.
  
  
• Are you planning to use Microsoft Transaction Server (MTS) to manage remote Visual Basic components running on the server? If this is the case, you need to choose one of the data access interfaces that can be executed on the server and can act as a “resource manager” (an MTS term for a component that implements their set of resource manager interfaces). The ODBC API can be used as a resource manager and thus RDO, with a few additional ODBC API settings, can as well. The DAO/Jet interface is not capable of being a resource manager at this time. You should also consider if the component must be thread-safe. This is a requirement for most MTS-managed components if you expect reasonable performance and resource utilization. When using MTS, you want an interface that is at least apartment-model multi-thread-safe. This applies to the component and the underlying ODBC layers including its net interface.
  
  
• Is this a new design or a re-fit of an existing application? For example, while it might be tempting to port a DAO/Jet application to DAO/ODBCDirect, developers might find that simply porting an ISAM design to a relational platform is counter-productive. ISAM methodologies are very different than traditional client/server designs. They depend on access to base tables and rely heavily on cursors and direct index access to chosen rows. This strategy is often frustrated on heavily used and secure relational systems as the administrators often do not expose the base tables—at least not directly. Yes, you can use many (but not all) of the DAO object interfaces to access RDO. However, you must leave behind perhaps the most powerful (and popular) access method—the DAO Table object and its Seek method. When using ODBCDirect, the approaches used in existing DAO/Jet applications to access remote databases must also be redesigned or abandoned. For example, you can no longer use attached tables, heterogeneous joins, or SQL passthrough—with or without pre-built TableDef objects.
  
  
• What are the developers trained to use? Do they have experience with DAO, xBase, AS400, or the ODBC API? Are they experienced C++ developers or simply Office macro builders? Are they familiar with single, two-, or three-tiered DBMS design and implementation regimens?
  
  
• How many users are expected to access the database? Is the LAN capable of supporting the expected load using the chosen interface? Yes, some interfaces are far more consumptive of LAN resources than others. Again, you need to test the interface under live load conditions. It might be your application that drives the net over the edge.
  
  
• How many licenses or users does your server support? If you are limited to a fixed number for some reason, it might be wise to choose an interface that neither over-consumes connection resources nor holds on to them once they are no longer needed. Using a correct design it is entirely possible to leverage a limited number of connections so that hundreds or thousands of users can (legally) gain access to your server.
  
  
• Where are the users? Are they all within shouting distance or spread all over the globe? Some interfaces require that all users be disconnected before the database can be repaired, backed up, or compressed. Some databases require regular, manual maintenance while others do not. While the DBMSs that need less maintenance or can perform it automatically and without user intervention are more expensive, if you consider down time and lost productivity in the equation, this extra cost can be easily amortized.
  
  
• Are you using the “right” DBMS? Have you chosen a database system capable of handling the number of intended users? What about administration, backup, tuning, and support? What about connection management? What about support by the DBMS vendor or third-party tools vendors and languages? What about interactive debug support? What about support by the developer community as a whole? If you need help, does anyone else know how to help you? 
  
  
• Where is the data? Is it on the Web, on a remote server, or simply stored locally on the user’s system? If the data is simply stored on the user’s system, the need to build a separate server to manage it might be considered overkill. If the data is remote, what about the means to access it? Will these links always be available—when you expect to need the data? What about times when you don’t expect to need data access, but might need to be able to get in?
  
  
• What about connection management? What about driver interaction? What happens when the driver cannot connect? Does it throw up a dialog that can’t be trapped? Can it be expected to run on a remote server that has no one to see the error dialog? Can it run in situations where there is no local database engine? Can it be run from Visual Basic script?