ADO: ActiveX Data Objects | O'Reilly Media

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In today's computing environments, data exists in many formats, ranging from Access and SQL Server databases to Word documents, email messages, and many others. ADO, or ActiveX Data Objects, data-access technology simplifies use of data from multiple sources, thus freeing developers from learning data, vendor-specific API calls, and any other coding minutiae for each data format involved. With ADO, almost any data source becomes accessible in a consistent way for developers creating standalone applications, client/server applications, or ASP pages.

In this chapter, I define ADO in the historic and current context of Microsoft's overall data-access strategy and related technologies.

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.

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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

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.

Section 1.1.1.1: 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).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Microsoft's Component Object Model, better known as COM, is a mature technology that offers universal access to components, regardless of the language in which they were programmed. This is the backbone that allows ADO, through OLE DB, to be so versatile. To understand how COM allows ADO to be language-independent, you must first understand what COM is and what it achieves.

COM is technology specification for writing software components that interact through a standard interface. The COM specification is strictly a binary specification. This guarantees that the language in which a COM object is developed has absolutely no importance once the object is compiled, as long as its adheres to the binary specification.

The COM specification sets rules for creating and managing component objects. This specification guarantees that all COM objects are compatible and that they expose a minimal set of interfaces. These interfaces allow COM objects to communicate with each other whether they are on the same machine or supported by networks. Since the COM specification relies on binary compatibility, COM works across heterogeneous networks. In other words, COM objects can run on any machine, even without the Windows operating system.

A particular type of COM implementation is OLE Automation, or simply Automation. Automation is a standard way for COM objects to expose their functionality to software products, development languages, and even scripting languages. The use of Automation allows applications to actually manipulate other applications through the exposed features and functionality of the latter's COM objects. Automation allows two applications to communicate with each other.

An example of this type of manipulation is a Visual Basic add-in. Visual Basic exposes an object model through the COM technology to any other component that wishes to interact with it. You can create an add-in for Visual Basic that works seamlessly with the product, through the use of Visual Basic's exposed features. As a matter of fact, many of Microsoft's products expose their features through COM, including the Microsoft Office family of products. Microsoft Word, for example, exposes its functionality through COM and allows itself to be manipulated through scripting with VBA (Visual Basic for Applications).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

ADO is language-independent, as discussed earlier. This means that no matter which language you are developing with -- Visual Basic, VBScript, Visual Basic for Applications (VBA), Visual C++, Visual J++, or JavaScript -- the development interface is identical. This allows developers to become familiar with the technology itself, instead of worrying about learning a half-dozen different programming syntaxes for that technology. I suggest that you use ADO whenever your application fits into any or all of the following categories:

Your application accesses or may later need to access more than one data source.
Your application accesses or may later need to access data sources other than ISAM or ODBC databases.
Your application spans or may later span a heterogeneous network.
Your application uses or may later use multiple languages.

If your application needs to access more than one type of data source, then you should consider integrating ADO technology into your application. For instance, if you were designing an application that had to search Word documents, email messages, and a SQL Server database for keywords and then to show related information based on that query, ADO is the best choice. With ADO, you can create a component to search all three of these data sources using identical code, saving you time in development, as well as in maintenance and upkeep. This choice also provides the option of adding a fourth data source to your application at some later time with little or no additional overhead in development.

If your application may access data sources other than conventional ISAM or ODBC databases, you should use ADO. With ADO, you can search through an Excel worksheet just as if you were searching through email messages. If you use some other technology besides ADO, you must not only code two different components, one for each data source, but you also need to learn that other technology. In this case, you would have to research MAPI API calls, as well as Word document file structures. And then what happens when Word comes out with a new version? Or what about when more APIs are added to MAPI? You could easily ignore these until your application becomes so outdated that it renders itself useless. With ADO, you simply use the data service providers supplied by Microsoft for both Excel and Word so that the ability to access and manipulate these data sources are exposed identically through ADO.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter introduced ActiveX Data Objects, along with the closely related evolution of Microsoft data-access technologies. You also learned when to use ADO, the newest of these technologies. Following is a list of some key items, pointed out in this chapter:

ADO offers access to virtually any data source on any platform by being a data consumer of OLE DB. OLE DB is an industry standard promoted by Microsoft for exposing data, regardless of its source or format, in a uniform way. With the power of OLE DB, used via ADO, you gain access to any data source that provides an OLE DB interface.
ADO offers ease of use when writing data access applications. Since ADO was created with a similar design to DAO (Data Access Objects), developers are familiar with the object architecture. And since the development interface is consistent, you can develop for any OLE DB data source with ADO using the same syntax.
ADO offers language-independence and thus offers developers a choice of languages. With any language, including Visual Basic, VBScript, VBA, Visual C++, Java, and JavaScript, the development interface remains the same, which allows developers to focus on the ADO technology, not the implementation.

Throughout the rest of this book, you will learn how to use ADO with any development language. You will learn every object, collection, property, and method of ADO and how you can use each of them to access the power of OLE DB in your applications.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In this chapter, we take a look at the ADO architecture; in the first section, "An Overview of the ADO Architecture," I describe how all of the pieces of ADO fit together to perform all of the functions that are necessary when accessing data sources. The remainder of the chapter is dedicated to the introduction and brief description of each of the key components of the ADO architecture.

ADO is built upon layer after layer of solid, proven technologies that allow applications to communicate with data, regardless of where it resides or how it is structured, using any language or scripting language. How can one technology offer techniques to access both relational databases and nonrelational sources such as email?

ADO is the lowest common denominator when it comes to data access. It makes no assumptions when it comes to its data sources. Because ADO cannot assume that the data source being accessed is even a database, it must use objects, methods, and properties that are relevant to all data sources.

With ADO, the data provider (as described in the previous chapter, the connection between the data consumer, or application), not the data consumer, creates the driver for a data source. What this means is that the version of ADO does not dictate the data sources that are available to us; rather, it dictates the functionality that is passed through from the data provider to our software. The burden is on the data provider or vendor to create and distribute the proper resources necessary to develop with their product. ADO is a framework; the behavior of the OLE DB providers can vary widely. ADO does not require that all interfaces and functionality be offered by each provider.

By designing the architecture of ADO as a simple generic interface, ADO is not tied to a specific command type, but is capable of growing with the needs and the abilities of both developers and data sources.

A powerful feature of ADO is its ability to offer the functionality of a particular data source. If your data provider supports stored procedures, for example, then you can use them. In Chapter 4, we take a look at a number of popular providers and their specific functionality.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

ADO is built upon layer after layer of solid, proven technologies that allow applications to communicate with data, regardless of where it resides or how it is structured, using any language or scripting language. How can one technology offer techniques to access both relational databases and nonrelational sources such as email?

ADO is the lowest common denominator when it comes to data access. It makes no assumptions when it comes to its data sources. Because ADO cannot assume that the data source being accessed is even a database, it must use objects, methods, and properties that are relevant to all data sources.

With ADO, the data provider (as described in the previous chapter, the connection between the data consumer, or application), not the data consumer, creates the driver for a data source. What this means is that the version of ADO does not dictate the data sources that are available to us; rather, it dictates the functionality that is passed through from the data provider to our software. The burden is on the data provider or vendor to create and distribute the proper resources necessary to develop with their product. ADO is a framework; the behavior of the OLE DB providers can vary widely. ADO does not require that all interfaces and functionality be offered by each provider.

By designing the architecture of ADO as a simple generic interface, ADO is not tied to a specific command type, but is capable of growing with the needs and the abilities of both developers and data sources.

A powerful feature of ADO is its ability to offer the functionality of a particular data source. If your data provider supports stored procedures, for example, then you can use them. In Chapter 4, we take a look at a number of popular providers and their specific functionality.

ADO has already proven to be a very well-thought-out interface for data access, which is worth its weight in gold because it is so very robust and scalable, in addition to being so easy to use.

In the second half of this chapter, I will take a closer look at how each of the major components of the ADO architecture fit together to achieve its desired goal of a generic data-access interface.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

ActiveX Data Objects consists of a generic-style data-access structure that allows you to access any data source, regardless of its structure, with the same programming interface. The individual objects within the ADO object model are used to provide all of the data-storage, manipulation, and retrieval commands needed when writing a data-based application. ADO includes the following objects and collections:

The Connection object
The Command object
The Parameters collection and the Parameter object
The Recordset object
The Fields collection and the Field object
The Record and Stream objects
The Properties collection and the Property object
The Errors collection and the Error object

In the next sections, I take a closer look at these objects and collections.

The Connection object is the gateway for all data communications through ActiveX Data Objects. Figure 2-1 illustrates the Connection object's object model.

Figure 2-1: The Connection object's object model

In order to access data from any source, a connection for that source must first be established. ADO uses the Connection object to accomplish this. The Connection object uses information that you provide to establish a unique connection to a particular OLE DB data source. The standard information that a Connection object accepts includes filenames, data-provider names, usernames, and passwords. If your particular data provider needs additional information, this information can be passed from the Connection object directly to your data provider. By allowing this form of pass-through of connection specifications, ADO does not make any assumptions or restrict itself to one type of data source. All of the functionality of the chosen data provider is made available through the use of the Connection object.

A Connection object is used to accomplish the following tasks:

Select a data source and data provider
Open and close a connection on a selected data source
Manage transactions on a data source
Execute queries on a data source

Connection objects can be created explicitly and used later with the Command and Recordset objects, or the Connection object can be created by the Command and Recordset objects implicitly, behind the scenes.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter has explained the architecture behind the ActiveX Data Objects technology. The following is a list of some key items pointed out in this chapter:

ADO offers a generic data-access interface that is used to communicate with a wide range of proprietary data sources and providers.
With ADO, the burden of creating efficient data access is placed upon the individual data provider, not the data-access technology.
The ADO architecture is comprised of nine major components. These components include the Connection object, the Command object, the Parameters collection and the Parameter object, the Recordset object, the Fields collection and Field object, the Record object, the Stream object, the Properties collection and the Property object, and finally, the Errors collection and the Error object.

The rest of this book walks you through the nitty gritty of application development using the ActiveX Data Objects technology. You will next learn how to access ADO through various different development languages, and then we will dive into the actual components of ADO.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Because ActiveX Data Objects expose their properties by means of COM interfaces, they can be accessed by any language that can utilize COM. In this book, we will look at accessing ADO from Visual Basic, Visual C++, and Visual J++, since these are the most commonly used tools for developing ADO applications on the Windows operating system.

In addition to these three languages, there are two scripting languages that are already well-established: VBScript and JScript. VBScript is a lightweight subset of Visual Basic that's designed specifically for adding script to HTML documents. JScript is Microsoft's implementation of JavaScript, designed for script development within HTML documents.

Although ADO is meant to offer the same development interface to each language from which it is accessed, some inconsistencies arise because of differences in their syntax and the development environments in which they are used. In this chapter, we will take a look at each of the five languages and learn how to get started developing ADO applications in each.

Visual Basic is probably the most popular language in which to develop applications for ADO. It is also the language used in the examples and code throughout this book. Visual Basic is a very easy language to understand and excellent for both beginners and advanced developers.

To write an application in Visual Basic using ActiveX Data Objects, you must first tell Visual Basic about them by adding ADO to the list of references that Visual Basic uses to run an application. You may do this by selecting the Project → References menu item so that the References dialog box appears, as shown in Figure 3-1. In the Available References list box, select the latest version of Microsoft ActiveX Data Objects Library that you have installed. Now you are ready to create and use ADO objects within your current Visual Basic application.

Figure 3-1: The References dialog box of Visual Basic

When redistributing ADO applications, you should use the MDAC redistributable package available for download from Microsoft's web site.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Visual Basic is probably the most popular language in which to develop applications for ADO. It is also the language used in the examples and code throughout this book. Visual Basic is a very easy language to understand and excellent for both beginners and advanced developers.

To write an application in Visual Basic using ActiveX Data Objects, you must first tell Visual Basic about them by adding ADO to the list of references that Visual Basic uses to run an application. You may do this by selecting the Project → References menu item so that the References dialog box appears, as shown in Figure 3-1. In the Available References list box, select the latest version of Microsoft ActiveX Data Objects Library that you have installed. Now you are ready to create and use ADO objects within your current Visual Basic application.

