Microsoft & .NET Visual Basic Book Review: Beginning Visual Basic 6 Databases

Book Review: Beginning Visual Basic 6 Databases

In this chapter we’re going to start getting to grips with what will definitely be the
database access technology of the future – ADO. We’ll look quickly at the historical
background to the development of ADO before moving on to discuss more recent developments
in data access technologies. Then we will begin exploring the concepts and components of
ADO itself before moving on to create our first pieces of code that will program ADO for
us. There’s a lot of ground to cover – as always in this exciting field – so let’s get
going!

What’s the Background to ADO?

In the early days of computing, dumb terminals were wired to powerful mainframe
computers. The centralized Information Services (IS) department of a company ran the
computing show. The mainframe gurus told us what we could and could not do. Then in August
of 1981, the first IBM personal computer was released and the world changed. Control was
eventually wrested from the centralized IS department at companies and flowed to the every
individual with a personal computer.

Each personal computer had its own CPU and hard drive to run programs and store data.
Centralized computing for many day to day activities disintegrated and each person with a
personal computer took individual control of their data destiny. There was talk of big
iron mainframes going the way of the dinosaur – who needed them? We had just as much
processing power on our desktops. Life was good.

But there were problems too. For example, lots of individual computers sitting on
people’s desks, and all wanting to share information and common data. Out of the many
desktop solutions to this need to access data by distributed computing stations was Data
Access Objects (DAO). We’ve already learned about (DAO) and how easy it is to create
desktop and file server database programs.

Now, with VB6.0, a brand new Jet database engine 3.51 just made this solution stronger.
In fact, Jet 3.51 is faster and more robust than Jet 3.5 that shipped with VB 5.0.
Microsoft wanted to enhance a proven, strong database access solution and, when developing
desktop database solutions using .mdb or ISAM files, Microsoft says the combination of Jet
and DAO is definitely the way to go. Microsoft has upgraded DAO and will continue to
support this approach for the foreseeable future. And don’t forget, DAO is the most
popular desktop database access method around! So the installed base of solid, robust
applications using DAO is tremendous.

But DAO’s days are numbered. It is a technology that will not be enhanced any further.
So we programmers must learn ADO because that is the future for us. And while ADO is
relatively new, VB6.0 has provided us tools to hit the ground running.

We have seen two needs of modern business rapidly emerge that require a new and more
sophisticated approach to gathering data. The first need is that of accessing legacy data
– that is, information that is stored around the business enterprise in disparate forms in
various types of computers. Companies like IBM and Oracle suggest that the solution is to
move everything into a single database structure. Well, if the world stood still, this
would still be problematical at best The second need is that of accessing non-relational
data. With the advent of businesses’ use of the Internet and corporate Intranets, there is
a need to get information from e-mail, HTML pages, and even video! Clearly DAO is not up
to this job.

So while other companies talk about trying to marshal all of the information stored in
disparate formats in to a single standardized database structure, Microsoft has approached
this problem from the other end of the spectrum. Their strategy is to access the data
exactly where it is. Using what is known as Universal Data Access (UDA), there is no need
to change or modify anything on the data side. Using a single data access model, the
programmer can use the same code to access essentially any data, anywhere, at any time. If
this sounds too good to be true, I think you will be pleasantly surprised at how easy VB
6.0 has made accessing data using the UDA strategy. Let’s take a closer look.

The Quest for Data

While .mdb databases (native to the Access database system) are easy to access (no pun
intended. None taken, I’m sure) there are times when programmers need to get data from
other desktop sources. For example, we might need to read or write data from dBASE,
Paradox, FoxPro, or other databases. We might also need to retrieve information from Excel
or Lotus spreadsheets, or even text files. If you take a look at the intrinsic (built-in)
data control in VB 6.0, you will notice that there are several additional data sources
that the control can talk to in addition to it’s native Access:

The real benefit of connecting to one of the external ISAM (Indexed Sequential Access
Method) file types listed in the connect property of the data control is that we can work
on the data – as is – without changing its structure. We can leave the data where it is
and use VB6.0 to connect with the various data source types. So any applications that
created these files can continue to operate unchanged. We just go in and read or write
data to and from these sources.

For example, there might be cases where an ASCII file is downloaded from a legacy
mainframe system that we need to retrieve. Or possibly, several departments create Excel
spreadsheets that track customer orders. We can simply connect to them, extract the data,
and consolidate the information in an Access table.

Another probable scenario is that we want to get all of the data in an older Paradox
system into a newest Access table. In a single VB6.0 application we can read the Paradox
data and write it to an Access table in one fell swoop. I have done that several times and
am still amazed at how easy the data control makes this. In many ways, it is transparent
to the programmer that VB6.0, in conjunction with the data control, is talking to both
Access and Paradox at the same time.

