ADO: ActiveX Data Objects

ADO in Context: Universal Data Access

Microsoft’s philosophy behind ADO and a series of related technologies is Universal Data Access ( UDA). UDA isn’t a tangible product or technology, but rather a strategy for attacking the problem of data access, whose goal is efficient and powerful data access, regardless of data source or development language. Moreover, this universal access is meant to eliminate the need to convert existing data from one proprietary format to another.

With this lofty goal in view, Microsoft developed a series of technologies, collectively known as Microsoft Data Access Components ( MDAC), that allow developers to implement UDA. MDAC consists of the following four key pieces:

• ODBC (Open Database Connectivity)

• OLE DB (Object Linking and Embedding Databases)

• ADO (ActiveX Data Objects)

• RDS (Remote Data Service)

These components implement the UDA vision both individually and as a whole. To best understand ADO in context, you should have a basic understanding of each MDAC technology and its relationship to ADO.

ODBC

Open Database Connectivity, or ODBC, provides access to relational databases through a standard API, addressing the problem of native application -- and platform-specific APIs and their lack of cross-application compatibility. ODBC’s industry-standard architecture offers an interface to any Database Management System (DBMS), such as SQL Server or Oracle, that uses the standard ODBC API. The main drawbacks of ODBC are the amount of work required to develop with it and its restriction to SQL-based data sources.

Two COM components (Component Object Model -- see “ADO and COM: Language Independence” later in this chapter) designed to help with ODBC complications are DAO and RDO, described briefly in later sections in this chapter.

Jet/DAO

With the release of Microsoft Access 1.1 in 1993, Microsoft introduced the Jet Database Engine, which worked with Access databases (Microsoft Access Databases, or MDB files), ODBC-supported data sources, and Indexed Sequential Access Method databases (ISAM, which includes Excel, dBase, and a few other databases).

Data Access Objects (DAO) was introduced as a means of interacting with Jet. DAO, through COM, provided an object-oriented interface to Jet and Microsoft Access.

Jet and DAO were successful in their flexibility but added layers to the ODBC API and were therefore more efficient for some databases (Access/MDB and ISAM) than others, including Relational Database Management Systems (RDBMS). DAO is still widely used today, but it is most appropriate for single-user, low-traffic database applications. The problem with DAO, as many soon began to see, was that it was so full-featured that it brought with it a profusion of objects. Figure 1-1 shows the DAO object model.

Figure 1-1. The DAO object model

As you will see later in this chapter and in other chapters, ADO was designed to address this and other problems with DAO.

RDO

Microsoft’s response to the developer’s need for easier access to ODBC data sources came, in 1995, in the form of Remote Data Objects, or RDO. RDO provided more direct, and therefore faster, access to the ODBC API, as well as support for RDBMS sources. With RDO, the emphasis moved from data-access methods designed for ISAM databases toward techniques to provide for stored procedures and the results that they returned. RDO lacked some of the power that DAO offered with Jet (for instance, RDO is not designed to access ISAM sources and does not allow the creation of new databases), but it offered more power for newer, more robust enterprise systems.

The problem with RDO is that it is very different from the DAO architecture, which means two things. First, developers had to learn a new interface, and second, converting an existing DAO application to RDO involved a lot of additional development, because almost every piece of RDO differed from DAO, as you can see by comparing Figure 1-1 and Figure 1-2 (the RDO object model). With the introduction of RDO, developers chose between DAO and RDO instead of moving directly to RDO and abandoning DAO.

Figure 1-2. The RDO object model

ODBCDirect

ODBCDirect was provided as part of a later release of DAO; to save time, it allows developers to work directly with Access sources without using Jet as the go-between. It is similar to DAO’s object model but includes RDO’s direct access to remote data sources.

OLE DB

ODBC provides access only to relational databases. Its successor, Object Linking and Embedding Databases (OLE DB), includes all other data sources. OLE DB is the foundation upon which ADO relies.

OLE DB provides the following features:

• Access to data regardless of its format or location (via COM -- see “ADO and COM: Language Independence” later in this chapter)

• Full access to ODBC data sources and ODBC drivers

• A specification that Microsoft wants to act as a standard throughout the industry

OLE DB comprises four types of components; Figure 1-3 shows their relationships, which are described here:

