Visual Basic Programming: Much Ado about ADO

The ADO architecture makes the very heart of application development in Visual Basic that involves interaction with the databases. Considering that ADO acts as an interface between a Visual Basic application and the data source, providing all the objects and methods of data access, the whole business of database manipulation in Visual Basic environment thus comes to hinge on the ADO architecture.

The know-how of ADO concepts often draws the wedge between an erroneous piece of code and a perfectly working Visual Basic program. This is for the simple reason that creating and using ADO objects and methods indiscriminately and without much idea about their scope and usage makes Visual Basic programming a thorny affair. One wrong move, and an error pops up, which might seem senseless.….unless you understand the ADO architecture to the core. So the answer to that ubiquitous “what the heck is wrong with my code” often lies in how one creates and uses the ADO objects and the associated methods.

This article delves into ADO concepts from Visual Basic programming perspective. It is assumed that the readers already have an elementary knowledge of the ADO architecture and Visual Basic environment. We shall see how the injudicious use of ADO objects and methods affects not only the execution but also the performance of a Visual Basic program. Let us have a look at some common slip-ups that often cause problems in the Visual Basic code.

Not Specifying the Library Being Referred To

Usually, this is how the ADO objects are declared in the General Declaration section:

Dim Con As Connection
Dim Com As Command
Dim Rst As Recordset

This works fine for a program that references just one data objects library (ADODB library in this instance), since you have selected just the ‘Microsoft ActiveX Data Objects 2.1 Library’ from the References menu. So when you say:

Dim Con As Connection

the complier creates a Connection object of ADODB library. But for an application that refers to more than one library that provides the data objects (for instance, the DAO architecture also has Connection, Command and Recordset objects), the habit of not specifying the appropriate library might lead to a messy code.

Here’s how an ADO object should ideally be declared and created:

Dim Con As ADODB.Connection
Set Con = New ADODB.Connection

Improper Ways of Opening the Recordset Object

One reason why ADO programming is a tricky business is mainly because ADO objects offer more than one method of executing an SQL statement, and the novices are often given to the belief that any way they do it is going to be fine. Well, here’s the catch. Have a look at the following piece of code:

Private Sub Form_Load()
Set Con = New ADODB.Connection
Set Com = New ADODB.Command
Set Rst = New ADODB.Recordset
With Con
.Provider = "SQLOLEDB"
.ConnectionString = "user id= sa; password=; initial catalog=videolib"
.Open
End With
Com.ActiveConnection = Con
Set Rst= Com.Execute (Select * from Actors)
With Rst
.CursorLocation = adUseServer
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
End with
End Sub

This code will work fine as far as establishing connection and fetching and displaying data in the Visual Basic form is concerned. The big shock comes when you try the MovePrevious, Delete and AddNew methods of the Recordset objects. An error pops up saying, “The operation requested by you is not supported by the provider”.

Don’t go by what the error reads. There’s nothing wrong with the provider. The problem lies in how the Recordset object has been created in this example. Actually, when you use Execute method of Connection or the Command object, the Recordset created is by default a read-only and forward-only. That’s why, despite having specified the appropriate cursor type and lock type, the Recordset won’t allow you to navigate backwards or modify the records.

To create a Recordset that allows addition and deletion of records and navigation in both directions, the Open method of the Recordset object must be used with the appropriate cursor type and lock type. Here’s how:

Rst.Open “Select * from Actors”, Con, adOpenDynamic, adLockOptimistic

Wasteful Creation of ADO Objects

As mentioned earlier, every ADO object has a definite scope and purpose. These objects should be created sparingly -- only when they are required. Creating ADO objects indiscriminately puts a lot of load on the system resources.

Connection object is indispensable. It has to be created. But you can certainly do away with the need of other two objects depending upon the requirement of the program. For instance, when you need an editable Recordset, all you need is Connection and Recordset objects. In the example that we discussed above, there was in fact no need to create the Recordset object. We discussed it just to pinpoint how sometimes you might end up creating a Recordset that is read-only and forward-only despite having declared proper cursor and lock types. For the purpose of creating an editable recordset, the following code could have fit the same bill, but with more efficiency: 

Private Sub Form_Load()
Set Con = New ADODB.Connection
Set Rst = New ADODB.Recordset
With Con
.Provider = "SQLOLEDB"
.ConnectionString = "user id= sa; password=; initial catalog=videolib"
.Open
End With
Rst.Open “Select * from Actors”, Con, adOpenDynamic, adLockOptimistic
End with
End Sub

Similarly, when you just need to fetch records in a recordset, and the recordset doesn’t have to be editable, all you need to do is to create a Connection object, and use its Execute method to run an SQL statement. Here it goes:

Private Sub Form_Load()
Set Con = New ADODB.Connection
With Con
.Provider = "SQLOLEDB"
.ConnectionString = "user id= sa; password=; initial catalog=videolib"
.Open
End With
Set Rst  = Con.Execute (Select * from Actors)
End Sub

In the above example, we saved the system resources by eliminating the need of Command and Recordset objects. This will in turn make the program more efficient. Efficiency is the buzzword in the real world of programming. How a programmer uses system resources reflects his or her efficiency as a programmer, since execution of code is not the issue in the real world of programming. What matters most is the efficiency.

The Purpose of Command Objects

Now the only question that remains unanswered is, What the heck is Command object used for? The answer is -- in two cases.

1. When we have to execute a stored procedure created at the backend. We need to use the CommandType property of the Command object to specify that the SQL statement is a stored procedure, and the CreateParameter method to create a parameter that is assigned to the Parameter object. Finally, the Execute method executes the stored procedure.
   
2. When we need to access more than one table through a single Visual Basic form and store them in a forward-only, unalterable recordset. This is accomplished by creating as many Command objects as the tables to be accessed. Every Command object in this case is made to represent the respective table by binding the CommandText property of the Command object with the respective table in the SQL statement:

.CommandText = “Select * from Actors”

Here’s the complete code for accessing two tables -- Actors and Directors, using two command objects with a single Connection object:

Private Sub Form_Load()
Set Con = New ADODB.Connection
Set Com = New ADODB.Command
Set Com1 = New ADODB.Command
With Con
.Provider = "SQLOLEDB"
.ConnectionString = "user id= sa; password=; initial catalog=videolib"
.Open
End With
With Com
.ActiveConnection = Con
.CommandText = “Select * from Actors”
.Execute
End With
With Com1
.ActiveConnection = Con
.CommandText = “Select * from Directors”
.Execute
End with
End Sub

NOTE  Apart from creating multiple Command objects for accessing multiple tables, we can also accomplish the same by creating a single Recordset object, which can be bound to multiple tables using a different SQL statement for each table. This eliminates the need of creating Command object, thus enhancing the performance of the program. Here’s the sample code for the same:

Rst.Open “Select * from Actors”, &  _
“Select * from Directors”, Con, adOpenDynamic, adLockOptimistic
Do While Not <some conditions>
Loop
Set Rst = Rst.NextRecordset
Do While Not <some conditions>
Loop

The NextRecordset method of Recordset object here will take you to the next recordset.

Conclusion

We just saw how the prospect of an efficient and error-free Visual Basic application boils down to one’s familiarity with the ADO concepts. So, before you go on to code that first Visual Basic application, it makes sense to first get your feet in the ADO concepts. For, programming in Visual Basic is more about knowing the ins and outs ADO rather than Visual Basic itself.