Knowledge For All

Tuesday, 10 April 2018

ActiveX Data Objects & The Connection Object

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.

Opening and Closing a Connection: Implicit Versus Explicit:-

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.

Opening a Connection:-

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.

Take a look at Example 4-2, which first explicitly creates and opens a Connection object to establish a connection before opening the table from the database.

Example 4-2. Explicit Creation of a Connection Object:-

' declare and instantiate a Connection and a Recordset
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Set con = New ADODB.Connection
Set rst = New ADODB.Recordset
' first establish a connection to the data source
con.Open "DSN=BiblioDSN"
' now open the recordset using the established Connection
rst.Open "Titles", _
con, _
adOpenForwardOnly, _
adLockReadOnly, _
adCmdTable
' do something
' close the Recordset and clean up
rst.Close
Set rst = Nothing
' close the Connection and clean up
con.Close
Set con = Nothing

Notice the amount of extra work that is needed to open the Connection object before opening the Recordset object. Instead of passing a connection string to the Open method of the Recordset object, we are passing the already opened Connection object. This longer piece of code is accomplishing exactly what the previous example did in fewer lines. If a connection string is passed to a Recordset, the Recordset object creates its own Connection object from that string. If you pass a Connection object to a Recordset object, a new Connection object is not created. When opening a lot of Recordset objects, it would be advantageous to pass a Connection object, not a connection string, so that only one connection to the database is created. Figure 4-1 shows us the difference between creating Connection objects implicitly versus explicitly.

Figure 4-1. Implicit versus explicit creation of Connection objects

Creating a Connection object should be done implicitly when you need only one or a small number of connections to a data source. If you plan on having multiple recordsets, or views, of the same data source, you should create your own Connection object, which requires fewer system resources and offers better control, as you will learn in the following sections.

Closing a Connection:-

Although I have not specifically defined how to close a Connection object, you have seen it in all of the code presented thus far. You can use the Close method to close or disconnect the Connection object from the data source. When you use this method, the physical connection is lost, but the Connection object itself remains. It can be reopened with the same properties, or those properties can be altered before the Connection object is opened again. To fully remove the Connection object from memory, to free resources, and to remain respectable in the development community, set the object to the value Nothing, as shown here:

Set con = Nothing

Now that you know how to both establish and break a connection to a data source, we should take a look at the various options that we can use when connecting. These options dictate the ways in which our data is presented to us in the rest of our applications.

When you are using a client-side Connection object with Remote Data Service ( RDS), the connection with the server is not actually established by the Open method. Instead, RDS waits until a Recordset object is opened on the Connection object.

ActiveX Data Objects (ADO) Various Languages Summary

This section 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.

Accessing ActiveX Data Objects (ADO) With JScript

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.

Referencing ActiveX Data Objects:-

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:

Creating ActiveX Data Objects:-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;

Using ADO with JScript: An Example:-

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:-

<% @LANGUAGE="JScript" %>


<html>
<head>
<title>Example of ADO using JScript</title>
</head>
<body>
<script LANGUAGE="JScript" RUNAT="server">
// define our variables which will be used as references to our
// ActiveX Data Objects and instantiate a new Connection object
var con = Server.CreateObject("ADODB.Connection");
var rst;
Response.write("Connection object created. ");
// create two strings for use with the creation of a connection
// and a recordset
var sConString;
var sSQLString;
// create temporary variable for Execute method call
var lRecordsAffected;
// open the BiblioDSN data source with the Connection object
sConString = "Provider=Microsoft.Jet.OLEDB.4.0; " +
"Data Source=C:\\Program Files\\" +
"Microsoft Visual Studio\\" +
"VB98\\Biblio.mdb";
con.Open(sConString, "", "", -1);
Response.write("Connection opened. ");
// create a Recordset object from a SQL string
sSQLString = "SELECT TOP 10 Author FROM Authors";
rst = con.Execute(sSQLString,
lRecordsAffected,
adCmdText);
Response.write("SQL statement processed. ");
// retrieve all the data within the Recordset object
Response.write("Getting data now... ");
while (!rst.EOF) {
Response.write(rst("Author") + " ");
rst.MoveNext( );
}
Response.write(" End of data. ");
// close and remove the Recordset object from memory
rst.Close( );
rst = null;
Response.write("Closed and removed " +
"Recordset object from memory. ");
// close and remove the Connection object from memory
con.Close( );
con = null;
Response.write("Closed and removed " +
"Connection object from memory. ");
</script>
</body>
</html>

