Book Review: Beginning Visual Basic 6 Databases

5 Click OK and run your program. It works as advertised:

We have just created an ODBC data source that can be used with an Access database. If
you needed another data source, you would follow the same steps with that driver. Now the
OLE DB provider can take our commands from ADO and translate them to the new ADO ODBC data
source. So the OLE DB acts as a universal translator from ADO to whichever ODBC driver we
happen to be using.

In In case you were wondering what the connection string looks like, here it is:

Provider=MSDASQL.1;Persist Security Info=False;User ID=Admin;Data Source=Our ADO
Example DSN;Mode=Share Deny None

As you can see, all of the required information is now built into the string. Notice
that the provider is now MSDASQL.1. We are now accessing the Biblio.mdb database via the
ADO data control using ODBC.

Let’s take a look at using some VB code to access our data via our new DSN

1    Add a form to your project, and name it frmDSN. Add a single
command button to the form and give it the name cmdTestDSN. Give it a caption as shown
below:

2    In the Click event of the command button, add the following code:

Private Sub cmdTestDSN_Click()

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

'If we wanted, we could set the provider property to the OLE 
'DB Provider for ODBC. However we will set it in the connect 'string.

' Open a connection using an ODBC DSN. The MS OLE DB for
' SQL is MSDASQL. We gave our new data source the name "Our ADO Example DSN" 
' so let's use it.

myConnection.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;User
ID=Admin;Data Source=Our ADO Example DSN;Mode=Share Deny None"

myConnection.Open

' Determine if we conected.
If myConnection.State = adStateOpen Then
MsgBox "Welcome to the Biblio Database!"
Else
MsgBox "The connection could not be made."
End If

' Close the connection.
myConnection.Close

End Sub

3    Let’s give it a try. Run the program and press the command button.
Success!

How It Works

Since we only want to test an ODBC connection, we only need to dim a new object
variable as type ADODB.Connection. We then immediately initialize the object variable
using the Set key word:

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

Next, we just lifted the connection string that was built from our DSN. If you wish to
copy it, simply bring up the ADO data control and copy the connection string. Our DSN
provider placed the full connection string there. Since this is a string, be sure that the
entire string is on a single line:

myConnection.ConnectionString = "Provider=MSDASQL.1;Persist _
  Security Info=False;User ID=Admin;Data Source=Our ADO Example _
  DSN;Mode=Share Deny None"

Once we set the .ConnectionString property of the connection object, we simply invoke
the .Open method to establish a connection to the data source:

myConnection.Open

We can then interrogate the .State property to see if the connection is open:

If myConnection.State = adStateOpen Then
MsgBox "Welcome to the Biblio Database!"
Else
MsgBox "The connection could not be made."
End If

If you need to find out the state of the connection, you can easily check the .State
property against these constants:

And since in our program the state is equal to adStateOpen, we display our message box
indicating success! We then close the connection.

Now that we’ve demonstrated how to open up our connection to the data source, let’s
consider how to run some SQL against the data in the data source. We send any processing
commands via the Execute method of the connection object.
Using the Connection Object’s Execute Method
To We can use the Execute method to send a command (typically an SQL statement, but it
might be other text) to the data source. If our SQL statement returns rows (instead of,
say, updating some records) then a Recordset is created. The Execute method in reality
always returns a Recordset. However, it is a closed Recordset if the command doesn’t
return results.Let’s see an example of the Execute method in action.

1    Add another button to the frmDSN form to test the execute method.
Name the new command button cmdExecute and give it the caption Text Execute, as shown
here:

2    Add the following code to the cmdExecute button’s Click event:

Private Sub cmdExecute_Click()

Dim myConnection As ADODB.Connection
Dim myRecordSet As ADODB.Recordset

Set myConnection = New ADODB.Connection


myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data
Source=C:BegDBBiblio.mdb"

myConnection.Open

' Create a Recordset by executing a SQL statement
Set myRecordSet = myConnection.Execute("Select * From Titles")

' Show the first title in the recordset.
MsgBox myRecordSet("Title")

' Close the recordset and connection.
myRecordSet.Close
myConnection.Close

End Sub

3    Run the program and press the Text Execute button. You’ll see this
message box appear:

How It Works

We learned a few interesting things in this example. First, we added the connection
string directly to the connection object. In prior examples we first assigned the
connection string to a string variable, then passed in the string variable to the
connection.ConnectionString property. This current example is a bare, minimalist approach
to a connection string. We just pass the provider and the data source:

myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51; _
  Data Source=C:BegDBBiblio.mdb"

Then, once the connection string property is set, we open the connection:

myConnection.Open

Once the connection is open, we now want to issue an SQL statement. We do this by using
the .Execute method of the connection object. Here we are selecting all of the records
from the Titles table:

' Create a Recordset by executing a SQL statement
Set myRecordSet = myConnection.Execute("Select * From Titles")

And finally, we simply display the contents of the Title field. Notice that we access
the field by using the name of the field. Of course, we could have used the "!"
bang operator or the ordinal position as we have done in the past:

MsgBox myRecordSet("Title")

And since the current record is the first record in the recordset, the title of the
first book in the first record is displayed.

One thing to keep in mind is that the returned Recordset will always be a read-only,
forward-only cursor. This means you can’t edit or scroll backwards. If you need a
Recordset object with a bit more functionality, then create a Recordset object with the
desired property settings. After the settings are in place, use the Recordset object’s
Open method to execute the query that will return the desired cursor type. We’ll talk some
more about using cursors in conjunction with recordsets later in the chapter.

Now, let’s discuss in more detail how we can interact with the recordset. The logical
place to start is with how we open a recordset.

To open a recordset, we use the .Open method of the recordset object and pass in the
name of the table we want to be placed in the recordset (as well as the name of the open
connection) as parameters. In this example, we can open a recordset with only two
parameters as shown:

adoRecordset.Open "Publishers", adoConnection