Figure 3-1: The References dialog box of Visual Basic

When redistributing ADO applications, you should use the MDAC redistributable package available for download from Microsoft's web site.

In Visual Basic, you can create new ADO objects by simply referencing the ADODB classes of the Microsoft ActiveX Data Objects Library. The following piece of code creates a Connection and a Recordset object in Visual Basic:

' create a reference to a Connection object
Dim con As ADODB.Connection

' create a reference to a Recordset object
Dim rst AS ADODB.Recordset

As with any other Visual Basic objects, you must instantiate them before they can be used, as in the following examples:

' create a new instance of the Connection object
Set con = New ADODB.Connection

' create a new instance of the Recordset object
Set rst = New ADODB.Recordset

In the previous examples, the ADODB prefix to the ADO objects is used in case your Visual Basic development environment references another object of the same class name in a different class library. The following code illustrates how a DAO Recordset and an ADO Recordset can be created within the same project:

' which object model is this from?
Dim rst As Recordset

' explicitly specifying the Data Access Object Model
Dim rstDAO As DAO.Recordset

' explicitly specifying the ActiveX Data Object Model
Dim rstADO As ADODB.Recordset

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Visual C++ is a much more difficult language and environment with which to develop applications for ActiveX Data Objects. Because it is so difficult, Microsoft is constantly trying to provide developers with easier ways to access ADO components.

By far the easiest method (and the only method described here) is one that takes advantage of the #import keyword. This approach offers not only the most control to the developer, but it also allows the developer to code in a Visual Basic programming style.

The #import keyword is used in Visual C++ applications to import information from a type library. To make ADO.ACcessible to your C++ code, use the following #import directive:

#import <msado15.dll> no_namespace rename("EOF", "EOFile")

This statement assumes that the path to msado15.dll (usually C:\Program Files\Common Files\System\ADO) is already set within the Visual C++ environment; if not, select the Directories tab of the Options dialog box (Tools → Options), and add it.

The #import statement does a couple of things. First, at compile time it creates a header file with a .tlh extension, which stands for Type Library Header. This header file is comprised of enumerated types and definitions for the objects contained in the type library for msado15.dll.

Secondly, it creates a file with a .tli (Type Library Implementation) extension that contains the wrappers for each function in the object model defined by the msado15.dll type library.

Finally, the rename attribute in the statement:

rename("EOF", "EOFile")

renames the EOF keyword from the type library and calls it EOFile so that it does not conflict with Visual C++'s definition of the EOF property.

In order to invoke an ActiveX Data Object, we must first start OLE so that we can use OLE DB. Remember that Chapter 2, showed that ADO was simply a wrapper around the OLE DB technology. We do this with the following piece of code:

struct StartOLEProcess{
    StartOLEProcess(  ) {
        ::CoInitialize(NULL);
    }
    ~StartOLEProcess(  ) {
        ::CoUninitialize(  );
    }
} _start_StartOLEProcess;

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Like Visual C++, Visual J++ offers a number of ways to access ActiveX Data Objects. By far the easiest and most powerful is to use the Windows Foundation Classes, which expose the ADO objects and their members.

To use the ActiveX Data Objects within your Visual J++ application through the WFC, you must import the type library with the following statement:

import com.ms.wfc.data.*;

In order to create an ActiveX Data Object in Visual J++, you must first create a variable to reference that object, as follows:

// define a variable which will be used as a reference to the
// Connection object
Connection  con;

// define a variable which will be used as a reference to the
// Recordset object
Recordset   rst;

Next, you can create a new instance of an ActiveX Data Object by using the new keyword and assigning it to the variable reference you just defined:

' create a new instance of an ADO Connection object
con = new Connection(  );

' create a new instance of an ADO Recordset object
rst = new Recordset(  );

These last two steps could be combined into one step with the following code (this is one of the beauties of Java):

// define a variable which will be used as a reference to the
// Connection object and create a new instance for that variable
Connection  con = new Connection(  );

// define a variable which will be used as a reference to the
// Recordset object and create a new instance for that variable
Recordset   rst = new Recordset(  );

