ADO (Active-X Data Objects)

I am taking ADO in this series not because I want you to learn the ADO as a Data Access Component. But, because I want to make ADO a tool for understanding the usage of COM. Basically, 'Active-x' means 'COM enabled software components'. So, ADO are the components based on COM specification, meant for providing Data Access Capabilities to the client.

Basically, Microsoft has developed ADO so that the client languages which cannot call low level APIs (such as OLEDB) can make use of database support. This is achieved by developing a Component which encapsulates OLEDB API. [If the terms in above statements, related to 'database', such as OLEDB, etc. are not clear to you, don't worry! They will be covered in the database section.] Because this is based on COM. VB, VC++, Scripting Languages, etc. can easily use it...So, it basically makes life more easy.

I could choose any component, but I chose ADO because many of you must have used it. So, it will easier to understand the basic concepts of COM usage using ADO. These concepts are like Early/Late Binding, etc.

First of all let us try to understand the word 'object'. In programming, we call an entity, or self sufficient piece of code as an Object. An object may represent a physical thing... For example - Chair, Boy, Car, etc. We can use the functionality defined in these Objects through the Properties, Methods, Events they provide. I will put more stress on this point in the chapter 'Objects & Classes'.

Now, can you guess the meaning of ADO or Active-X Data Objects. To understand this term let's break it into two parts:

• Active-X - This word suggests that ADO is a COM based software component.

• Data Objects - This suggests that it contains Objects related to Data access, i.e., objects which provide the functionality of Data Access.

We will now learn how can we use ADO or any component for that matter.

Visual Basic & Active-X Data Objects

First of all open a new Standard EXE type project. Now, place a command button on it. We will be connecting to a MS Access database when this button is clicked.

Add a reference to the Excel Library to your project by going to: Project | References...

In the above figure, as you can see, Microsoft Active-X Data Objects 2.5 Library is included. Now what is this Object Library? Do you know what is a library or rather a book library? A collection of books from where we can use any book. Similarly an Object Library is collection of Objects, which, we can use according to need.

This version '2.5' is because of the version of the server. In this case ADO 2.5 is installed on this machine. If your computer has previous versions, they may be 2.0 or 2.1, etc. And see below, the location is shown, it is the location of the file which is providing this service. Or, in other words, location of the component.

Why do we need to put references at all? This tells VB that we are going to use the functionality of the specified COM Component. If we do not include the reference, VB will not be able to use the component. (It has something to do with interfaces...So, for right now the this description is sufficient, later we will see the technical aspect of this.)

Note that there are two Priority buttons.  I am going to take them shortly...

Now let's see some code. Following is the 'Click' Event Procedure for Command Button - Command1.

01       Private Sub Command1_Click()
02           On Error GoTo err_handler
03           Dim con As ADODB.Connection
04           Set con = New ADODB.Connection
05           con.Provider = "Microsoft.Jet.OLEDB.4.0;" & _
06                          "data source=c:\db1.mdb"
07           con.Open
08           MsgBox "Connected..."
09           MsgBox "Now Closing..."
10           con.Close
11           Set con = Nothing
12           Exit Sub
13       err_handler:  
14           MsgBox "Can't connect"
15       End Sub

Let's review the piece of code above...

In line no. 3, we are declaring an object variable named 'con'. Now, what are we actually telling the compiler to do? We are telling compiler that we want to hold data of type Object in con. Which object? ADODB.Connection. Note that we are just defining a container which can hold data of type ADODB.Connection, or Connection Object in short. The interesting fact here is that there is nothing right now which con is holding. But, it is fixed that con is going to hold an object of type ADODB.Connection. Now, because con is destined to hold only an object, it is called an object variable. For this piece of code, con can only hold object of type ADODB.Connection.

In line no.4 we are actually creating an object. Now it is time to see certain things more technically. Basically, an object is something in memory which has some data and some methods to manipulate that data associated with it. Compiler is capable of creating such objects for us. But how does compiler know which object we want  it to create. The answer is simple. We tell it. We tell the compiler the class to which the object belongs. Now, what the hell is this class? It is the specification for the object or simply it contains the details of what exactly has to be created. Here I remember a few analogies.

Analogy I: Do you know about the maps/plans for houses? They contain the specification of how to build a particular house/building. But, is it itself a house? Of course not! But several houses can be built using this plan. Similarly, a class (such as ADODB.Connection) is not an object itself but the specification for the object and obviously, any number of objects can be created using this class.

Analogy II: Suppose I give you a sheet of paper which has instructions/steps required to build a toy. You implement those instructions and get a toy. Now, class is the instruction set and object is the toy.

When we create (or compiler creates, to be more accurate) an object, we can say that the compiler is instantiating the class. Or, compiler is creating an instance of the class. That is, an object is called to be instance  of the associated class. But how can we instantiate a class or putting in simple words, how can we create the object of a class?

There are a number of ways for doing so. In this chapter we will be discussing only one of them. Rest will be covered in the next chapter. We'll be using the new keyword for creating an object of a class. This keyword creates the object of the specified class and returns its reference which can be stored in the object variable. But unlike normal variables, we have to use the keyword Set before the object variable. Let's see line no. 4 again. Here we are telling new to create object of class ADODB.Connection, also we are storing the reference returned in con. Also, notice the usage of Set. Only after this statement, con is pointing to some object. Now, through con, we can call the Properties and methods (If you don't know what are these, please refer to the chapter on Objects and Classes) of the object of ADODB.Connection (Now, we will call it Connection Object in short...). This is shown in line nos. 5 & 6. In line 5, we are setting a property of Connection Object and in line 6, we are calling a method of Connection Object. [It must be clear that without line 4, this is not possible as there will be no object then!] This method open  establishes the connection to the specified database (by setting Provider property).

Now, in line no. 10, we are calling the close method of Connection object (through con which is pointing to the Connection Object. Remember con is not the connection object, it the object variable. This means it can hold reference to some object). This closes the link between the database and our program. Now, at this point, we can call another open method to connect with some other database because our object still exists... If we want to release our object and free the memory, when we are no longer using the object, we can tell our object to point to Nothing. When we do so, as in line no. 11, our object variable con stops to point to the connection object. This means the object is pointed by no object variable and is hence unusable. Such objects are basically orphans. The Operating System takes care to free the memory by removing such orphans from. memory.

To make the above more clear, I am giving you a visual representation of above facts:

As you can see in the image above, I am referring to ADODB.Connection as just Connection. Yes, we can do that. Basically, ADODB is the component to which the class Connection (and many others) belong. If we use ADODB.Connection, we are basically mentioning fully qualified name of the class. This is not necessary. So, we can just use the Class name.

But, what will happen when we are referencing two components and both of them have a class with same name? In such a situation, we can use the fully qualified name to refer to that particular class. But what if we use only partial class name (not fully qualified). In such a case, the Visual Basic compiler will consider the class of the component which has more priority. Remember, the References... dialog box? Yes, we can set the priority while including the reference to the Object Library, using the Priority Buttons. We will discuss this aspect practically in the next chapter - Automation.

One last thing, instead of writing two different statements to declare the object variable and creating object and assigning reference (Statement No. 3 & 4), we can use a single statement:

Dim con As New Connection

This is equivalent to the following:  

Dim con As Connection
Set con = New Connection

Was this chapter a real long one, did it bore you? But I can't help - This is my way... I was planning to add one more topic also in this chapter, but it is better to take that up in the next. Otherwise, many of you will run away...