Once the recordset is opened, we simply loop through like we did using DAO. In fact,
the syntax to access a field is exactly the same using the recordset!field notation. So
once we open the recordset, the programming is almost identical to DAO:

Do Until adoRecordset.EOF
List1.AddItem adoRecordset!Name
adoRecordset.MoveNext
Loop

To Finally, when we want to close both the recordset and connection, just use the Close
method of both objects:

adoRecordset.Close
adoConnection.Close
set sdoRecordset = Nothing
set adoConnection = Nothing

We certainly did dimension these object variables locally – they only exist in the
click event procedure of the command button. As such, they go out of scope when the
program leaves the procedure. So if we omitted the .Close methods, both would be closed by
default when they go out of scope. But we have been talking consistently about not relying
on the default behavior of Visual Basic. This has to do with both initializing variables
as well as releasing them. So it is good form to explicitly close both of the object
variables before exiting the procedure. We should also set both object variables to
Nothing which effectively releases the pointers to them and frees up the memory they
consume.

Fun with Schemas

Remember when we wrote the Database Analyzer using DAO a while back? This worked great,
but on Access .mdb files only. However, what happens if we are using an OLE DB data
provider and we don’t know exactly what fields are available? Well, we can accomplish the
same thing for any data source as we did using our DAO Table Analyzer, using ADO. Since
ADO really talks to the OLE DB layer, we can get any information on the underlying data
source from the OLE DB provider. This can easily be done by using the OpenSchema method of
our connection object.

By using the OpenSchema method, we can spy on information about the particular data
source we are connected to. We can easily get information about the data source, such as
the tables on the server and the columns in those tables.

Our application uses ADO to talk to the OLE DB data provider. Our application probably
does not have to know how to communicate with all of the various data stores. We just use
ADO to talk to the OLE DB provider and it takes care of the nitty gritty of how to
communicate with the various and sundry data stores. When we select a specific OLD DB data
provider, we know that our application can just use ADO to talk to that OLE DB provider.
And using this approach, we are removed from having to know about the details of each and
every data source. We just leave that up to the OLE DB provider.

But, despite all this built-in invisibility, what if our program needs to know
something about the data store we are accessing? For example, what if we need to find out
things like field names? Or what if we need to know if certain variables will be
supported? Well, this is a snap using ADO.

It is the responsibility of the OLE DB provider to give us this type of information.
This way our application can quickly get information on the underlying data store that
might range from a relational database such as Access to an e-mail message or text file.

When reading about OLE DB, you will see the terms Consumer and Provider. A consumer is
any application that uses – or consumes – OLE DB interfaces. For example, our programs
have been using ADO to talk to OLE DB to connect to our Access database. Our ADO code and
the data control are both consumers of OLE DB services.

An OLE DB provider uses OLE DB interfaces, such as our ODBC connection that we created.
This means that an OLE DB provider (our ODBC connection) allows consumers of their
services to access data in a uniform way via the OLE DB interface. Conceptually, an OLE DB
provider is similar to an ODBC driver. That driver provides a uniform mechanism for
accessing relational data – it understands SQL. But the cool thing about OLE DB providers
is that they not only provide a mechanism for relational data, but they can talk to
non-relational data sources as well.

OK, what if our program needs to find out information about the underlying data store?
What we’ll do next is create some code that will allow us to display information about how
the data source that we want to access is laid out. We’ll display the description of
what’s in the data source – its schema.

Try It Out – Getting the Schema of the Data Source using ADO

1 Add a new form to your project. Call the form frmSchema. Add a single command button
named cmdSchema – nothing fancy:

We will use this single form for these next few examples – we will just add a few
command buttons and print the results to VB’s Immediate Window using the Print method of
the debug.object. Rather than cloud the examples with a lot of formatting code, I want to
focus on the ADO code. So just use a single form and add another button when asked.
Thanks!
2    OK enough talk. Let’s do some coding. Add the following code to the
Click event procedure of the command button. This code will establish a connection with a
data source. Then we will ask the data source which tables and fields are available. You
will quickly notice that the ADO code is much easier to write than the equivalent DAO
code.

Private Sub cmdSchema_Click()

Dim adoConnection As ADODB.Connection
Dim adoRsFields As ADODB.Recordset
Dim sConnection As String
Dim sCurrentTable As String
Dim sNewTable As String

Set adoConnection = New ADODB.Connection

sConnection = "Provider=Microsoft.Jet.OLEDB.3.51;Data
Source=c:BegDBBiblio.mdb"

adoConnection.Open sConnection

Set adoRsFields = adoConnection.OpenSchema(adSchemaColumns)

sCurrentTable = ""
sNewTable = ""

Do Until adoRsFields.EOF
sCurrentTable = adoRsFields!TABLE_NAME
If (sCurrentTable <> sNewTable) Then
sNewTable = adoRsFields!TABLE_NAME
Debug.Print "Current Table: " & adoRsFields!TABLE_NAME
End If
Debug.Print " Field: " & adoRsFields!COLUMN_NAME
adoRsFields.MoveNext
Loop

adoRsFields.Close
Set adoRsFields = Nothing
adoConnection.Close
Set adoConnection = Nothing

End Sub

3    Run your frmSchema form and click on the Schema button. We will
step through what the code is doing shortly, but first, take a look at the results that
appear in the Immediate window:

Since we are using the debug.print method, the output is just being sent to the
Immediate window. Of course, if you wish, you can get fancy and place the output in a
TreeView control as we did using DAO a few chapters back. But in this example we can see
just how easy it is to interrogate the OLE DB provider to get this type of information.
You will see some tables that start with Msys such as MSysIMEXColumns. These tables are
used by Jet to store various meta-information about the tables and database.
Meta-information really means information about information. So you get to spy on the
various tools that Jet uses to maintain an Access database. Of course, these would not be
present if you used ADO to open another – non-Access – data source.

How It Works

We start out by dim’ing our local variables. We dim an ADODB connection and recordset
object as usual:

Dim adoConnection As ADODB.Connection
Dim adoRsFields As ADODB.Recordset
Dim sConnection As String
Dim sCurrentTable As String
Dim sNewTable As String

Next, we set a reference to our (new) connection in preparation for opening it. Of
course, that reference is in our object variable adoConnection. In order to open the
connection, we must set the .ConnectionString property. So again, to illustrate the point,
we just assign the connection string to a string variable, sConnection. Next, we invoke
the .Open method of the connection object and pass in the sConnection variable that holds
the connection string as a parameter:

Set adoConnection = New ADODB.Connection
sConnection = "Provider=Microsoft.Jet.OLEDB.3.51;Data
Source=c:BegDBBiblio.mdb"
adoConnection.Open sConnection

We now have an open connection. Why not do something with it?

Our cunning plan is to retrieve a recordset of information about the data source. By
using the .OpenSchema method, we can get returned to us information about the data source,
such as information about the tables on the server and the columns in the tables. There
are several constants that can be used to retrieve specific information about the
underlying data source. Of these, we will use the adSchemaColumns constant – this will
return the table name and the column name. This way we can find out about what tables are
in the database, and what fields are in the tables (in our next example, we will find out
the details about the individual fields in the tables):

Set adoRsFields = adoConnection.OpenSchema(adSchemaColumns)

At this point, we have a valid recordset containing the information about the table and
field names. Now we will loop through the recordset and print the results in the debug
(immediate) window. The two string variables, sCurrentTable and sNewTable, are used as
placeholders. We will loop through the results and provide the table name as a header and
then print the fields inside that table in an indented manner.

The returned recordset, adoRsFields, will have a combination of Table Name and Field
Name in each record:

sCurrentTable = ""
sNewTable = ""

Do Until adoRsFields.EOF
sCurrentTable = adoRsFields!TABLE_NAME
If (sCurrentTable <> sNewTable) Then
sNewTable = adoRsFields!TABLE_NAME
Debug.Print "Current Table: " & adoRsFields!TABLE_NAME
End If
Debug.Print " Field: " & adoRsFields!COLUMN_NAME
adoRsFields.MoveNext
Loop

The Do loop simply runs the code inside of it until the end of the recordset. The first
time though the loop, we assign the value of the current table name to the variable
sCurrentTable. Of course, there will be several fields for each table, so we want to only
print the table name once.

The next line is used to determine if there is a new table name in the current record.
However, we initialized the variable sCurrentTable to "", so the sCurrentTable
value and the name of the table are not equal. Therefore, we first assign the name of the
current table to sNewTable and then print the name of the table in the Immediate window.

The code then exits the If…End If and prints the name of the field in that same
record. Recall that each record in the recordset will have both the current table and a
field in that table. Then the current record pointer is incremented by using the .MoveNext
method of the recordset.

The next time through the loop, we assign the name of the table in that record to
sCurrentTable. If the name of that table is equal to the name of the table name that was
just printed out, If…End If structure is bypassed and only the field is printed.

There will be times when we need to know the data types that are supported by the
underlying data source. For example, we would not want to try to write a variable to an
underlying field if that field could not support the data, right? For example, it would be
embarrassing to write a variant to an integer field, only to be surprised by an error
message.

It would be great if there were a simple way for us to find out what is supported by
whatever data store we are connected to, right? Well, ADO provides an easy and painless
way to find out.

As mentioned above, you can also use the .OpenSchema method to find out this important
information. By passing in the constant adSchemaProviderTypes as a parameter, a recordset
is returned that shows all of the types provided. Let’s have a go at doing that now.

Try It Out – Determining Data Types of the Data Source using ADO

1 Add another button to your frmSchema form that was used in the proceeding example.
Name it cmdDataTypes and caption it as shown:

2    Next, add this code to the click event of the
cmdDataTypes button. If you are lazy like me, you can cut and paste from the
cmdSchema_Click event and just change a few lines:

Private Sub cmdDataTypes_Click()
Dim adoConnection As ADODB.Connection
Dim adoRsFields As ADODB.Recordset
Dim sConnection As String
Set adoConnection = New ADODB.Connection
sConnection = "Provider=Microsoft.Jet.OLEDB.3.51;Data
Source=c:BegDBBiblio.mdb"
adoConnection.Open sConnection
Set adoRsFields = adoConnection.OpenSchema(adSchemaProviderTypes)
Do Until adoRsFields.EOF
Debug.Print "Data Type: " & adoRsFields!TYPE_NAME & vbTab _
& "Column Size: " & adoRsFields!COLUMN_SIZE
adoRsFields.MoveNext
Loop
adoRsFields.Close
Set adoRsFields = Nothing
adoConnection.Close
Set adoConnection = Nothing
End Sub

3    Press F5 to run the program. Press the Data Types
button. When you run the program, the following output is sent to the immediate window:

How It Works

Since the code is almost identical to our last program, we don’t need to
dwell on what is happening. But let’s take a look at the output. Of course, the Data Type
tells us all of the data types available in this particular data source. The Column Size
tells us the length of a column or parameter. The length refers to either the maximum or
the defined length for this type by the provider. For character data, this is the maximum
or defined length in characters. For date/time data types, this is the length of the
string representation (which assumes the maximum allowed precision of the fractional
seconds component). If the data type is numeric, the column size is the upper bound on the
maximum precision of the data type. Pretty cool.

Earlier in this book we touched on the Object Browser. Well, this is a
very handy tool to use for becoming familiar with the various members of the ADODB model.
Take a minute and select View | Object Browser from the main VB 6.0 IDE window. Select
ADODB from the drop down window and take a look around. This will be time well spent in
becoming familiar with all of the members of ADODB:

Let’s walk through what some of the information displayed here is all about.

The ADO Errors Collection

One thing we need to learn about is the Errors collection. When an error is encountered
by ADO, the Errors collection is filled with detail on the culprit. Depending on the
source of the error, or even if there are bugs in the underlying OLE DB provider to ADO,
the Errors collection may not be populated. But for the most part, VB will tell you the
cause of the problem. The Errors collection is available only from the connection object.