Jet performs this seemingly magical task by using various .DLL files called ISAM
drivers. Indexed Sequential Access Method drivers are .DLL files that contain the specific
code to talk to the various data sources. Jet supports several data access connections:

Data Source Version supported by Jet
Access All versions

Paradox 3.x, 4.x, 5.x
DBASE III, IV, 5.0
Excel 3,4,5, and 8
FoxPro 2, 2.5, 2.6 and 3.0
Lotus 1-2-3 wk1, wk3, and wk4
Text Any fixed or delimited text file

All data access from the data control, including Access itself, is handled under the
hood with installable ISAM drivers. We just program the data control, and it handles the
translation to the other data sources. We didn’t bother with setting the DataSource on the
data control we have been using in our programs so far. If you don’t specify a specific
connection, the default connection is to Access.

We mentioned earlier in the book that there are not only different formats of data, but
there are even variations in the way tables themselves are laid out. Access, for example,
uses the notion of a container. There is a single .mdb database that contains all of the
tables within it. Paradox, however, has a separate file for every table. So if a database
in Access has 10 tables, there will be a single .mdb file that contains 10 tables.
Paradox, on the other hand, will have 10 separate independent tables.

As we mentioned earlier, DAO is the interface to the Jet database engine. The DAO/Jet
combination is primarily designed to access ISAM databases as it supports most of the
common ISAM data access techniques.  DAO/Jet is still the solution to use when it
comes to accessing native Jet (.mdb) or ISAM data sources such as Btrieve, FoxPro,
Paradox, and dBase.

In addition to the ISAM .DLL files that contain the code to handle specific data
sources, as listed above, there is another more generalized method available to you.
Microsoft’s standard for providing data access to various data sources is Open Database
Connectivity (ODBC). Essentially, this is a SQL approach to retrieving data. ODBC is
supported by all sorts of software applications from spreadsheets to word processors to
databases. ODBC provides database interoperability, which really means that it gives us
methods by which data can be exchanged among different databases. So if an ISAM driver is
not available, it is possible to communicate with a data source if it understands ODBC.
Starting with DAO 3.5, an important enhancement was added – ODBC Direct. Selecting this
option completely bypasses Jet and can really speed things up.

Using ODBCDirect allows you to deploy client/server applications using Microsoft Access
without using Microsoft Jet as the middle layer. ODBCDirect is an interface directly to
ODBC. So in most cases, it is faster.

If your application is hitting a Microsoft Jet .mdb or any other file-share databases
it supports, you should use the Microsoft Jet path. This is because ODBCDirect was created
specifically to access remote data. You should also use Jet if you want to join data in
tables stored in different back-ends such as Oracle and SQL Server. You would need Jet in
this case because it provides heterogeneous joins. You can create tables using ODBCDirect
by executing SQL statements, but it’s more convenient to use the Jet TableDef object we
saw in the last chapter. So by now you are probably wondering why would you ever use
ODBCDirect anyway? Good question. Most client/server developers simply need to quickly
read data in, change it, and write it back to the back end. And if they need to do this
quickly, then ODBCDirect is the way to go. It will make your code faster because it gives
you direct access to the ODBC data source. It does not require loading the Jet engine, so
this uses far fewer resources on the client side. This approach makes the ODBC server
responsible for all query processing.

But the world does not stand still and the rate of change has only accelerated in the
past 18 months, and it’s the Internet that has been driving this change like no other
technology ever seen before. With the advent of the Internet in everyone’s lives, a
mechanism was needed to easily send information across the Internet from host servers to
browser-based clients. For example, companies are rushing to build database solutions to
distribute information not only across the enterprise, but across the globe. A client in
England needs to get product information on the new camping gear from a supplier in
Washington State. The tyrant of geography is no more – the Internet is changing the way we
live.

Consider the ubiquitous Web browser, such as the Microsoft Internet Explorer. The
browser is of course a computer program. Not only that, but the web browser is the most
widely distributed and used computer program in history. More computers of all stripes run
a browser than any other type of application. Since the browser application is a client,
it gets served data from a server computer somewhere in the world. And the browser client,
since it is a computer program, can take the data it is served and do things with it.

For example, a simple text file formatted using Hyper Text Markup Language (HTML) tags
can be rendered perfectly in any browser running on an IBM compatible, MAC, Sun, or any
other type of computer. Since the browser can render HTML pages on any computer, the
server simply serves the HTML file and it’s the individual browser’s responsibility to
format and render the output. So the server doesn’t know or care what type of browser is
receiving the data – it just serves it up.

Back to the Future

