Visual Basic Explorer
Visual Basic Explorer
 Navigation
 Home


 Coding
 Source Code

 FAQ Center

 VB Tips

 Downloads

 ToolBox

 Tutorials

 VB Games

 VB News

 VB Award

 VB Forums



 Affiliates
 Planet Source Code

 Rent a Coder

 DirectX4VB


 Misc
 Search

 Feedback

 Advertise

 About


Need to hire
a VB coder?

Please support our sponsor:

 Home 
 Site Map 
 Forums 
 News 
 Feedback 

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:

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

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.

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

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

       

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

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.

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

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:\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:

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.

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

 

Why Cursors are Important in ADO

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

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.

 

Opening ADO Recordsets – Syntax

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.

An ADO Transaction

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

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.

Summary

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.





Home | About | What's New | Source Code | FAQ | Tips & Tricks | Downloads | ToolBox | Tutorials | Game Programming | VB Award | Search | VB Forums | Feedback | VBNews | Copyright & Disclaimer | Advertise | Privacy Policy |

Quick searches: Site Search | Advanced Site Search 

Copyright 2002 by Exhedra Solutions, Inc.
By using this site you agree to its terms and conditions
VB Explorer and VBExplorer.com are trademarks of Exhedra Solutions, Inc.