Visual Basic 6 Black Book
(Publisher: The Coriolis Group)
Author(s): Steven Holzner
ISBN: 1576102831
Publication Date: 08/01/98

ADO

Microsoft’s latest set of data access objects are the ActiveX Data Objects (ADO). These objects let you access data in a database server through any OLE DB provider. ADO is intended to give you a consistent interface for working with a wide variety of data sources, from text files to ODBC relational databases to complex groups of databases.

The way Microsoft implements connections to all those data sources is with the OLE DB set of COM interfaces, but that standard is a very complex one. Our interface to that interface, so to speak, is ADO, a set of objects with properties, events, and methods. Here are the ADOs:

•  Connection—Access from your application to a data source is through a connection, the environment necessary for exchanging data. The Connection object is used to specify a particular data provider and any parameters.

•  Command—A command issued across an established connection manipulates the data source in some way. The Command object lets ADO make it easy to issue commands.

•  Parameter—Commands can require parameters that can be set before you issue the command. For example, if you require a debit from a charge account, you would specify the amount of money to be debited as a parameter in a Parameter object.

•  Recordset—If your command is a query that returns data as rows of information in a table, then those rows are placed in local storage in a Recordset object.

•  Field—A row of a Recordset consists of one or more fields, which are stored in Field objects.

•  Error—Errors can occur when your program is not able to establish a connection, execute a command, or perform an operation, and ADO supports an Error object to hold the resulting error.

•  Collection—ADO provides collections, an object that contains other objects of a particular type. ADO provides four types of collections: the Connection object has the Errors collection, the Command object has the Parameters collection, the Recordset object has the Fields collection, and the Connection, Command, Recordset, and Field objects all have a Properties collection, which contains all the Property objects that apply to them.

•  Events—ADO uses the concept of events, just like other interface objects in Visual Basic. You use event handling procedures with events. There are two types of events: ConnectionEvents (issued when transactions occur, when commands are executed, and when connections start or end) and RecordsetEvents (events used to report the progress of data changes).

ADO also includes the Remote Data Service (RDS), with which you can move data from a server to a client application or Web page, manipulate the data on the client, and return updates to the server in one round-trip.

In this chapter, we’re going to use the ADO data control to handle our ADO work.

The ADO Data Control

The ADO data control is similar to the data control and the remote data control. The ADO data control is designed to create a connection to a database using Microsoft ActiveX Data Objects (ADO). At design time, you create a connection by setting the ConnectionString property to a valid connection string, then set the RecordSource property to a statement appropriate to the database manager.

You can also set the ConnectionString property to the name of a file that defines a connection (the file is generated by a Data Link dialog box, which appears when you click ConnectionString on the Properties window and then click either Build or Select). You then connect the ADO data control to a data-bound control such as the data grid, data combo, or data list control by setting its DataSource properties to the ADO data control.

At runtime, you can set the Provider, ConnectionString, and RecordSource properties to change the database.

We’ll see how to work with the ADO control in this chapter. We’ll use controls like the data control, the remote data control, and the ADO data control with bound controls.

The Data-Bound Controls

You can bind certain controls to the data control, the remote data control, and the ADO data control, and those controls are called bound controls. To bind a control to a database control, you use properties like DataSource to specify the database control, and then use properties like DataField or BoundColumn to specify what field to display in the bound control, as we’ll see. Here are the controls that can function as bound controls:

•  Picture boxes

•  Labels

•  Text boxes

•  Checkboxes

•  Image controls

•  OLE controls

•  List boxes

•  Masked edit controls

•  Rich text boxes

•  Combo boxes

In addition, there are special controls that are designed to be used as bound controls:

•  DBList

•  DBCombo

•  FlexGrid

•  MSFlexGrid

Finally, a number of bound controls are specially built to be used with the ADO control only:

•  DataList

•  DataCombo

•  DataGrid

We’ll see these controls at work in this chapter.

That’s it, then, for the overview of databases. We’ve seen how the process works in overview; now it’s time to turn to the Immediate Solutions.

Immediate Solutions

Creating And Managing Databases With The Visual Data Manager

You can create and manage databases with the Visual Data Manager. Open this tool from the Visual Basic Add-Ins menu, as shown in Figure 24.3.

Figure 24.3  The Visual Data Manager.

You can use this tool to create and modify databases. You create a new database with the File menu’s New item and open an existing database with the Open item. Let’s see an example. Here, we’ll create a new database with the Visual Data Manager. Click the Table Type Recordset button, which is the button at the extreme left in the toolbar, and the Use Data Control On New Form button, which is the fourth button from the left.

Next, select the New item in the File menu. The Visual Data Manager lets you design databases in several different formats; for this example, choose Microsoft Access Version 7 MDB or later. The Visual Data Manager asks you for a name and path for this new database; we’ll call it “db.mdb”, so enter that name now and click on OK.

That’s it—now we’ve created a new database, db.mdb. We’ll add a table to it in the next topic.