The business world has discovered the Internet in a big way. The Internet was a 25-year
overnight success. Even though it has been around since the late 1960’s, it wasn’t until
the mid 90s, with the advent of the graphical web browser, that the Internet took off like
a rocket. Since then, businesses started scrambling for ways to send database information
around the globe from servers to browser clients. Wait a minute! A centralized server
sending data to a client connected to it? This sounds like the 60’s all over again, right?
Centralized main frame computers talking to light clients. The world is migrating to
mainframe servers serving client browsers connected to them. Information centralized on
mainframe servers. Hmmm. Where have we heard this before?

So now programmers need to not only access relational data sources, but non-relational
data as well. As we mentioned, Microsoft’s approach is to provide a common method to get
at data stored in various formats. They think it makes sense to focus on the access to the
data rather than to the physical layout of the database itself. After all, what if we need
to get at data in a relational database, a legacy system, an Excel spreadsheet, a web
site, some text files, and e-mail? And what if these are stored in various locations?
Rather than change the world to conform to a single data structure, we want to change the
way we retrieve data stored in various structures. Makes sense.

As programmers at the dawn of new millennium, our problem is one of data access. We
have been using Data Access Objects (DAO) in the book both to program our database and to
create new tables. But if there is a need to access data sources other than Microsoft
Access, the enterprise edition of Visual Basic 6.0 provides Remote Data Objects (RDO). RDO
permits Visual Basic programmers to work with relational ODBC data sources. And DAO/Jet is
used when we want to work with Jet and ISAM data sources. So traditionally, if we needed
to work with remote ODBC data sources we would select RDO. However, when we need to access
ISAM or Jet data sources, then DAO is the clear choice. But now Microsoft is offering us a
new and much more efficient approach that permits us to use a single high level, efficient
programming paradigm to work with everything. It’s called Universal Data Access (UDA).

Universal Data Access is Microsoft’s high-performance solution to access a variety of
information sources, including relational and non-relational data sources. UDA is an easy
to use programming interface – it is a tool and it is language independent. In other
words, UDA is really a bundle of technologies that enable us to integrate diverse data
sources, relational and otherwise. These tools permit companies to create easy-to-maintain
solutions, and take their pick of best of breed tools and application programs.

Universal Data Access does not require the expensive and time-consuming (and many times
impractical) shuttling of data from various databases into a single data store. Also,
companies are not required to commit to a single vendor’s products – pretty attractive for
any IS department. Universal Data Access is based on open industry specifications: it
enjoys broad industry support and currently works with all major established database
platforms.

The way to employ UDA is by using ActiveX Data Objects. You can see that by using ADO,
we can access any data either by using tried and true ODBC or an OLE DB providers:

So you can see that our Visual Basic 6.0 application can use DAO as we have been doing
up till now. DAO can access Jet directly as we have been doing. This gives us the
interface into Access, Paradox, dBase, Excel, FoxPro, Lotus 1-2-3, and text files. We can
also use ODBC to talk to any SQL compliant data source. This can be accomplished either
through Jet, or ODBCDirect that bypasses the Jet engine. But consider the multitude of
steps that must be gone through under the hood.

Now, by using ADO, we can simply use the OLE DB provider for the specific data source
and voila! – we have connected. However, we can still employ the OLE DB provider for ODBC
to use ADO for communicating with any ODBC sources. So using ADO, we get everything we got
with DAO, and more. And, it is actually easier to use. Life is good.

Universal Data Access is really an evolutionary step from today’s standard data
interfaces we have discussed. We know about the alphabet soup of ODBC, RDO, and DAO. UDA
is a step to extend the functionality of these well-known and solid technologies. The
bundle of technologies that make up UDA consist of ActiveX Data Objects (ADO), Remote Data
Services, (RDS, formerly known as Advanced Database Connector or ADC), OLE DB, and Open
Database Connectivity (ODBC). Together, these interfaces provide us the means to work with
just about any data source. And together they are known as

Universal Data Access.

So let’s start looking in detail at ADO.

Say Hello to ActiveX Data Objects – ADO

Both RDO (which is used, remember, for sending data over a network) and DAO (for
desktop solutions) are relatively robust and mature technologies. So Microsoft decided to
create a universal method of accessing Data that encompasses all of the functionality of
both in a single interface.

With the Internet changing the way people handle data, not only do programmers need to
access relational data sources, but also non-relational data such as hyper text markup
language (HTML), mail, video, text, legacy system data, and just about anything else you
can imagine. So over the next 18 months or so, Active Data Objects (ADO) will emerge as
the single, unified alternative that will replace the current alphabet soup of data access
choices. Programmers will write code that conforms to ADO and the rest of the data access
will be handled under the hood. It sounds magical, doesn’t it? Well, I think those people
up in Redmond really are wizards.

The cool thing about ADO is that it not only provides us a consistent interface but
also gives us high- performance access to just about any source of data. So whether you
need to create a front end to a local database, or a middle tier that contains business
objects, or even get data from an Internet browser, ADO is the single data interface you
will need to use for your solution. Sounds almost too good to be true, don’t you think?
Well, stay tuned and let’s see how it’s done.