Accessing ActiveX Data Objects (ADO) With VBScript

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,[For more detailed information, see ASP in a Nutshell, Second Edition by A. Keyton Weissinger (O'Reilly & Associates, 2000), which goes into depth about how to incorporate ADO into your ASP pages. In addition, Developing ASP Components by Shelley Powers (O'Reilly & Associates, 1999) covers accessing ADO from Visual Basic and Visual J++, discussing how to create an OLE DB simple data provider.] 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.

Referencing ActiveX Data Objects:-

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:

Creating ActiveX Data Objects:-

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.

The first step in creating our ActiveX Data Objects in VBScript, as in Visual Basic, is to define the variables that will be used as references to our ActiveX Data Objects:

' define our variables which will be used as references to the
' Connection and Recordset objects
Dim con
Dim rst

You should notice that I did not use the As datatype notation in the variable-declaration statements. This is because VBScript does not allow us to define variables as a particular type. Because of this, we cannot directly create our variables as ADO objects. Instead, we must use late binding through the CreateObject method of the Server object to assign ActiveX Data objects to our Variant variables:

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

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

Just as in Visual Basic, it is always good practice to remove your objects from memory before your code ends:

' remove the objects
Set con = Nothing
Set rst = Nothing

Using ADO with VBScript: An Example:-

Example 3-4 uses VBScript along with ActiveX Data Objects to create a static HTML sheet that can be passed from the Microsoft Internet Information Server to a client's web browser. It must be assigned a filename ending with an .asp extension and it must be stored in an IIS virtual directory so that IIS recognizes it as an Active Server Page. As with the other projects, ensure that the Biblio.mdb file is located in the C:\Program Files\Microsoft Visual Studio\VB98 directory or that the correct location is entered in the ASP page that you create.

Example 3-4. A Simple ASP Example Using VBScript:-

<% @LANGUAGE="VBScript" %>

<% Option Explicit %>

<html>

<head>

<title>Example of ADO using VBScript</title>

</head>

<body>

' define our variables which will be used as references to our

' ActiveX Data Objects

Dim con

Dim rst

' create two strings for use with the creation of a connection

' and a recordset

Dim sConString

Dim sSQLString

' create a new instance of an ADO Connection object

Set con = Server.CreateObject("ADODB.Connection")

Response.Write "Connection object created. "

' open the BiblioDSN data source with the Connection object

sConString = "Provider=Microsoft.Jet.OLEDB.4.0; " _

& "Data Source=C:\\Program Files\\" _

& "Microsoft Visual Studio\\" _

& "VB98\\Biblio.mdb"

con.Open sConString

Response.Write "Connection opened. "
' create a Recordset object from a SQL string
sSQLString = "SELECT TOP 10 Author FROM Authors"
Set rst = con.Execute(sSQLString)
Response.Write "SQL statement processed. "
' retrieve all the data within the Recordset object
Response.Write "Getting data now... "
Do Until (rst.EOF)
Response.Write rst("Author") & " "
rst.MoveNext
Loop
Response.Write " End of data. "
' close and remove the Recordset object from memory
rst.Close
Set rst = Nothing
Response.Write "Closed and removed " _
& "Recordset object from memory. "
' close and remove the Connection object from memory
con.Close
Set con = Nothing
Response.Write "Closed and removed " _
& "Connection object from memory. "
%>
</body>
</html>