As in any language, it is always a good idea to remove instances of objects that are no longer being used. You can do this in Java with the following lines of code:

' remove the objects 
con = null;
rst = null;

Example 3-3 illustrates how an ActiveX Data Objects application may be written for the Visual J++ development environment. To create this project, open a Visual J++ Console Application project, and simply replace the code within the Class1.java file with the code from Example 3-3.

If you are having difficulty running this example, remember to have the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

ActiveX Data Objects can be accessed from within server-side scripts via Active Server Pages, better known as ASP (which in this case does not stand for Application Service Provider). Although this book does not go into ASP in detail, a brief explanation of the technology is needed to understand how to develop VBScript code that uses ActiveX Data Objects.

When a client requests an ASP (Active Server Page) from a server, the ASP is "executed" before it is sent to the calling client. If there are any scripts embedded within the Active Server Page, they are executed. The result of this execution of different scripts is a static HTML page that can be viewed by virtually any web browser.

Active X Data Objects therefore can be embedded within a server-side script in order to gather and display information for the client in a low-resource-intensive manner. Because the ADO code is run on a server, the HTML page contains only the result, not the code. Once the page has been dynamically created by the server, it is passed back to the client for static reading. Because the web server does not pass actual recordsets, or rows of data, the potential savings in bandwidth can be considerable.

In order to use ActiveX Data Objects from within your server-side scripts, your server must be running IIS (Internet Information Server) Version 3.0 or better. Along with IIS, you must of course have installed ADO, which is part of the MDAC installation. MDAC and IIS are included as part of the Windows 2000 operating system.

Also, in order to use ADO constants, you should copy the file adovbs.inc to the directory in which your HTML pages that use ADO reside. You can reference the adovbs.inc file by adding the following line of code to your HTML source:

<!--#include file="adovbs.inc"-->

In VBScript, the Variant is the only datatype. This type can represent just about any type of information that you could possibly want it to. Although in Visual Basic developers usually try to avoid using the Variant datatype at all costs, it is a necessary component of almost any VBScript code.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The JScript implementation of ActiveX Data Objects is almost identical to that of VBScript. The only difference is in the syntax. JScript server-side scripts are used within Active Server Pages and (with the help of Internet Information Server) are issued to a client web browser.

Once difference between the VBScript and JScript implementations of ADO is the name of the include file for ActiveX Data Objects. In JScript, the filename is adojavas.inc. To add it to an Active Server Page, type the following line:

<!--#include file="adojavas.inc"-->

The first thing you need to do is create the variables that will hold your objects:

// define a variable which will be used as a reference to the
// Connection object
var con;

// define a variable which will be used as a reference to the
// Recordset object
var rec;

Once you have the variable references, you can create ActiveX Data Objects with the CreateObject function of the Server object just as in VBScript:

' create a new instance of an ADO Connection object
con = Server.CreateObject("ADODB.Connection");

' create a new instance of an ADO Recordset object
rst = Server.CreateObject("ADODB.Recordset");

Again, always remove your unused objects by setting them to null:

' remove the objects 
con = null;
rst = null;

The last example in this chapter is very similar to the VBScript example. The JScript program in Example 3-5 illustrates how an Active Server Page can use the JScript scripting language to create and instantiate ActiveX Data Objects on an Internet Information Server in order to create standard static HTML pages to be sent back to a requesting client. The ASP page should be stored in a file with an .asp extension that is located in an IIS virtual directory.

Once again, ensure that the Biblio.mdb file resides in the C:\Program Files\Microsoft Visual Studio\VB98 directory or that the directory entered in the ASP source matches the location of the Access database file.

Example 3-5. A Simple ASP Page Using JScript

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter has explained how to access and use ActiveX Data Objects with the five most commonly used Microsoft development languages: Visual Basic, Visual C++, Visual J++, VBScript, and JScript. The following is a list of key points:

Visual Basic is an easy language with which to develop ActiveX Data Object applications due to its minimal setup.
Visual C++ offers a keyword, #import, to help create type library information for ADO enumerations (groups of constants) and interfaces. In addition, OLE must be instantiated before any ActiveX Data Objects are created at all.
Visual J++ uses the Java Type Library Wizard to create type library information for ADO enumerations and interfaces.
VBScript and JScript can be used through Active Server Pages to provide requesting clients with static HTML pages based upon an OLE DB data source.
The interface for ActiveX Data Objects is extremely similar throughout all of the languages we have looked at, making it easy to move your skills from one language to another.

The next chapter in this book, Chapter 4, deals with the most fundamental object within ADO, the Connection object. This object is used to create a session with a data source and to create different views with the data source's data.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Within ADO, all activity is centered on the Connection object. A Connection object represents a unique physical connection to a data source. The characteristics of a connection are defined by the values that you pass to the Connection object.

The Connection object is used to establish a unique physical connection to a given data source. This connection defines how you can obtain, interact with, and manipulate data from the specified source. While a Connection object is always required, you can choose whether to instantiate a connection explicitly or to allow ADO to create one implicitly on your behalf.

Example 4-1 illustrates how to open a Recordset object on a table in a data source without explicitly creating a Connection object.

Example 4-1. Implicit Creation of a Connection Object

' declare and instantiate a Recordset
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

' open the Recordset object and implicitly create a Connection
rst.Open "Titles", _
         "DSN=BiblioDSN", _
         adOpenForwardOnly, _
         adLockReadOnly, _
         adCmdTable
'
' do something

' close the Recordset and clean up
rst.Close
Set rst = Nothing

Don't worry about not understanding the entire example now -- I will explain everything soon. Do notice, however, how easy it is to open a table within a data source. Example 4-1 relies on no other code to first establish a connection; the simple connection string DSN=BiblioDSN tells ADO that the table, Titles, is in the BiblioDSN data source.

Some objects in ADO -- in particular, the Recordset and the Command objects -- do not require a pre-existing Connection object to operate. Both objects can read and write data to a data source, and both need a physical connection to a data source to do so. But the Recordset and the Command objects can create their own Connection objects in the background with information that you supply. The choice of declare and establish a connection with the Connection object or to let the Recordset or Command object handle the work for you.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Connection object is used to establish a unique physical connection to a given data source. This connection defines how you can obtain, interact with, and manipulate data from the specified source. While a Connection object is always required, you can choose whether to instantiate a connection explicitly or to allow ADO to create one implicitly on your behalf.

Example 4-1 illustrates how to open a Recordset object on a table in a data source without explicitly creating a Connection object.

Example 4-1. Implicit Creation of a Connection Object

' declare and instantiate a Recordset
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

' open the Recordset object and implicitly create a Connection
rst.Open "Titles", _
         "DSN=BiblioDSN", _
         adOpenForwardOnly, _
         adLockReadOnly, _
         adCmdTable
'
' do something

' close the Recordset and clean up
rst.Close
Set rst = Nothing

Don't worry about not understanding the entire example now -- I will explain everything soon. Do notice, however, how easy it is to open a table within a data source. Example 4-1 relies on no other code to first establish a connection; the simple connection string DSN=BiblioDSN tells ADO that the table, Titles, is in the BiblioDSN data source.

Some objects in ADO -- in particular, the Recordset and the Command objects -- do not require a pre-existing Connection object to operate. Both objects can read and write data to a data source, and both need a physical connection to a data source to do so. But the Recordset and the Command objects can create their own Connection objects in the background with information that you supply. The choice of declare and establish a connection with the Connection object or to let the Recordset or Command object handle the work for you.

By using your own Connection object, you gain greater control over your data access and manipulation. For instance, with a Connection object, you can execute queries through stored procedures that reside in a data source or through SQL statements that you explicitly declare to your application at runtime. The Connection object also offers transaction management so that at critical points in your data-manipulation code, the integrity of your data source can be preserved if an error were to occur.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Let's now take a look at the different ways in which we can configure the connection to a data source through the use of a Connection object. In this section, I will explain how to work with connection strings and Data Source Names (DSNs). Connection strings are detailed explanations of how to open a data source, while Data Source Names are just a name of a definition that is stored on the current machine, by the operating system rather than the application. In addition, I will also talk about how to obtain the version number for the ADO library that you are using and how to set connection options such as the cursor location (whether to run the cursor on the server or the client), the default database setting, and the permission settings.

