Hitchhiker's Guide to Visual Basic® and SQL Server™, Sixth Edition

Author William R. Vaughn Pages 1024 Disk 1 Companion CD(s) Level Int/Adv Published 09/09/1998 ISBN 9781572318489 ISBN-10 1-57231-848-1 Price(USD) $49.99 To see this book's discounted price, select a reseller below.	More Information About the Book Table of Contents Sample Chapter Related Series Related Books About the Author Support: Book & CD Rate this book

Hitchhiker's Guide to Visual Basic and SQL Server, 6th edition

Chapter 28: Understanding ADO and OLE DB

• Your First Peek at OLE DB
  • The "Universal" Solution
  • OLE DB as Seen from 10,000 Feet
  • OLE DB and ODBC Data Sources
  • OLE DB and SQL Server
• Architectures That Use OLE DB
  • Traditional client/server architecture
  • Web-based architecture
• A First Peek at ADO
• Introducing ADO 2.0
  • The ADO Object Model
  • The ADO Objects
    • Connection object
    • Command object
    • Parameter object
    • Recordset object
    • Field object
    • Property object
    • Error object
  • The ADO Object Model from an RDO Perspective

Over the last six years or so, Microsoft has been driven by customer demand to release an ever-expanding series of data access interfaces for Microsoft Visual Basic. Beginning with the Visual Basic Library for Microsoft SQL Server (VBSQL) and ending with the most recent interface to OLE DB, ActiveX Data Objects (ADO), a new data access interface seems to evolve every couple of years. As you've seen in earlier chapters of this guide, each of these interfaces addresses broadening customer requirements to access an ever-increasing number of data sources. As Visual Basic applications have grown more sophisticated and data-centric, so have the data access interfaces. But now, we're faced with a dizzying variety of data sources. Choosing the right one is harder than picking out just the right gift after having overlooked your thirtieth wedding anniversary. Been there, done that. No thanks.

Your First Peek at OLE DB

It's not unusual these days to find businesses that want to access databases, mail, directory, telephony, exotic, legacy data, or Internet content—all from the same application or system. The problem is that each of these data sources has its own proprietary or merely different data access interface—if an interface exists at all. Some require API access, others can be accessed by using one of the more familiar data access paradigms. This cornucopia of data sources means that the skills you've learned to access one type of data don't always apply to other types. It also means that once the data arrives it must be morphed into some common format, or your applications have to adapt themselves to the different structures and data access interface requirements. Figure 28-1 shows an application that needs to draw from a wide variety of data sources—no two of which use the same data access interface.

In this figure, notice that the application uses at least five separate APIs to access the different types of data. Some of the interfaces are Component Object Model (COM) interfaces, and some are not; some expose Automation (formerly known as OLE Automation), interfaces; and some are procedural. In addition, transactional control doesn't exist, so I can't safely make any changes to all the information within one transaction.

Click to view graphic (6 KB)

Figure 28-1
An application drawing from diverse data sources

What all of this means is that the developers who write the application will have to become experts in a multitude of data access methods. Is there a better way? Well, one solution is to put all the different types of data into a single relational data store. That is, take the relational data, the ISAM data, e-mail data, directory data, and any other information, including that from the Internet, and move it into a single relational database. This approach solves the problem of having a single API or interface that also gives you transactional control, but it has a number of problems.

First, this all-in-one approach requires moving huge amounts of data out of the original source(s). Second, you typically don't own much of the data you need, such as the wealth of information on the Internet or the data from that rogue accounting department upstairs. Sure, you could get a copy of it today—but that information would soon be stale and have to be refreshed, and you would have to repeat the download or at least keep a replication engine running full time. The third problem with moving the data into a single data store is that tools today don't look for or store data into a single relational data store. E-mail applications, word processors, project management tools, spreadsheets, Web sites, and other tools expect to access their own data stores for their data. You would have to rewrite your tools to access the data in the single data store or duplicate the data to the single data store from where it naturally lives—then you still run into problems with synchronizing the data. A fourth problem is that one size does not fit all. The way that relational databases deal with data is not the best way to deal with all types of data. Data (structured, semistructured, and unstructured) is stored in different containers based on how the data is used, viewed, managed, and accessed. It's like trying to store earth, wind, fire, and love in plastic buckets. Finally, the performance and query requirements are going to differ by data type, depending on what it is you're trying to do. Ah, no. This approach is not your best solution. Go back three squares, and start over. Of course, those of you out there who have tried this approach (at least to some extent) know what I'm talking about.

The "Universal" Solution

In fall 1996, Microsoft introduced the inaugural version of its answer to this complex problem. The strategy has since been dubbed Universal Data Access. In theory, this strategy provides access to all types of data through a single data access model—with OLE DB and ADO at the center. With Universal Data Access, you can access data in different data stores through a common set of interfaces, regardless of where the data resides or what form it takes. No, at this point, an OLE DB data provider for earth, wind, fire, or love is not available—but they're working on it.

Implementing this architecture, your Universal Data Access application speaks to a common set of interfaces that generalize the concept of data. Microsoft examined the common characteristics of all types of data, such as how you navigate, represent, bind, use, filter, sort, and share that data with other components. The result is a set of interfaces that developers can use to represent data kept in relational databases, word processors, spreadsheets, and applications of all kinds, and in project and document containers, ISAM, email, and file systems—virtually all forms of information. The data stores simply expose common interfaces—a common data access model—to the data. With this Universal Data Access strategy in place, our application now looks like the model shown in Figure 28-2.

Click to view graphic (6 KB)

Figure 28-2
An application using Universal Data Access

If you want to find out more about the Universal Data Access paradigm, see /data/.

OLE DB as Seen from 10,000 Feet

ADO is the newest data access interface—it is the "Automation server," or object-level interface, to OLE DB, which is a new low-level object-based interface. OLE DB is included in the new bundle of tools dubbed the Microsoft Data Access Components (MDAC) data access paradigm. Sound familiar? Yes, these are almost the same terms that were used to describe Microsoft's Open Database Connectivity (ODBC) some years back. However, OLE DB's architecture is not restricted to ISAM, Microsoft Jet, or even relational data sources, but is designed to interface with virtually any type of data—information—regardless of its format or storage method. OLE DB doesn't really do anything—it defines what its service components and data providers must do and provides standard ways to expose that functionality to developers.