As with the other examples shown so far, the previous code may not mean too much to you yet. Right now, remember that when implementing ADO with VBScript, there are two important things that you should always remember. The first is that all variables are created as Variant datatypes. The second is that you must use late binding through the use of the Server. Create Object method in order to assign a new instance of an ActiveX Data Object to a Variant datatype.

Accessing ActiveX Data Objects (ADO) With Visual J++

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.

Referencing ActiveX Data Objects:-

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.*;

Creating ActiveX Data Objects:-

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;

Using ADO with Visual J++: An Example:-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 Biblio.mdb file in the C:\Program Files\Microsoft Visual Studio\VB98 directory, or have the correct directory for the Access database entered in the source code that you run.

Example 3-3. A Simple Visual J++ Example:-

import com.ms.wfc.data.*;
public class Class1
{
public static void main(String args[]) {
// define our variables which will be used as references to the
// Connection and Recordset objects
Connection con = new Connection( );
Recordset rst = new Recordset( );
// create two strings for use with the creation of a connection
// and a recordset
String sConString;
String sSQLString;
// create temporary variables for Execute method call
long lRecordsAffected;
int nCmdType;
// create a new instance of an ADO Connection object
System.out.println("Connection object created.\n");
// open the BiblioDSN data source with the Connection object
sConString = "Provider=Microsoft.Jet.OLEDB.4.0; " +
"Data Source=C:\\Program Files\\" +
"Microsoft Visual Studio\\" +
"VB98\\Biblio.mdb";
con.open(sConString);
System.out.println("Connection opened.\n");
// create a Recordset object from a SQL string
sSQLString = "SELECT TOP 10 Author FROM Authors";
rst = con.execute(sSQLString);
System.out.println("SQL statement processed.\n");
// retrieve all the data within the Recordset object
System.out.println("Getting data now...\n\n");
while (!rst.getEOF( )) {
System.out.println(rst.getField("Author").getValue( ));
rst.moveNext( );
}
System.out.println("\nEnd of data.\n");
// close and remove the Recordset object from memory
rst.close( );
rst = null;
System.out.println("Closed and removed " +
"Recordset object from memory.\n");
// close and remove the Connection object from memory
con.close( );
con = null;
System.out.println("Closed and removed " +
"Connection object from memory.\n");
}
}

Notice that with the WFC, the implementation of ADO is just as easy as the implementation of ADO within Visual Basic.

Monday, 9 April 2018

Accessing ActiveX Data Objects (ADO) with Visual C++

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.

Referencing ActiveX Data Objects:-

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.

Creating ActiveX Data Objects:-

In order to invoke an ActiveX Data Object, we must first start OLE so that we can use OLE DB. We do this with the following piece of code:

struct StartOLEProcess{

StartOLEProcess( ) {

::CoInitialize(NULL);

}

~StartOLEProcess( ) {

::CoUninitialize( );

}

} _start_StartOLEProcess;

Placing this structure definition anywhere in our application forces the application to call the _start_StartOLEProcess constructor once it has started. This constructor simply calls CoInitialize to initialize OLE. Once our application is complete, the destructor of _start_StartOLEProcess will be called. This in turn will call CoUninitialize, which will shut down OLE.

The next thing we must do to create an ActiveX Data Object is to declare a pointer to the object we wish to create, as follows:

// define a variable that will be used as a reference to the

// Connection object and set it to NULL

ADODB::_ConnectionPtr con = NULL;

// define a variable that will be used as a reference to the

// Recordset object and set it to NULL

ADODB::_RecordsetPtr rst = NULL;

We then can create an ActiveX Data Object by calling the CreateInstance function of our ADO pointer. This function returns a result of type HRESULT to inform us whether the creation of the object was successful. This is illustrated in the following code fragment:

' create a new instance of an ADO Connection object

hr = con.CreateInstance(_ _uuidof(ADODB::Connection));

' create a new instance of an ADO Recordset object

