Universal Data Access Using ADO

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 if 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. So why is DAO coming to the end of it's development as

The Limitations of DAO

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 ofaccessing 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 accessingnon-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 asUniversal 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 externalISAM (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 newer 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:

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 theDataSource 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 isOpen 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 providesdatabase 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 accessremote 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 JetTableDef 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.

Change is the Only Constant

But the world does not stand still and the rate of change has only accelerated in the past 18 months, and it's theInternet 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.

Universal Data Access

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 providesRemote 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 asingle high level, efficient programming paradigm to work with everything. It's calledUniversal 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.

Why ADO is the Cool New Way to Access Data

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 ofActiveX Data Objects (ADO),Remote Data Services, (RDS, formerly known as Advanced Database Connector or ADC),OLE DB, andOpen Database Connectivity (ODBC). Together, these interfaces provide us the means to work with just about any data source. And together they are known asUniversal 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.

OLE DB is the Answer!

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

1Start a new project called \Chapter11\prjfirstADO. Now go into theProject | References dialog and add theMicrosoft ActiveX Data Objects 2.0 Library andActiveX 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 theMicrosoft 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 projectfrmADO. 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 ourBiblio.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 aProperty Page dialog box for the control. The first thing we must do is tell the control some important information. Unlike the singularDatabaseName property we need to set on the standard data control, the ADO data control requires aconnection 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.

Review of Steps to Set Up the ADODC ConnectionString

If you haven't already done so, right click on the ADODC control and selectADODCProperties.

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 theBuild 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 theConnection 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\BegDB\Biblio.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:

It is always a good idea to use theTest 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 Connectiononce 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 theAll 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 theOK 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 ADODCProperties. 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:

Select2 - adCmdTable. Now the control knows we want to access records from a table directly. If theDataSource is not known in advance, thenadCmdUnknown is selected. If we were going to issue a SQL command, thenadCmdText would be selected, and the bottom text box, Command Text (SQL), would become enabled. Finally, if we have stored, pre-compiled procedures, we would chooseadCmdStoredProc. This time, be sure to select choice2 - 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, theTableorStored Procedure Name listbox becomes enabled. Click the listbox and all of the tables in the database are shown:

Select thePublishers table and clickOK.

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 theAdodc1 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 theText1 textbox. Bring up the property dialog box forText1. Set theDataSource property toAdodc1.

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 theName field.

1. 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

TheMoveComplete 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'sActivate 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 thefrmADO form'sForm_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 providesa 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.

The ADO Data Control Properties

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 casePublishers. And since we want to open a table, instead of a dynaset from a join, the .CommandType ofadCmdTable is selected. Take another look at theConnectionString - 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 is0 - 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.

The ADO Object Model

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 aConnection to the database, we will then create aRecordset with that connection. From theRecordset, we will access theFields 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

TheConnection object allows us to establish connection sessions with data sources. In other words, the connection represents aphysical 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 themethods of the connection object:

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

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.

Programming with Active Data Objects

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:\Begdb\Biblio.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:\BegDB\Biblio.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.

Creating a New Data Source

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:\Begdb\Biblio.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

2. 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 \BegDb\Biblio.mdb database.

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

1. 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

Try It Out - Testing our DSN

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.

1. 1. 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 >>.
       
   2. 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.

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

      

2. 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.

Try It Out - Testing our New DSN Connection in Code

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

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

Private Sub cmdTestDSN_Click()

Dim myConnection As ADODB.Connection

Set myConnection = New ADODB.Connection

'If we wanted, we could set the provider property to the OLE

'DB Provider for ODBC. However we will set it in the connect 'string.

' Open a connection using an ODBC DSN. The MS OLE DB for

' SQL is MSDASQL. We gave our new data source the name "Our ADO Example DSN"

' so let's use it.

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

myConnection.Open

' Determine if we conected.

If myConnection.State = adStateOpen Then

MsgBox "Welcome to the Biblio Database!"

Else

MsgBox "The connection could not be made."

End If

' Close the connection.

myConnection.Close

End Sub

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

How It Works

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

Dim myConnection As ADODB.Connection

Set myConnection = New ADODB.Connection

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

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

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

myConnection.Open

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

If myConnection.State = adStateOpen Then

MsgBox "Welcome to the Biblio Database!"

Else

MsgBox "The connection could not be made."

End If

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

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

Now that we've demonstrated how to open up our connection to the data source, let's consider how to run some SQL against the data in the data source. We send any processing commands via the Execute method of the connection object.

Using the Connection Object's Execute Method

To We can use the Execute method to send a command (typically an SQL statement, but it might be other text) to the data source. If our SQL statement returns rows (instead of, say, updating some records) then a Recordset is created. The Execute method in reality always returns a Recordset. However, it is a closed Recordset if the command doesn't return results.

Let's see an example of the Execute method in action.

Try It Out - Testing the Execute Method

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:\BegDB\Biblio.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:\BegDB\Biblio.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.

Opening Recordsets

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.

Take a look at the figure below:

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. 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:\BegDB\Biblio.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:\BegDB\Biblio.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.

Trawling for Data Types

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. 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:\BegDB\Biblio.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.

Reaquaint Yourself With the Object Browser

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. 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:\OhNooo\Directory\Path;"

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:

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:\OhNooo\Directory\Path;"

Of course, we don't have a directory called C:\OhNooo\Directory\Path 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:

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

Finding Out All About Our Data Provider

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. 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:\BegDB\Biblio.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.

The ADO Object Model - Revisited

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?

** KATE COULD YOU PLEASE MAKE ANOTHER RENDITION OF THIS? THANKS

NB care re: highlighted shades – need to emphasise the brown ones **

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).

There

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