The latest technology that performs this magic is OLE DB. OLE DB is designed to provide
universal access to several relational and non-relational data sources. We will
communicate with OLE DB using Active Data Objects. By using ADO in conjunction with OLE
DB, we can talk to Access, Oracle, SQL Server, or any other data source by simply using
the ADO object model.

To the VB6.0 database programmer, ADO is the interface we need to understand. Take a
look at this figure:

Notice that this object model is much ‘flatter’ than the previous diagram. ADO and UDA
are all about simplicity.

You can see that by using ADO from either a web browser or a Visual Basic 6.0
application, we can talk to just about any data source. OLE DB handles the grunt work out
of our sight to make all of this magic work. And best of all, ADO is actually easier to
work with than DAO! As we mentioned, Microsoft has indicated that DAO and RDO will
eventually be replaced with ADO. So it does make sense to start learning it now. OLE DB
will now handle working with the standard relational data and non-relational data from
just about anywhere on the planet.

Let’s take a closer look at ADO. We’ll begin by making sure that VB knows all about
ADO.

Try It Out – Telling VB About ADO

1    Start a new project called Chapter11prjfirstADO. Now go into the
Project | References dialog and add the Microsoft ActiveX Data Objects 2.0 Library and
ActiveX Data Objects Recordset 2.0 Library references to your project.

Now VB 6.0 knows about the ADO components we want to use.

Then right click on your tool palette and select Components. Select the Microsoft ADO
Data Control 6.0 (OLEDB):

Click OK. This will add an ADO data control to your palette.

2    Name the default form in the project frmADO. Draw an ADO Data
Control (ADODC) on the form. Next, draw a textbox and label as shown on the form as well.
We are going to create a simple bound text box program like our first data control
program. And we will use the label to show where we are in the recordset.

In order to hook up the ADODC to our Biblio.mdb database, we must first set some
properties. We did this a bit earlier in the book, remember?

Right click on the ADODC and select ADODC Properties. This will bring up a Property
Page dialog box for the control. The first thing we must do is tell the control some
important information. Unlike the singular DatabaseName property we need to set on the
standard data control, the ADO data control requires a connection string. The connection
string consists of the specific OLE DB provider to use, as well as the data source we want
to access. The connection string is the critical piece of information the ADODC control
needs to find the data source. Let’s take just a minute to review the connection string,
because we will be using them for the rest of the examples in this chapter, and though the
rest of the book.

If you haven’t already done so, right click on the ADODC control and select ADODC
Properties.
We are presented with the property page for the ADODC control. Since setting up the
ConnectionString must contain just about every piece of information required to connect to
our data source, this comes in very handy indeed! Recall that the connection string needs
to know things like the location and name of the database, any passwords that might be
required, and the OLE DB data provider.

Click the Build button and let’s step through the process.

We are presented with another set of property pages for the Data Link. Notice the list
of OLE DB Providers that are shipped with VB6.0. If we wanted to connect to a generic ODBC
source, we have a provider for ODBC Drivers. Notice that we have providers for Oracle and
SQL Server. And as time goes on, all of the major database providers will ship their own
OLE DB providers. This way, ADO can talk directly to the specific provider, just as DAO
can now talk to ODBC.

Select the Microsoft Jet 3.51 OLE DB Provider.

Click the Next>> button. This brings up the Connection tab. Here is where we must
tell VB the location and name of the database we will be using. Click the button with the
ellipsis and locate the usual BegDBBiblio.mdb database. Since the database does not
require a password, don’t change the entries for logging on to the database. As you’ll
recall, Admin is the default user name for Access databases:

always is a good idea to use the Test Connection option. This way, if there was
something wrong with the location or name of the database, we would get an error advising
us of this. Let’s say that you entered the name of the database but forgot to add the .mdb
extension. By testing the Data Link, we would know immediately:

We can then correct the error in the name and location of the database and press Test
Connection once again.

There, that’s better. Now click the Advanced tab just to see what options are available
to us. Leave the default Share Deny None (if you needed to open the database in a
read-only, exclusive mode you would check the Read box):

Now click the All tab. Here you can see all of the information the Data Link property
box garnered for us:

This is all of the information that will be used to create the connection string. If
you need to modify any of the properties, simply click the Edit
Value…button.Highlight any value you wish to edit and press the Edit Value button.
This will give you a chance to modify any value in the connect string prior to clicking
the OK button.

After the connection string is built, click OK to dismiss the property pages for the
Data Link. Now the control has the information it needs to connect to the data source.
However, we still need to inform the data control which table(s) we wish to access. Right
click on the ADODC data control again and select ADODC Properties. Notice that the
connection string text box is now filled in:

Click on the RecordSource tab and click the drop down list box for the Command Type:

Select 2 – adCmdTable. Now the control knows we want to access records from a table
directly. If the DataSource is not known in advance, then adCmdUnknown is selected. If we
were going to issue a SQL command, then adCmdText would be selected, and the bottom text
box, Command Text (SQL), would become enabled. Finally, if we have stored, pre-compiled
procedures, we would choose adCmdStoredProc. This time, be sure to select choice 2 –
adCmdTable.
Now the control knows that we want to access records from a table, and it knows the name
of database from when we set up the Data Link. Now, the Table or Stored Procedure Name
listbox becomes enabled. Click the listbox and all of the tables in the database are
shown:

Select the Publishers table and click OK.

The data control now has the connection string built, and will be able to retrieve a
recordset for us from the data source. Double-click on the data control to bring up the
code window. You might notice that the Adodc1 data control has a few new event procedures.
And many more parameters are passed in by VB so we can really know what is going on.

3    Now that the ADODC data control has been set up, let’s bind the
Text1 textbox. Bring up the property dialog box for Text1. Set the DataSource property to
Adodc1.

Now click the drop down box for the DataField. Notice that just like the DAO data
control, all of the valid fields are displayed:

Select the Name field.

4 Now double click on the ADODC data control to bring up the code window. In the
MoveComplete event procedure of the control, add the following highlighted code:

Private Sub Adodc1_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError
As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

Label1 = "Record " & Adodc1.Recordset.AbsolutePosition _
& " of " & Adodc1.Recordset.RecordCount

End Sub

The MoveComplete event of the ADODC control will fire when the control navigates to a
new record. So this is the perfect place to update our label, lablel1, to show what record
is the current record in the recordset managed by the ADODC control. Of course, we read
the .AbsolutePostion property to tell us what record we are on and then read the
.RecordCount to inform us how many records are in the recordset.

Remember way back when we noted that when the form’s Activate event procedure is fired,
we can be sure that all of the visible components (like the ADO data control) are fully
initialized and displayed? Let’s place a line of code that will display the current record
when the form is fully loaded.

5    Add the following code to the frmADO form’s Form_Activate event.
When this event fires, we know the ADODC data control has been completely initialized so
it is safe to read the properties.

Private Sub Form_Activate()
Label1 = "Record " & Adodc1.Recordset.AbsolutePosition _
& " of " & Adodc1.Recordset.RecordCount
End Sub

6    Go ahead and run the program. You can see that it
works as advertised:

To the user, there is absolutely no difference between the intrinsic data
control and our new ADO data control. See – I told you that the transition would be
painless.

How It Works

Well, no magic here. We just used the same techniques we used earlier for
the DAO data control. The only twist here was setting up the connection string. As you can
see, this is a bit more involved. But the connection string provides a generalized method
to encapsulate all of the information required to talk to the OLE DB provider, locate the
database, and provide password information. But once the string was built, the rest of the
code was pretty much the same. As the user navigates the recordset with the ADODC control,
we update the label.

Let’s take a quick look at the properties of an ADO data control and a
standard data control. Bring up the property window for the ADODC data control:

Notice that with the ADO data control, we have the connection string that
tells the control which OLE DB provider to use. This string contains the fully qualified
name and location of the database to open. And then the .RecordSource property tells the
control which table to open – in our case Publishers. And since we want to open a table,
instead of a dynaset from a join, the .CommandType of adCmdTable is selected. Take another
look at the ConnectionString – notice that the OLEDB 3.51 provider is listed.
The DAO and ADO Data Control Properties Compared
Let’ have a look back at the DAO data control’s properties by way of comparison with ADO:

The standard DAO data control shown here uses the .DatabaseName property
to reference the fully qualified name and location of the database. The .Connect property
tells the control to use the installable ISAM file for connecting with Access (remember
that there are options here for Paradox. FoxPro, etc.) And the .RecordSource property is
the same. We selected Publishers. Notice that the .RecordsetType is 0 – Table. So we
include the exact same information to the control, but it is located in different
properties. But the ADO data control can do so much more with the same information.

Remember when we examined the DAO object model? It was quite a bit more
involved than the ADO object model. We’ll take a look at the ADO object model, then write
a quick program that examines the various parts of the structure. Here’s a graphic
summarizing the model:

Here’s how what we do with our code relates to the figure: Once we open a
Connection to the database, we will then create a Recordset with that connection. From the
Recordset, we will access the Fields Collection and, through it, the fields of the
database.

We will write a simple program that shows each of these components of the
ADO object model. But let’s review the model first. This way, we can refer to the model as
we write our program.

Step 1 – The Connection Object