Let’s take a look at how we can access the information that’s held in the Errors
collection.

Try It Out – Harvesting Errors from the Errors Collection

1 Add another command button to your frmSchema form and name it cmdErrors. Add an Error
Collection caption so that it looks like this:

2    Add this code to the click event procedure of the cmdErrors button:

Private Sub cmdErrors_Click()

Dim adoConnection As ADODB.Connection
Dim adoErrors As ADODB.Errors

Dim i As Integer
Dim StrTmp

On Error GoTo AdoError

Set adoConnection = New ADODB.Connection

' Open connection to Bogus ODBC Data Source for BIBLIO.MDB
adoConnection.ConnectionString = "DBQ=BIBLIO.MDB;" & _
"DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DefaultDir=C:OhNoooDirectoryPath;"

adoConnection.Open

' Remaining code goes here, but of course our program
' will never reach it because the connection string
' will generate an error because of the bogus directory

' Close the open objects
adoConnection.Close

' Destroy anything not destroyed yet
Set adoConnection = Nothing

Exit Sub

AdoError:

Dim errorCollection As Variant
Dim errLoop As Error
Dim strError As String
Dim iCounter As Integer

' In case our adoConnection is not set or
' there were other initialization problems
On Error Resume Next

iCounter = 1

' Enumerate Errors collection and display properties of
' each Error object.
strError = ""
Set errorCollection = adoConnection.Errors
For Each errLoop In errorCollection
With errLoop
strError = "Error #" & iCounter & vbCrLf
strError = strError & " ADO Error #" & .Number & vbCrLf
strError = strError & " Description " & .Description & vbCrLf
strError = strError & " Source " & .Source & vbCrLf
Debug.Print strError
iCounter = iCounter + 1
End With
Next

End Sub

3    Press F5 and run the program, then click on the Error Collection
button.

The Errors Collection Output

We didn’t place these in screen shots of the immediate window because the output is
actually longer than the screen. The error messages have gone from being rather terse to
chatty Cathy in nature. They now are almost conversational. Here is what you will see in
the immediate window:

Error #1
ADO Error #-2147467259
Description [Microsoft][ODBC Microsoft Access 97 Driver] ‘(unknown)’ isn’t
a valid path. Make sure that the path name is spelled correctly and
that you are connected to the server on which the file resides.
Source Microsoft OLE DB Provider for ODBC Drivers

Error #2
ADO Error #-2147467259
Description [Microsoft][ODBC Driver Manager] Driver’s SQLSetConnectAttr
failed
Source Microsoft OLE DB Provider for ODBC Drivers

Now that’s handy. Instead of some strange number, the ADO errors are more ‘wordy’ – and
they even look like English. This is more like it!

Let’s have a look at how this all fits together.

How It Works

We first want to set up an error handler to trap and skin any errors that may occur in
our program:

On Error GoTo AdoError

When the code hits this line, our local error handler, AdoError, becomes active. From
this point forward, when an error occurs in our procedure, control automatically jumps to
the label AdoError that contains our handler.

Our connection string is then defined.

adoConnection.ConnectionString = "DBQ=BIBLIO.MDB;" & _
"DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DefaultDir=C:OhNoooDirectoryPath;"

Of course, we don’t have a directory called C:OhNoooDirectoryPath so the connection
object will not be able to communicate with the database.

Simply setting the bogus connection string does not cause the error. But when we invoke
the .Open method with the faulty string, this does cause our problem and generates the
error:

adoConnection.Open

When our program can’t establish a connection with the database, an error is generated.
And since we have an active error handler, the code jumps there immediately.

Once in our error handler, we can then loop through the error collection of our
adoConnection object. And each error provides us with a Number, a Description of the
error, and the source of the error. Very handy, and a lot of useful descriptive
information:

Set errorCollection = adoConnection.Errors
For Each errLoop In errorCollection
With errLoop
strError = "Error #" & iCounter & vbCrLf
strError = strError & " ADO Error #" & .Number & vbCrLf
strError = strError & " Description " & .Description & vbCrLf
strError = strError & " Source " & .Source & vbCrLf
Debug.Print strError
iCounter = iCounter + 1
End With
Next

We route each error to the immediate window using our trusty debug.print. This is the
result of attempting to connect with a database that resides in a bogus path. Notice that
the first error is smart enough to know that the problem is a bad path! It not only tells
us the problem, but is polite enough to suggest what we should do about it:

Error #1
ADO Error #-2147467259
Description [Microsoft][ODBC Microsoft Access 97 Driver] ‘(unknown)’ isn’t
a valid path. Make sure that the path name is spelled correctly and
that you are connected to the server on which the file resides.
Source Microsoft OLE DB Provider for ODBC Drivers

Next, we’ll see how we can have a look at how we can display some information about the
Data Provider itself.

Well, while we are at spying on what data types and column sizes, why not find out
about the data provider? Well, it’s easy. And you might notice that again, the connection
object is the workhorse of this operation.

Try It Out – Getting Information about the Data Provider

1 Keep the venerable form frmSchema used for the above exercises and add yet another
command button. Name this one cmdProvider and caption it Provider.
2 Add the following code to the Click event procedure of the new command button:

Private Sub cmdProvider_Click()
Dim adoConnection As ADODB.Connection
Dim sConnection As String
Set adoConnection = New ADODB.Connection
sConnection = "Provider=Microsoft.Jet.OLEDB.3.51;Data
Source=c:BegDBBiblio.mdb"
adoConnection.Open sConnection
'Output all of the version information to the debug window.
Debug.Print "ADO Version: " & adoConnection.Version & vbCrLf
Debug.Print "Database Name: " & adoConnection.Properties("DBMS
Name") & vbCrLf
Debug.Print "Database Version: " & adoConnection.Properties("DBMS
Version") & vbCrLf
Debug.Print "OLE DB Version: " & adoConnection.Properties("OLE DB
Version") & vbCrLf
Debug.Print "Provider Name: " & adoConnection.Properties("Provider
Name") & vbCrLf
Debug.Print "Provider Version: " & adoConnection.Properties("Provider
Version") & vbCrLf
End Sub

3    Your immediate window is probably getting a bit crowded just about
now. You might wish to highlight the contents and press delete to clear it out.
Unfortunately the debug has only two methods – print and assert. It would be very handy if
it also has a .Clear method. Ah well…
OK, run your program and press the Provider button. Take a look at the immediate window –
it should look something like this:

How It Works

Most of the code is identical as the previous examples. But for this code we only open
a connection. Then, by interrogating the .Version property of the connection object, we
can get its value and send it to the debug window. We then print out values from the
Properties collection of the connection object. The properties collection contains
provider information that can be read-only or read/write:

Debug.Print "ADO Version: " & adoConnection.Version & vbCrLf
Debug.Print "Database Name: " & adoConnection.Properties("DBMS
Name") & vbCrLf

A Word on Setting References

Some programmers prefer to save a line of code and dimension Object variables. For
example, we dimensioned the connection object variable like so:

Dim adoConnection As ADODB.Connection

This tells VB that we will have a variable called adoConnection that will be of
ADODB.Connection type. This is not unlike dimensioning a variable of type integer or
string. Then in the next line, we actually create the variable:

Set adoConnection = New ADODB.Connection

At this point, we have an object variable, adoConnection, of type ADODB.Connection.
Some programmers like to save a line of code and actually insert the NEW keyword directly
in the declaration like this:

Dim adoConnection As NEW ADODB.Connection

Notice that the keyword NEW is inside the dimension statement. If we add the word NEW
here, then we do not need the line of code that Sets the reference to a NEW connection.
The adoConnection variable is set to Nothing and no memory is yet allocated for it. If we
don’t use it in our code, then it never really gets created. The only memory penalty is
that of an unused object variable. But the first time we reference it, the variable
springs to life.

Functionally it does not matter is we use the NEW keyword when we dimension the object
variable or if we explicitly set the dimensioned variable to a NEW ADODB.Connection in a
separate step. Either way, we get a reference variable that points to a separate object –
in this case an ADODB.Connection.

Visual Basic 6.0 always initializes intrinsic variables to something. Typically the
value is zero or empty. But object variables such as our adoConnection get initialized to
Nothing.

As a rule of thumb, I recommend using an explicit Set statement, as we have done, when
using object variables. There are several reasons for this, but of course you may not find
them compelling.

If we declared the adoConnection using the NEW keyword, then the first time we touch
the variable (i.e., use it), the object is created for us automatically. But you can never
set a break point on a DIM statement because declarations are not executed at run time. If
you use the Set statement, you can use the debugger to step on that line. So if there is
an error setting the object variable, it will be clear to us what caused the error. If we
used NEW, then the variable is created when me touch it, like making an assignment. If
there is an error, it could be due to setting OR the assignment itself.

But in any case, our object variables are declared locally, so they go out of scope as
soon as the procedure is exited. If you declare a form level or global object variable
using the NEW keyword, then it will be in your program until you set it to Nothing. But if
you want to use it again, just reference it and it will be there for you. Again, I prefer
to always have control of when an object variable is instantiated and destroyed. There are
always exceptions to the rule, but for our purposes it just gives you finer granularity on
control of what is happening, rather than permitting VB to be in control.

Using the NEW keyword in the declaration statement is known as explicit creation of the
object, meaning that Set is not required. Again, when we use the As New syntax in the
declaration, we lose control. But worse than that, we can’t tell if the variable has
already been created. In fact, when we test to see if it has been created by using
something like "If adoConnection is Nothing", that might actually create the
object! Why, because we are referencing it. Yikes!

When we use the Set statement, this explicitly assigns an object to an object variable.
When we are done with it, we simply set adoConnection = Nothing. If we wish to use this
again (within its scope, of course) we must use the Set statement again. If we declared it
implicitly, we just reference the object variable and it is there to do our bidding. So
this can look a bit confusing, but really makes sense when you realize what is going on
under the hood.

Remember when we looked at the DAO object model back in Chapter 9? Well, you will be
pleased to know that the ADO model is much flatter. There are fewer collections, but the
model sports much more functionality.

Here is a slightly different slant on the ADO object model than we presented at the
beginning of the chapter. It has the same information, but here it is presented a bit
differently. The gray boxes represent collections. Remember when we iterated through each
of the errors in the Errors Collection?

There are some differences between the DAO and ADO object model. For example, all
objects represented can be created independently. The exceptions are the Error and Field
objects because they are dependent on the Connection and Recordset objects respectively.
Otherwise, they make no sense!

Notice that the DAO hierarchy that all database programmers are used to has
deliberately been de-emphasized in the ADO model. This will give you much more flexibility
to reuse objects across various context boundaries. What does this mean exactly? Well, in
ADO you can create a single Command object. Then you can use it with more than one
Connection object.

Recall that the Connection object represents a connection to the underlying data
source. In our previous examples, we illustrated the connection object talking to our
Access Biblio.mdb data source. The Connection interface provides an Execute method. We
used this to process a SQL command via the connection.

We also mentioned that if the command generates rows, a default Recordset object will
be returned. However, if you need to use a more specialized or complex Recordset object
with data returned from the Connection, you should create the new Recordset object
(specifying the way you need it to behave), bind it to the Connection, and open the cursor
(more about cursors in a moment).

The Parameter Object

Another cool feature of the Command object is the use of a Parameter object collection.
This is used to hold command-specific parameters. The Parameter interface to the Command
Object (now that’s a mouthful!) represents a parameter of a command. So you can easily
create Parameter objects, and then add them to the parameter collection. Why? Well, this
really speeds things up.

What the Heck is a Cursor, Anyway?

No, this is not the little flashing mark that shows you where you are on the screen.
You can think of a cursor – in ADO terms – as another way of referring to a recordset. All
of the cursor information – what the underlying OLE DB/ODBC code retrieves from the data
source – is contained in the Recordset. The Recordset object is referred to as your
‘cursor of data’.

When we have been programming databases so far, we have tended to think of processing
our data in terms of a logical sequence of records. For example, we have written an
application that read through the records in the publishers table and displayed the name
in a grid. The application read through all of the records in a recordset and displayed
the name field from each record until it reached the end of the file (EOF).

When your database applications use queries to do data access, the "data"
that is returned is a query result set based on the SQL query statements. When we consider
the query result set, or recordset, we can’t think of it in terms of a "next
row" concept, as we can think about the rows in a spreadsheet. Nor is there any way
to operate on the individual recordset rows.

This scenario tends to be a bit awkward because most developers understand sequential
record-based retrieval – however they many times have no corresponding experience with
query result sets, our recordsets. While your query-based database application knows
typically what to expect in the recordset, it may need to do more processing. For example,
it may need to evaluate certain columns in selected rows to reach some sort of conclusion.

What is meant by this? Well, consider our application that retrieved all of the records
from the Publishers table but only displayed – say – 10 records at a time in the grid.
Such applications need some sort of mechanism to map one row (or a small block of 10 rows
simultaneously displayed in our grid) from the recordset set into the bound grid control.
How can the grid know which records to display out of the over 700 when only 10 are shown
at a single time? The user can scroll forwards, backwards, or jump to the end of the
recordset using the scroll bar. How can the program know which records are to be displayed
when it working on a recordset that has no concept of ‘next row’?

Enter the cursor. Cursors are animals that expose the entire recordset so that your
application can use rows in much the same way we would use records in a sequential file.
The following shows how a cursor ‘really’ makes rows available to your application. Let’s
say that our application issued the following SQL Query:

SELECT * FROM Publishers WHERE City = "New York"

Conceptually, our recordset looks like this:

We can see our records as if they were indeed sequential. This permits us to iterate
through the recordset and display the publisher’s name in a grid. We have been blissfully
unaware of the magic of cursors that makes this happen. Let’s take a look at what is
really going on beneath the smooth surface that is presented to us:

Notice that when our application issues the SQL query, the results are really returned
in no specific order. The records returned reflect the arbitrary order in which they exist
in the database – usually the order that they were entered in. However, they are presented
to us in a nice, sequential order. This is what a cursor does for us – it manages the
recordset. With our cursor, we can:

• Specify positioning at a specific row in the recordset
• Retrieve one row, or a block of rows to display in our grid, based on the current result set position
• Modify data in the row at the current position in the recordset

Notice that, as far as the user is concerned, the publisher records are appearing one
at a time. However, behind the scenes the application is using a scrollable cursor to
browse up and down through the recordset.

By using a read-only cursor, the user can browse through the recordset but not make
updates. A read/write cursor can implement changes to individual rows. More complex
cursors can be defined with keysets. These point back to base rows directly in a database
table.

Some cursors are read-only in a forward direction, which makes them very fast. They
don’t have to bother with handling the mechanics of moving backwards as well. These are
great for updating a read-only grid: we just loop through the recordset and display the
data. Other cursors can move back and forth in the recordset and provide a dynamic refresh
of the recordset based on changes other users happen to make to the database. Although
each cursor "library" uses a slightly different syntax and usually has somewhat
different approach to implement these things, they are all similar in most respects

But don’t worry. Cursors aren’t really as enigmatic as they might appear. Of course, we
have actually been using them all along – albeit unwittingly – every time we have written
a DAO or ADO program. These interfaces all use cursors in some form. Whenever our VB6.0
database application requires data access, we request a recordset to be opened. We really
received a type of cursor from the interface. These DAO or ADO interfaces can have their
own cursor library. Or they might use the cursors provided by the data source we are
accessing.

Luckily, as a user of a cursor, we don’t have to create the cursor directly. We are
really requesting it from some service provider, such as a relational database or a cursor
library.

So think of a cursor as being the manipulator of a set of data. This data is prepared
by a service such as Jet, and it uses the resources of the owner of the cursor. So a
cursor manages our data: it has the ability to retrieve a portion of that data in the
recordset. When the user us using our grid control to move from record to record, a
request is made by the user of the cursor to retrieve a piece of data. This, in cursor
lingo, is called scrolling.

While cursors were always with us when we were using DAO, we didn’t need to concern
ourselves with them. In fact, if you take a look at the intrinsic data control, a choice
of cursors is there as plain as day:

So cursors are the beasts that let you move from row to row and maintain the contents
of the grid as the user scrolls through the rows. Cursors come in many sizes and colors.
Take a look at the Data control property above. The DefaultCursor is always selected –
this is the most powerful cursor. It permits the user to scroll forward and backwards in
the recordset as well as update and delete records. This is the most expensive (in terms
of memory and speed) type of cursor. After all, the cursor has to be prepared for any
eventuality. Consequently, this default cursor can actually slow down your application.
Why? Because it has to keep track of all of the things we just talked about. This takes
time and memory.

With ADO, the understanding of cursors is extremely important. In fact, it is critical
in order to get our recordset to do the things we want. We need to open certain ADO
recordsets with specific types of cursors – in order to get the .RecordCount property, for
example. Well, every time a new Recordset object is created, a cursor is automatically
opened. But before we open a recordset, we can specify the type and location of cursor to
use when retrieving our results. The .CursorType property allows us to set a cursor as
forward-only, static, keyset-driven, or dynamic. Each type of cursor has its pros and
cons. In the next chapter we will cover each of these and why they are used.

This is very important because the type of cursor we use determines whether a Recordset
object can be scrolled forward/backward or updated. The type of cursor also affects who
can see changed records. Keep in mind that the most efficient cursor is used by default.
This is read forward only. If you only need to read the data, one time, and only move
forward, this cursor is for you and there is no need to change the default. But if you
need something a bit more robust, you need to bypass the default. Let’s summarize the
cursor options.

Types of Cursors

Cursors can be either updateable or non-updateable. If you only need to display
information and not diddle with it, the non-updateable is the fastest. The provider simply
passes you the data and forgets about it! There is no need to keep tabs on the data to see
if it has been changed. Therefore, this is the fastest cursor to use.

Scrollable cursors, which can be updateable or non-updateable, permit you to move
(scroll) back and forth in the recordset. If you only need to blast out some data to a
grid or HTML page, a non-scrollable cursor will give a performance boost. This is because
there is no need to track where you are in the recordset.

Keyset-Driven cursors take note of a key you may have in your database. Under normal
circumstances, when you request records from a data source, you get the entire package.
However, when a Keyset cursor is selected, only the keys are returned, giving a
performance boost. Then, when you scroll through the recordset, the data for the keys is
retrieved. However, when you first invoke the cursor, both keys and data are returned so
you can see something in your bound control. When you scroll, and the data is not in the
current batch of data, the Keyset fetches the next block. This way it only has to manage
small keys rather than large chunks of data.

Dynamic and Static cursors determine what data is available in the cursor at any point
in time. As the name implies, Static cursors only contain data that was placed in the
cursor when it was created. However, with a Dynamic cursor, any new records that are added
to the recordset are available. It’s like a living cursor.

We can use our old friend the Object Browser to see the cursor types available to us:

With ADO, we have the choice of four types of cursors. We can simply use constants to
tell the recordset which to use:

We can also tell the recordset how to lock our data while it’s being manipulated via
the cursor:

The syntax for opening an ADO recordset is like this:

adoRecordset.Open Source, ActiveConnection, CursorType, LockType, Options

The Source argument is Optional. This is a valid command object variable name, or it
might be an SQL statement, a table name, or a stored procedure call.

The ActiveConnection is also optional. This is either a valid Connection object
variable name, or a String that contains our ConnectionString

The CursorType is also Optional. This is simply one of the cursor constants that tells
the provider the type of cursor to use when opening the Recordset.

ConnectionString Options

There are several other options that we can provide the recordset as well. You may
recall that when we build the connect string in our ADO data control, these options were
provided in a drop down box. They may have looked a bit cryptic back then, but now we can
see what they mean:

After the initial quantity of records specified in CacheSize is fetched, any remaining
rows should be fetched asynchronously: adFetchAsync

When might you use some of these options? Well, a good illustration would be when using
a transaction.

Transactions and You

Transactions are useful or even necessary when you need to make several changes at
once. Think of a transaction as a logical unit of work. And if any part of the transaction
fails, the whole thing is rolled back (i.e. any changes made since the beginning of the
transaction are undone). The example often used in computer books is one drawn from
banking. For example, let’s say that you go to the bank to pay a bill. The funds are drawn
from your account and placed in the electric company’s account. So two things happen.
First the funds are debited from your account and second, the funds are credited to the
electric company’s account. If the funds are not drawn from your account but are credited
to the electric company, you are happy but the bank is not. If the funds are drawn from
your account but not placed in the electric company’s account, you are mad and the
electric company is not that happy either. So both parts of the transaction must occur
properly (i.e., the transaction is committed) or both part of the transaction are not
executed (i.e., the transaction is rolled back).

Computer scientists use the acronym ACID to define the characteristics of a
transaction.

Atomicity – Although the changes may include several records, if anything fails the
entire transaction fails. The system goes back to a pre-transaction state.

Consistency -Although A transaction never leaves the database in an inconsistent state.
If a change is made, it can be undone if the transaction fails at a later point.

Isolation – Although A transaction behaves as if it were in complete isolation from
other transactions in the system.

Durability – Once a transaction’s changes are committed, they persist beyond any system
failure. If the system crashes (or GPF’s!) after the transaction is committed, the
transaction’s results are still maintained.

Let’s look at an ADO example of a transaction that uses a few of the optional constants
described above for opening our recordset. In this example, we examine the syntax for
using a transaction. We just append a "" to each of the more than 8,500 titles
in the Titles table. But if there is an error anywhere, the entire enchilada is rolled
back and the original table is returned to its fresh as the new-driven snow state (mmm,
enchilada’s in the snow!). The transaction is sandwiched between the .BeginTrans and
.CommitTrans method calls on the connection object:

Dim myConnection As ADODB.Connection
Dim myRecordset As ADODB.Recordset

Set myConnection = New ADODB.Connection
Set myRecordset = New ADODB.Recordset

myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data
Source=c:BegDBBiblio.mdb"

'-Open the connection --
myConnection.Open

'Determine if we conected.
If myConnection.State = adStateOpen Then

myRecordset.Open "SELECT * FROM TITLES", myConnection, _
adOpenDynamic, adLockOptimistic, adCmdTable
Else
MsgBox "The connection could not be made."
myConnection.Close
Exit Sub
End If

'-just to be sure --
myRecordset.MoveFirst

On Error GoTo transError

'-here is the top of the transaction sandwich --
myConnection.BeginTrans

While Not myRecordset.EOF
mcounter = mcounter + 1
myRecordset!Title = myRecordset!Title & "" 'so we don't really change it
myRecordset.Update
myRecordset.MoveNext
Wend

'-if we got here ok, then everything is written at once
myConnection.CommitTrans
myRecordset.Close
myConnection.Close

Exit Sub

transError:
    myConnection.RollBack
    myRecordset.Close
myConnection.Close
    MsgBox Err.Description

How It Works

After the connection is established, this example begins a transaction. The data
changed in this transaction is either all committed at the end of the transaction, or it
is all rolled back to the pre-transaction state. Let’s take a look at the important parts
of the code.

After we dim and initialize our connection and recordset objects, we build our
connection string as we have been doing so far. The connection is then opened by invoking
the .Open method of the connection object:

myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51; _
  Data Source=c:BegDBBiblio.mdb"

'-Open the connection --
myConnection.Open

Then, as a precaution, we check out the .State property of the connection. Recall the
general syntax of opening an ADO recordset:

adoRecordset.Open Source, ActiveConnection, CursorType, LockType, Options

Well, in our example, if the connection is open, we pass the .Open method of the
recordset an SQL query string as the Source. It is requesting all of the records for the
Titles table as the first parameter. Next, we pass in the ActiveConnection connection
object, myConnection. Next, we specify the CursorType as an adOpenDynamic cursor for the
recordset. This type of cursor permits all movement backwards and forwards, allows the
user to make changes to the current record, and even will dynamically update if another
user updates any records in the database that are included in our recordset. We will
actually see any additions or deletions as they occur by others! We then specify the
LockType as adLockOptimistic. This locks record by record and only occurs when the .Update
method is called. Since we know this is a table, we pass in the Options parameter as
adCmdTable. Now ADO does not have to spend time figuring out what it is going after. We
have fully specified the type of recordset we want by passing each parameter.

'Determine if we connected.
If myConnection.State = adStateOpen Then

myRecordset.Open "SELECT * FROM TITLES", myConnection, _
adOpenDynamic, adLockOptimistic,adCmdTable
Else
MsgBox "The connection could not be made."
myConnection.Close
Exit Sub
End If

Once our recordset is opened, we want to perform an edit / update on each record in the
recordset. Edit / Update is very expensive in terms of processing time. So we sandwich our
edit / updates inside a transaction. When the .BeginTrans method is called, everything
until the .Commit or .RollBack method of the connection object is reached:

'-here is the top of the transaction sandwich --
myConnection.BeginTrans

Now we do our processing. We loop through the recordset as we usually would. With ADO,
no .Edit method is required as it is in DAO. Remember when we discussed ADO being slimmed
down by having many redundant methods removed? Well, this is one of them. By simply
changing the data, an Edit is assumed. So an Edit still occurs, but it is implicit. We
don’t have to explicitly call the method. So we modify (edit) each record and then call
the .Update method. However, since we are in the transaction sandwich, the results are
written to a temporary file. They are not yet committed to the database:

While Not myRecordset.EOF
mcounter = mcounter + 1
myRecordset!Title = myRecordset!Title & ""
'so we don't really change it
myRecordset.Update
myRecordset.MoveNext
Wend

If everything goes as planned, when we exit the loop we then call the .CommitTrans
method of the connection object. Now, all of the changes are written to the data source at
one time. So we are not hitting the disk for each record, writing the changes, then moving
to the next record. The changes are kept, where possible, in memory and then blasted all
at once to the data source. So instead of doing – say – 700 writes (one for each record),
we only do a single bulk write which is much, much faster.

As I said, this technique can be used when you are not ‘technically’ performing a
transaction. In other words, when you are not hitting two tables that require both to be
changed simultaneously. When you only need to update a single table as in our simple
example, this will be orders of magnitude faster than updating each individual record.
Then we close the recordset and the connection when we are finished and exit the sub:

'-if we got here ok, then everything is written at once
myConnection.CommitTrans
myRecordset.Close
myConnection.Close

If we were going to perform the same task on several records such as appending an
"" to each title, it would be much faster to just use the .Execute method and
use SQL. But we wanted to show an example of the transaction method that you might be able
to use in your every day programming.

If we run into a snag, an error is generated. Since we have an active error handler,
our VB code jumps to the transError label, which is the start of the error handler. Here
we roll back everything that has occurred up to this point. The .RollBack method will
bring the system back to the point of the .BeginTrans method. None of the records in the
transaction sandwich will be changed. We then close the recordset and connection and
display a description of the error from the global Err error object:

transError:
myConnection.RollBack
myRecordset.Close
myConnection.Close
MsgBox Err.Description

Using Transactions in Everyday Life

Now this is a cool tip. You can use transactions whenever you have to update several
records in a coordinated manner. VB keeps as much in a cache as possible to reduce costly
disk writing activity. With a transaction, everything gets written at once, instead of on
every .Update method. The speed benefit can be enormous. I use this whenever possible when
adding or editing records in a large recordset.

One caveat. If you attempt to do this with the ODBC connection we established earlier,
you get the friendly message shown below:

Why? Because the ODBC driver we used does not support transactions. The moral of the
story is: know your data source capabilities.

We covered a lot of ground in this chapter. We noted how ADO really evolved from DAO’s
inability to retrieve non-relational data. We saw how the need to access non-relational
data was largely driven by the rise of the Internet, and by the problems of accessing the
wealth of legacy data stored in a variety of formats.

We noted how the move to ADO was really evolutionary, not revolutionary. ODBC was a
first attempt to permit a single interface to work with many database providers products.
But ODBC, while still powerful, was limited to relational data. ADO breaks these bounds
and allows us to access everything we could get hold of with ODBC, plus much more.

The basic way to retrieve an ADO recordset is by first opening a connection to the
database. We examined the ins and outs of a connection string and even built our own Data
Source Name (DSN), and we opened an ADO connection using that.

Next we used ADO to examine the data provider’s tables and fields by using the
.OpenSchema method. This permitted us to see exactly how the data source was made up. We
then went on to look at the Errors collection and how it is used in the ADO object model.

Moving on to cursors, we caught a glimpse of what really happens under the hood when
data is returned to us in a recordset. An example was shown opening a recordset with a
specific cursor.

We then ended up with transactions and discussed how they can not only help when we
need to update several tables simultaneously, but can also really speed up our code when
we have more ‘garden variety’ tasks such as updating several records.

What we Learned

• Programming ADO is very similar to DAO
• We program to ADO, but it is the OLE DB provider that does the real work of taking the ADO commands and translating them to work with the data source
• The Connection Wizard can be used to seamlessly create the connection string
• Once we are connected to an OLE DB data source, we can access data almost exactly like DAO
• Declaring an object variable implicitly or explicitly requires us to handle the variable differently when bringing it to life
• Transactions are not only useful when working with several recordsets, but can speed up everyday data access tasks

And now we will move on to another cool use of ADO – building our own bound ADO ActiveX
control.