Data consumer

Any application or tool that accesses data from a data source. While the API calls that are available to access the data in your database are considered data providers, the application that uses that data itself is a data consumer, since it requests the data from the data provider.

Data service provider

The engine that makes OLE DB work; the resource necessary for a data provider to be able to provide data. A data service provider is a modular or add-on component that allows an application to deliver data through OLE DB. Data service providers are usually provided by the vendor for major products such as Oracle, DB2, and Informix. Microsoft promotes the creation of data service providers by either the manufacturer of the data provider or a third-party company.

Business component

A go-between for a data provider and a data consumer. In today’s development environment, it is becoming more and more important not to develop in such a way that every object in your application manipulates your data. With a business component that you call to access your data, which in turn calls your database access component (ADO, RDO, ODBC, OLE DB, or ADO), then you need only modify the code in that business component.

Data provider

A component (application or database engine, for example) that delivers data from a data source (such as a database, spreadsheet, or email message) in a consistent manner.

Figure 1-3. OLE DB component relationships

ODBC, as we have just seen, is an excellent technology for accessing SQL-based data. OLE DB incorporates this proven technology with a particular component that allows OLE DB consumers to communicate directly with ODBC providers. In other words, use OLE DB to access SQL-based data, and you gain the advantage of being able to access both relational and other forms of data with the same code.

As they have done with ODBC, Microsoft is actively encouraging software vendors and tool developers to support the OLE DB standard within their applications and tools. Widespread standardization is an advantage for developers; with OLE DB, we can ensure that our applications become more robust and more powerful as they span the enterprise.

Keep in mind that OLE DB was designed for software vendors who develop data-based applications to expose that data to you, an end-user developer, through a consistent interface. OLE DB is fast, efficient, and powerful. It has everything a developer looks for in a data-access technology. It offers access to any data source known to man (or to Windows, for that matter), and it provides access to these data sources with a consistent interface, regardless of data source. The problem with OLE DB is that, like ODBC, it is inaccessible to Visual Basic and other developers, because it is based on a C-style API. Visual Basic developers, in particular, needed more.

ADO

Enter ActiveX Data Objects (ADO). ADO, an application-level interface to OLE DB, is the latest, greatest piece of Microsoft’s UDA strategy. It combines the best features of its predecessors, DAO and RDO, and adds OLE DB accessibility for VBA programmers. ADO provides a consistent, language-independent means to access data from almost any source, including text-based or other legacy data in relational and nonrelational formats. (You can now see why I needed to explain some of the alphabet soup before getting to ADO itself.)

ADO comprises a collection of object libraries in a new, modular object model: in this new model, many objects can exist independently of the others, as you will see in later chapters of this book. The ADO object model is more flexible than the DAO object model, but it’s similar, so programmers familiar with DAO will feel at home with ADO. ADO is a smaller version of DAO, generalized to allow easy access to any data source, not just Jet databases or ODBC data sources. The ADO object model simplifies data access more than DAO or RDO did by using fewer objects. See Figure 1-1 and also Chapter 2, for more information.

Used with OLE DB, ADO provides fast, simple access to almost any data source. It allows developers to use a single, consistent interface to new and legacy databases and other data sources of all formats, when creating desktop -- or web-based -- applications.

ADO can also use the OLE DB provider for ODBC. Instead of removing the already proven and tested code for ODBC drivers, ADO allows you to use ODBC through the same interface you would for OLE DB. This may be an option when you have code you are migrating from RDO, which already uses ODBC.

ADO breaks the common characteristics of all data sources into easy-to-use components (which we will look at in Chapter 2). Consistency and language-independence are provided, so that developers can worry more about the content and quality of applications, rather than about the techniques used in delivering data or the type of data being used.

What does language-independent development mean? It is quite simple -- one technology, one development interface. You will use the same object, method, and property names with ADO, regardless of the development language that you are using. The difference is almost unnoticeable. Under the covers, ADO, through COM (Component Object Model), worries about the particular language you are developing with, whether it is Visual Basic, Visual C++, or Java. Even scripting languages, such as VBScript and JavaScript in HTML pages are supported. We will look more closely into programming for these different languages in Chapter 3.