The Connection object allows us to establish connection sessions with data sources. In
other words, the connection represents a physical connection to a data source. The
connection we set up provides a mechanism for initializing and establishing the
connection. Once the connection object is created we can use its methods and properties to
use the data source (in this case the database) that we have connected to. We will also
use the connection for executing queries and using transactions (we’ll talk about these in
detail later). The underlying OLE DB provider is used for connecting to the data source.

Let’s briefly summarize the methods of the connection object:

Connection Object Methods Description
Open Opens a new connection to a data source
Close Closes a connection as well as any dependant objects
Execute Executes a query, typically an SQL statement
BeginTrans Starts a new transaction
CommitTrans Commits changes made during the transaction
RollBackTrans Cancels any changes done during a transaction

Let’s And here are the connection object’s properties:

Connection Object Properties Description
ConnectionString Contains the necessary information to
establish a connection
ConnectionTimeOut Determines how long to wait to establish
a connection
CommandTimeOut Determines how long to wait while
executing a command
State Indicates if a connection is open
Provider Indicates the name of the provider
Version Indicates the ADO version
CursorLocation Sets/returns the location of the cursor
engine

As we mentioned, the connection object allows us to establish sessions with data
sources. But remember, behind the scenes the underlying OLE DB provider is actually used
for connecting to the data source. That is how the magic is accomplished.

We program to the ADO interface. But remember when we selected the OLE DB provider when
we selected "Build…" next to the Connection String option? It is the OLE DB
provider that knows how to take commands from ADO and translate them into language that
the specific data source will understand. Makes sense now, doesn’t it? And as more and
more suppliers create OLE DB providers for their data sources, ADO will take over the
world.

Step 2 – Opening a Recordset

Once the connection is opened, we can then retrieve a recordset from it. We use the
Open method of a recordset object to open a recordset. Using the Open method establishes
the physical connection to a data source and opens a recordset that represents records
from a base table or the results of a query. Once we have the recordset, we can access the
Fields Collection, just as if we’re operating with the DAO recordset. Let’s see how these
pieces fit together.

The Connection and Recordset objects are the key components we will use for
manipulating data. A VB6.0 application can use the connection object to establish
connections with the database server. The Command object is used to issue commands, such
as queries, updates, and so on to the database. And we use the Recordset object to view
and manipulate the returned data, as we’ll do when we update a listbox in a simple program
we will write later.

The language used with the command object is dependent on the underlying provider for
the database. But for our purposes, we will be using relational databases where the
command language is generally SQL. You might want to refer back to Chapter 7 for an SQL
refresher.

We are already familiar with programming DAO. But you can access more data sources with
ADO, and I think you will be pleasantly surprised to find out that programming with ADO is
actually simpler! You don’t believe me? Let’s give it a try. We’ll start off with a simple
ADO program that just fills a listbox with database data for us.

Try It Out – Programming ADO

In this exercise, we will open an ADODB connection and an ADO recordset. When these
tasks have been completed, we will populate the listbox with the names in the Publishers
table.

1.  Add a new form to your project and name it frmADOPublishers. Be sure that you
have selected Project | References and include the ActiveX Data Objects 2.0 Library in
your project.  Then add a listbox and a command button as shown below:

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

Private Sub Command1_Click()

Dim adoConnection As ADODB.Connection
Dim adoRecordset As ADODB.Recordset
Dim connectString As String

'-Create a new connection --
Set adoConnection = New ADODB.Connection
'-Create a new recordset --
Set adoRecordset = New ADODB.Recordset

'-Build our connection string to use when we open the connection --
connectString = "Provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data Source=C:BegdbBiblio.mdb"

adoConnection.Open connectString
adoRecordset.Open "Publishers", adoConnection

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

adoRecordset.Close
adoConnection.Close
set adoRecordset = nothing
set adoConnection = nothing

End Sub

1 Select Project | Project1.Properties… and add frmADOPublishers as the Startup
Object.
2 Run the program, and click the Fill List button

By pressing the Fill List button, we call upon ADO to open a data source and then use
code to iterate through the recordset and fill up the listbox. Pretty straightforward, eh?

How It Works

Let’s take a closer look at what we have just done.

Notice that we need to dim an ADODB.Connection object.

Dim adoConnection As ADODB.Connection

The connection object allows you to establish connection sessions with data sources,
and this object provides a mechanism for initializing and establishing the connection to
our data source. Remember the connection string we created with our ADO data control? We
will pass the connection object the same kind of string.

Once our connection is open, we want to create a recordset. Just like our DAO
counterpart, we dim an ADODB recordset.

Dim adoRecordset As ADODB.Recordset

First of all, since our VB 6.0 supports both DAO and ADO, we must be careful to fully
qualify the type of recordset we need. If we forget to specify that the recordset is of
type ADODB, the environment will gladly provide a recordset – except that it will be for
DAO, not ADO. So take a look at what would happen if you tried to create a recordset:

The problem is that the Recordset highlighted by IntelliSense here would be a DAO type
recordset – not what we want. So please be careful to specify an ADO recordset. This is
done by first specifying ADODB. As soon as you press the "." dot after ADODB,
you will see the various methods, constants, and events that are available to an ADODB. Of
course we want the recordset of the ADODB object. Be sure that you select the correct type
of recordset as shown below:

Since you remembered to add the ActiveX references to your project, VB 6.0 now is able
to provide the Intelli-help choices in the drop down box.

After we dim our connection and recordset object variables, we want to set them to a
new connection and a new recordset.

Remember that in DAO we would set an object variable to a database, then another to a
recordset that was built on the database object variable. Opening the DAO database
required that we pass in the fully qualified name and location of the database.

In ADO, however, we will pass that information in as part of the connection string when
we actually open the connection.

Here we are creating a new connection and recordset object. By using the Set keyword,
we set a reference to our object variables, adoConnection and adoRecordset:

'-Create a new connection --
Set adoConnection = New ADODB.Connection

'-Create a new recordset --
Set adoRecordset = New ADODB.Recordset

'-Build our connection string to use when we open the connection --
connectString = "Provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data Source=C:BegDBBiblio.mdb"

Of course, we could just as easily have added this last line directly to the Open
method of the adoConnection object. But placing it in a string is a good way to get
comfortable with the actual string of parameters.

Before establishing a connection, our application must set up a connection string, as
well as connection time-out, default database, and connection attributes. The connection
object also allows you to set up the CommandTimeout property for all the command objects
associated with this particular connection. We will just use the default for now, which is
15 seconds.

We are ready to open the connection with the string we defined above. The string
provides the connection object with enough information on the OLE DB provider and the
database to establish the link.

The easiest way to open a connection is simply pass the connection string to the Open
method of the connection object:

adoConnection.Open connectString

To see if the connection was successful, you could check out the State property of the
connection object. State will return adStateOpen if the connection is open and (surprise!)
adStateClosed if it isn’t. If you wanted to test the connection, you could simply add
something like:

'We can test to see if the attempt to connect worked.
If adoConnection.State = adStateOpen Then
MsgBox "The Connection is now open!"
Else
MsgBox "Sorry. The connection could not be opened."
End If

Once our connection object is linked to the database by using the open method, we can
now use this connection to open a recordset or perform some action on the data source.

If there is an error in the connection string, VB will not know it until we try to open
the connection object, adoConnection. By simply assigning the connect string to the
connectString variable (or even directly to the connection object), any errors won’t show
up until we try to actually use the string by connecting. So if an error does crop up, you
can be sure that the connection string is the culprit.

Now we are ready to open the recordset by using its .Open method. We are using only two
parameters here – the table we want opened and the connection to use.

adoRecordset.Open "Publishers", adoConnection

There are several additional parameters we can pass to more granularly define the
recordset we want opened. The syntax of the .Open method for a fully qualified recordset
looks like this:

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

As you can see, in our program we are only passing in the source of the data and the
active connection. We will soon cover these additional parameters when we write some
additional programs. But for now, we will simply rely on their default values.

Now that we have an open connection and a recordset, it is very straightforward to
update our list box. Notice that they syntax is the same as we used for DAO: we use the
same "!" ‘bang’ operator to access a specific field from the recordset:

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

Finally, we close the recordset and the connection. Since these are object variables
are dim’ed locally, they would go ‘out of scope’ as the code exited the procedure. But it
is good programming practice to always close these items when they are no longer needed:

adoRecordset.Close
adoConnection.Close
set adoRecordset = nothing
set adoConnection = nothing

There, we have just opened an ADO connection, created a recordset, and accessed the
Name field. Not too bad.

As you already know, the Open method of the Connection object is used to establish a
connection. With the OLE DB – ODBC Provider, an ADO application can use the ODBC
connection mechanism to connect to a database server. ODBC allows applications to
establish a connection through various ODBC data sources, or by explicitly specifying the
data source information. This is commonly referred to as DSN (Data Source Name)-less
connection. DSN stands for Data Source Name. To see the difference, take a look at these
examples. First, the standard, DSN connection:

Dim myADOConnection As New ADODB.Connection

'A DSN Connection looks like this
myADOConnection.Open "myDSN", "sa"

Next, here’s the DSN (Data Source Name)-less example:

'A DSN (Data Source Name)-less connection looks like this
myADOConnection.Open "Provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data Source=C:BegdbBiblio.mdb"

myADOConnection.Close
Set myADOConnection = Nothing

The DSN example opens a connection using the myDSN ODBC data source that points to a
.mdb database. In other words, the DSN connection has all of the required information
stored in it. With a DSN (Data Source Name)-less connection, we provide all of the
information required to open the connection.