hr = rst.CreateInstance(_ _uuidof(ADODB::Recordset));

Finally, just as in Visual Basic, it is always a good idea to release objects once they are no longer needed. In Visual C++, we accomplish this with a couple of lines of code that look like the following:

' remove the objects

con = Null;

rst = Null;

Using ADO with Visual C++: An Example:-

Now let's take a look at a fully functional example of a Visual C++ application that utilizes ActiveX Data Objects. To try the following code, create a new Win32 Console Application from within Visual C++, choosing the Simple option from the wizard, and replace the contents of the main .cpp file with the code shown in Example 3-2.

Remember, just as with the Visual Basic example, make sure that a copy of Biblio.mdb is in the C:\Program Files\Microsoft Visual Studio\VB98 directory, or that you change the directory in the following source code to reflect the proper path of the Access database. In addition, if you are having trouble with this code, make sure that you have the MSADO15.DLL file in the C:\Program Files\Common Files\System\ado directory or that you have the proper directory entered in the source code.

Example 3-2. A Simple Visual C++ Example

#include "stdafx.h"

#include <stdio.h>

#import "C:\Program Files\Common Files\System\ado\MSADO15.dll" _

rename("EOF", "EOFile")

struct StartOLEProcess{

StartOLEProcess( ) {

::CoInitialize(NULL);

}

~StartOLEProcess( ) {

::CoUninitialize( );

}

} _start_StartOLEProcess;

void main(void)

{

// define our variables which will be used as references to the

// Connection and Recordset objects

ADODB::_ConnectionPtr con = NULL;

ADODB::_RecordsetPtr rec = NULL;

// define variables to read the Author field from the recordset

ADODB::FieldPtr pAuthor;

_variant_t vAuthor;

char sAuthor[40];

// create two strings for use with the creation of a Connection

// and a Recordset object

bstr_t sConString;

bstr_t sSQLString;

// create a variable to hold the result to function calls

HRESULT hr = S_OK;

// long variable needed for Execute method of Connection object

VARIANT *vRecordsAffected = NULL;

// create a new instance of an ADO Connection object

hr = con.CreateInstance(_ _uuidof(ADODB::Connection));

printf("Connection object created.\n");

// open the BiblioDSN data source with the Connection object

sConString = L"Provider=Microsoft.Jet.OLEDB.4.0; "

L"Data Source=C:\\Program Files\\"

L"Microsoft Visual Studio\\"

L"VB98\\Biblio.mdb";

con->Open(sConString, L"", L"", -1);

printf("Connection opened.\n");

// create a Recordset object from a SQL string

sSQLString = L"SELECT TOP 10 Author FROM Authors;";

rec = con->Execute(sSQLString,

vRecordsAffected,

1);

printf("SQL statement processed.\n");

// point to the Author field in the recordset

pAuthor = rec->Fields->GetItem("Author");

// retrieve all the data within the Recordset object

printf("Getting data now...\n\n");

while(!rec->EOFile) {

// get the Author field's value and change it

// to a multibyte type

vAuthor.Clear( );

vAuthor = pAuthor->Value;

WideCharToMultiByte(CP_ACP,

vAuthor.bstrVal,

-1,

sAuthor,

sizeof(sAuthor),

NULL,

NULL);

printf("%s\n", sAuthor);

rec->MoveNext( );

}

printf("\nEnd of data.\n");

// close and remove the Recordset object from memory

rec->Close( );

rec = NULL;

printf("Closed an removed the "

"Recordset object from memory.\n");

// close and remove the Connection object from memory

con->Close( );

con = NULL;

printf("Closed and removed the "

"Connection object from memory.\n");

}

Although much of the previous example will be very foreign to you until you have a thorough understanding of how to develop applications with ActiveX Data Objects, it is particularly important to notice how Visual C++ applications must convert datatypes returned by a field's value. In Example 3-2, a function called WideCharToMultiByte is used to convert a Variant datatype to a normal char string datatype (ASCII) so that it can in turn be passed to the printf function.

