Chart the best data-access course for Visual Basic and SQL Server
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.