We can now find a wide variety of ODBC drivers that can be used with ADO to establish a
connection to data. Soon, there will be OLE DB providers available to connect to most data
sources. You can use a different provider by setting the Provider property of the
Connection. But if you want to connect with an ODBC-compliant data source, you could use
the following Try It Out as an example and create your own DSN. Let’s take a look at how
this works.

First, we will build the New Data Link by creating a new ODBC Data Source. Then we will
use the SQL OLE DB data provider to talk to it. We will go through these steps so you can
see how to connect to virtually any data source.

First then, we want to build a Data Source Name (DSN). This can be referenced, and it
will contain all of the information required to access a data source.

Try It Out – Creating a New Data Source

1 From your Windows 95/98 Settings | Control panel, select the 32bit ODBC icon:

This will bring up the ODBC Data source Administrator dialog box. Any data sources
already defined will be listed:

2    Click on Add to create a new user data source. When you click Add,
the Create New Data Source dialog box appears with a list of drivers:

Choose the driver for which you are adding a user data source. Since we are using .mdb
files, select the Microsoft Access Driver. Any drivers that are installed on your machine
will show up. Notice that the Access driver is version 3.51 – new with Visual Basic 6.0.

3    Double click on the Microsoft Access Driver (*.mdb) to display the
Setup dialog box:

Provide the name of the data source as Our ADO Example DSN and the description as
Beginning Database Programming in VB6.0. Now we need to set up the database. Within the
Database frame, click the Select… button. Here you can navigate to our old friend,
the BegDbBiblio.mdb database.

When you click OK, you will now see the database name and location defined on the Setup
dialog box.

3 Next, click on the Advanced… button to display the Default Authorization.

5    Remember when we were discussing Access security (long ago, in
Chapter 2)? Be sure to add Admin as the Login name. Then click OK. Now choose the User DSN
tab and notice that our new DSN description is listed as a valid choice:

6    Make sure that Our ADO Example DSN has the Access driver file
selected. Again, when you install new drivers on your machine, they will be listed as
options here.

Now we’ll test new ODBC Data Source

1    To ensure everything is working, let’s go back to our form,
frmADOPublishers and right click the ADODC control to bring up the properties page.

2 Choose Build… to bring up the Data Link Properties.  Since we just defined
a new ODBC DSN, on the Provider tab select the OLE DB Provider for ODBC Drivers entry.
Then click Next >>.

3 Now select our newly minted data source – Our ADO Example DSN – from the drop down
box.

Be sure to add Admin for the User Name. However, it is already built into our DSN
connection. If you needed to set this up for a specific user, it would be done here. Then
click Test Connection to insure everything is fine.

4 Click the Advanced tab and select the Share Deny None choice as shown. Again, you
would do this if you wanted to change the defaults defined when the DSN setup was built.
But we do it here just to show you how to accomplish this:

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:

Constant Description
AdStateClosed Default. Indicates that the object is closed
AdStateOpen Indicates that the object is open
AdStateConnecting Indicates that the Recordset object is connecting
AdStateExecuting Indicates that the Recordset object is executing a command
AdStateFetching Indicates that the rows of the Recordset object are being fetched

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:

Cursor type Description Constant
Static cursor This is the one to use for generating reports or finding data. Additions, changes, or deletions by other users are not visible adOpenStatic
Forward-only cursor This is the default. It is identical to the Static except that you can only scroll forward. The fastest cursor this side of the Pecos Mountains adOpenForwardOnly
Dynamic cursor Additions and deletions by others are visible. All movement is supported. But some providers don’t support this cursor type adOpenDynamic
Keyset-driven cursor This is similar to a Dynamic cursor except you can’t see records others add. If another user deletes a record, it is inaccessible from your recordset adOpenKeyset

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

Lock Type Description
AdLockReadOnly Default. Read-only: you cannot alter the data.
AdLockPessimistic Pessimistic locking, record by record-the provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately upon editing.
AdLockOptimistic Optimistic locking, record by record-the provider uses optimistic locking, locking records only when you call the Update method.
AdLockBatchOptimistic Optimistic batch updates-required for batch update mode as opposed to immediate update mode

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:

Description Constant
The provider should take the source as a text description of a command, such as a SQL statement adCmdText
ADO should generate an SQL statement to fetch all rows from the table in Source adCmdTable
The provider should return all of the rows from the table named in Source. adCmdTableDirect
The provider should treat the Source as a stored procedure adCmdStoredProc
The type of command in Source is unknown. You should not use this! It is the slowest of all cursors. AdCmdUnknown
A saved recordset should be restored from the file names in Source adCommandFile
Source should be executed asynchronously adFetchAsync

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.

Latest Posts

Related Stories