All connections revolve around connection strings, which contain all the pertinent information to ADO concerning the establishment of a connection to our data source. The connection string comprises a number of arguments. There are five standard ADO arguments that can be used in a connection string:

Provider: Identifies the name of the data provider that you wish to use to establish a connection to a data source. The data provider indicates the type of data source. The Provider argument can be set to such things as Microsoft's OLE DB provider for SQL Server (SQLOLEDB.1) or Microsoft's OLE DB provider for Jet (Microsoft.Jet.OLEDB.4.0). (I will talk about the various types of data providers later in this chapter.)

File Name: Specifies an exact filename (including a path) with which the connection should be established. Because this argument forces ADO to load the data provider that is associated with the data-source type of the file, the Provider argument cannot be used with the File Name argument; when the File Name argument is used, the data provider is implicit rather than explicit.

Remote Provider: Used only when implementing RDS from a client-side Connection object to specify the name of a data provider. As a matter of fact, when using a client-side Connection object, you can use only the Remote Provider and Remote Server arguments.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

As of this writing, Microsoft supplies ten OLE DB providers with ADO. These providers are listed in Table 4-2. Other companies supply their own OLE DB providers. In this book, I will focus on the OLE DB provider for ODBC drivers and the OLE DB providers for SQL Server and Microsoft Access.

Table 4-2: Available Microsoft OLE DB Providers
Provider	Value
Microsoft OLE DB provider for ODBC	MSDASQL.1
Microsoft OLE DB provider for Microsoft Indexing Service	MSIDXS
Microsoft OLE DB provider for Microsoft Active Directory Service	ADSDSOObject
Microsoft OLE DB provider for Microsoft Jet	Microsoft.Jet.OLEDB.4.0
Microsoft OLE DB provider for SQL Server	SQLOLEDB
Microsoft OLE DB provider for Oracle	MSDAORA
Microsoft OLE DB provider for Internet Publishing

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

ADO can work with data, or execute commands, in several ways:

Data can be queried or gathered based on a specific list of qualifications (selection commands).
Data can be manipulated with an action query that usually changes data in some common way throughout your data source (update commands).
Data can be restructured with statements that alter the way the data resides in a particular data source (restructuring commands).

These three types of data gathering and manipulation can be done through either SQL statements or stored procedures. The Connection object allows the execution of both SQL statements and stored procedures directly, through the use of an Execute method. The Execute method can also be used to open an entire table from your data source.

The following sections describe in detail execution of commands.

There are two different syntaxes for the Connection object's Execute method. The first is for commands that return information in the form of a Recordset object (see Chapter 5 ), and the second is for commands that do not return anything.

The correct syntax for a call to the Execute method that returns a Recordset object is:

Set recordset = connection.Execute (CommandText, RecordsAffected, Options)

The correct syntax for a method call that does not return any records is:

connection.Execute CommandText, RecordsAffected, Options

Table 4-3 describes each of the components found in these syntaxes.

Table 4-3: The Components of the Execute Method
Component	Description
connection	A currently open Connection object.
CommandText

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Transaction management is used to maintain the integrity of a data source when operations on one or more data sources need to be treated as a single operation.

The most common example of transaction management comes from banking. Take, for instance, the steps involved in transferring money from a savings account to a checking account. First, you must remove the desired amount of money from the savings account, and then that amount must be added to your checking account. Suppose that somebody walked by and pulled the plug of the ATM machine just when it had completed removing your money from your savings account, but before it added it to your checking account.

By using three methods (BeginTrans, CommitTrans, and RollbackTrans), you can create single transactions from multiple operations.

The BeginTrans, CommitTrans, and RollbackTrans methods are not available when you are using a client-side Connection object with RDS.

A transaction begins with a call to a Connection object's BeginTrans method and ends with a call to the CommitTrans method. The CommitTrans method indicates that the transaction is completed and that the data should be saved, or committed, to the data source.

The following code illustrates the use of the BeginTrans and CommitTrans methods:

' begin a new transaction
con.BeginTrans

