March 3, 2021
Hot Topics:

Book Review: Beginning Visual Basic 6 Databases

  • By John Percival
  • Send Email »
  • More Articles »

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

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

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

Step 1 - The Connection Object

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

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

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

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

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

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

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

Step 2 - Opening a Recordset

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

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

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

Page 9 of 23

This article was originally published on November 20, 2002

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date