With this feature, you might expect that a lot of specific functionality of data sources would be lost. On the contrary, ADO allows the developer to access any data source-specific commands, methods, properties, and utilities that the vendor has made available through OLE DB. And yes, ADO does this in a well-structured, consistent way. Can you possibly ask for more?

As we will see in chapters to come, an application can be designed to access a simple database, such as Access, and with a little bit of additional code, it can later access more intricate databases, such as SQL Server databases, Word documents, or email files. The only real coding necessary involves altering the connection string used in ADO to read the new data source. This powerful technology will help us move into the future as applications begin to grow across enterprises.

RDS

The final piece of data-access technology in this list of the MDAC components is Remote Data Services (RDS). RDS, based on existing Active Data Connector (ADC) technology integrated into ADO, transports ADO objects via proxy between server and client, thus allowing developers to create web-based applications that can access data on the server in new ways. Some of the advantages of RDS are:

• Client-side caching of data results

• Ability to update data from the client

• Support for data-aware ActiveX components and controls

Client-side caching is something that we will all grow to love. With it, clients (end-users) are able to view data from the server without making numerous round trips. For instance, when you are using a search engine on the Internet, such as Yahoo!, you receive a list of links that relate to your search, usually in groups of tens. If you want to see the next ten sites from the resulting search, your browser must make another request to the server. With client-side caching, all of the data is sent to the client, so that the client can browse this data without incurring time delays that are associated with additional requests. This feature reduces local-area network and Internet traffic and allows the end-user to move freely through data without unnecessary pauses and to perform operations on that data, such as sorting and filtering.

With RDS, web pages can now offer the client the ability to interact with and alter data. This data can be sent back to the server after manipulation. At the server, the data can be verified and then returned to the data source. With this technology, your client/server applications can span the Internet (or your intranet). Clients can now invoke server-side automation objects through HTML, meaning that particular business rules (chosen by the developer) can be accessed via the client.

RDS enables three-tier client/server applications, with the model shown in Figure 1-4.

Figure 1-4. The three-tier client/server web-based application model

With automation objects, your application can become an auto-downloaded application. For businesses with a large number of client-side users, you can create, maintain, and update your application on the server alone. When clients run your application, they can use an ActiveX-aware browser (Internet Explorer) to access the application. With auto-download features built into the browser, the client receives an updated version of the application.

RDS also supports data-aware ActiveX controls that can be placed within an HTML page on a client. For instance, if you want to allow the client to view a list of documents that you have stored in your data source on the server, you could link RDS to an ActiveX list box control that is placed in the HTML page and downloaded to the client. The control interacts automatically with RDS, without any additional programming, to download all of the document names.

See Chapter 11, for a more detailed introduction to RDS.

Putting It All Together

With the addition of RDS to its MDAC family of components, Microsoft has integrated several useful existing technologies into the universal data-access strategy: IE data-access technology for data-bound web pages, remote data capability through RDS, and ASP/IIS-related technologies for better access to data services via the Internet. The result allows applications to work with data offline to reduce network traffic, update data on remote clients, and gather data asynchronously for faster response time.

Figure 1-5 shows the relationships and dependencies of the MDAC components.

Figure 1-5. MDAC architecture

As you can see from Figure 1-5, your application can use a number of different Microsoft-supplied technologies to access SQL -- as well as non-SQL and legacy -- data, such as that residing on a mainframe.

Until ADO, we had four choices: DAO, RDO, ODBC, and OLE DB. DAO served its purpose well: it used the power of the underlying ( Jet) database engine to access Microsoft and other ISAM data sources. With RDO, things were even better with its easy-to-use interface to ODBC and ability to access almost any SQL data source. Accessing ODBC directly was always a possibility, but it was questionable whether the overwhelming amount of work was worth the extra speed gained in the process. Finally, OLE DB offered everything under the sun. It offered access to ISAM, SQL, non-SQL, and legacy data. However wonderful OLE DB was, it is considered the most difficult interface with which to develop to access data sources. This is where ADO comes into play. ADO reports directly to OLE DB and no one else, meaning that it provides an interface to the whole complicated mess, about which we need to know little or nothing.

ADO provides a consistent development interface to the wonders of OLE DB, and it does so while being language-independent.