Accessing ActiveX Data Objects (ADO) with Various Languages

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.

Accessing ADO With Visual Basic:-

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.

Referencing ActiveX Data Objects:-

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.

Creating ActiveX Data Objects:-

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

If you know for a fact that no other class library listed in the References dialog box of your current Visual Basic application has the same class names as ADO, you may remove the ADODB prefix when declaring and instantiating object variables. However, if you are using more than one object model with the same class definitions (as in the previous example), not specifying the library from which the class should be derived tells VB to instantiate the class from the library that comes first in the list of references to the project.

In Visual Basic, it is always a good idea to remove an object from memory once it is no longer being used. This is done by setting the object to Nothing, as follows:

' remove the objects
Set con = Nothing
Set rst = Nothing

Using ADO with Visual Basic: An Example:-

So that you can visualize how to work with ADO objects in Visual Basic, Example 3-1 uses ADO to open a connection to the Jet Biblio database and to return a recordset containing the names of its first ten authors. Each record is then written to a list box before both the Connection and Recordset objects are closed. Note that the example makes use of dynamic control creation supported by Visual Basic 6.0 or later; if you have an earlier version, simply delete the code that defines, instantiates, and sets the properties of the list box, and place a list box named lstAuthors on the form at design time.

To begin, create a new Application EXE project, and open the Project References menu so that you see the References dialog box shown in Figure 3-1. Select the latest version of Microsoft ActiveX Data Objects that you have installed, and press the OK button.

Now, replace the existing source code for Form1 with the code shown in Example 3-1, and run the application. That's all there is to it. Make sure that you have a Biblio.mdb database located at C:\Program Files\Microsoft Visual Studio\VB98, or if you have it in another location, simply change the path in the code that points to the Access database.

Example 3-1. A Simple Visual Basic Example:-

Option Explicit

Private WithEvents lstAuthors As ListBox

Private Sub Form_Load( )

' create new instances of the Connection and Recordset objects

Dim con As ADODB.Connection
Dim rst As ADODB.Recordset

' instantiate the Connection and Recordset objects
Set con = New ADODB.Connection
Set rst = New ADODB.Recordset

' create two strings to define the connection and the recordset

Dim sConString As String
Dim sSQLString As String

' Create list box control

Set lstAuthors = Me.Controls.Add("vb.listbox", _"lstAuthors", _Me)

lstAuthors.Visible = True

' open the BiblioDSN data source with the Connection object

sConString = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=C:\Program Files" _
& "\Microsoft Visual Studio" _
& "\VB98\Biblio.mdb"

con.Open sConString

Debug.Print "Connection opened."

' create a Recordset object from a SQL string

sSQLString = "SELECT TOP 10 Author " & _
"FROM Authors"

Set rst = con.Execute(sSQLString)

Debug.Print "SQL statement processed."

' retrieve all the data within the Recordset object

Debug.Print "Getting data now..."

Do Until (rst.EOF)
lstAuthors.AddItem rst("Author").Value
rst.MoveNext
Loop

Debug.Print "End of data."

' close and remove the Recordset object from memory

rst.Close
Set rst = Nothing

Debug.Print "Closed and removed " _
& "Recordset object from memory."

' close and remove the Connection object from memory

con.Close

Set con = Nothing

Debug.Print "Closed and removed " _

& "Connection object from memory."

End Sub

Private Sub Form_Resize( )

' this code is added for asthetics

lstAuthors.Top = 0

lstAuthors.Left = 0

lstAuthors.Width = Me.Width

lstAuthors.Height = Me.Height

End Sub

A lot of this information will not make much sense to you now, but it will start to as you begin to learn how to use ActiveX Data Objects from the rest of the chapters in this book. The important technique to notice from this example is how the ADO objects are created in the beginning of the code example, and how the ADO objects are removed at the end of the code example.