'
' do some manipulation of the data here
'

' commit the manipulations of the data to the data source now
con.CommitTrans

Not all data providers support transactions, and you should check before using them. You can tell whether the current data provider supports transactions by checking for the Transaction DDL dynamic property by using the Properties collection of the Connection object. If it appears in the Connection object's Properties collection, then your data provider supports transaction management through the BeginTrans, CommitTrans, and RollbackTrans methods.

Example 4-14 shows how you can test for the support of transactions by your data provider, and how you can work with or without it depending on the result of your test.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Your data source has many characteristics that can be exposed to your applications via ADO. For instance, some data sources have table names, field names, and indexes. You can use the OpenSchema method of the Connection object to enumerate the characteristics that make up the structure of your data source. The OpenSchema method has the following syntax:

Set recordset_name = connection_name.OpenSchema(QueryType, Criteria, SchemaID)

Table 4-7 describes each of the components found in the previous syntax declaration.

Table 4-7: The Components of the OpenSchema Method
Component	Description
recordset_name	A valid Recordset object.
connection_name	A currently open Connection object.
QueryType	Indicates what type of schema query to perform on the associated connection object. This value must be a valid constant that belongs to the SchemaEnum enumeration. Not all QueryType values are supported by every data source. See the OpenSchema method in Appendix C, for more information.
Criteria	Optional. Indicates a specific constraint used to perform the query as defined by the QueryType argument. Criteria values are specific to each QueryType value, and, because not all QueryTypes are supported by every data source, neither are all Criteria values.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter has introduced the Connection object -- the first of the nine major components of ActiveX Data Objects. You have learned how to establish connections with any type of OLE DB data provider. The following list summarizes key points:

You can create a Connection object either explicitly within your own code, or implicitly, through instantiation of a Recordset or Command object.
Each Connection object represents a single unique connection to a particular OLE DB data source through a data provider, both of which you have specified in a connection string.
With the Connection object, you can execute commands against the associated data source. There are three types of commands that can be executed: a SQL statement, a stored procedure, or a parameterized query. In addition, you can open a simple table or pass a data provider-specific string to the particular data provider, which will provide its own analysis and resultset.
The Connection object controls the utilization of transaction management through three methods: BeginTrans, CommitTrans, and RollbackTrans. Transactions are used to manage the integrity of one or multiple data sources when data is manipulated. Like If...Then statements, transactions can be nested.
You can use the Connection object to obtain information about the structure of a given data source with the use of the OpenSchema method. This information can be helpful when querying for table, procedure, or index names.

The next chapter of this book, Chapter 5, explains how to manipulate and read data that is stored in record form, each of which is a collection of fields.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This chapter explains the Recordset object (familiar to users of DAO), which is used to access and manipulate data on the record level. As you learned earlier, all data can be broken down into logical rows, each containing one or more fields. Each field, in turn, describes one specific piece of data that falls into a specific category that is common throughout all the other rows in that rowset. For instance, the Authors table of the Biblio.mdb Access database supplied with Visual Basic contains one row per Author in the table. It also has a field called Name, which is an attribute of every Author.

With the Recordset object, you can navigate through the multiple rows that make up a rowset. You can search for particular rows, move to a row that you previously marked, or move to the beginning or the end of your logical rowset, all with the Recordset object.

The Recordset object is also used to add, edit, or delete records. We can specify how the data provider executes batch commands, and we can also run queries on our data source to provide a customized, filtered view of records.

ActiveX Data Objects allows us to view our records, selected from our data source, in a number of different ways. The way in which our data is present ed to us is described by a cursor.

Cursors present a logical view of a particular recordset. Once the records have been selected for us from our data source by opening our Recordset object, we must decide on how we would like them presented. This is done through a cursor. In ADO, there are four types of cursors:

Dynamic cursor
Keyset cursor
Static cursor
Forward-only cursor

In the following sections, the different cursor types are explained and an example at the end displays the various functions of each. To prepare your Recordset object to open with a particular cursor, use the CursorType property. The CursorType property is both read- and write-enabled while the Reco

Table of Contents

Section 1.1.1.1: Jet/DAO