Book Review: Beginning Visual Basic 6 Databases
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