Using the ADO Data Control

See Also

The ADO Data control uses Microsoft ActiveX Data Objects (ADO) to quickly create connections between data-bound controls and data providers. Data-bound controls are any controls that feature a DataSource property. Data providers can be any source written to the OLE DB specification. You can also easily create your own data provider using Visual Basic's class module.

Although you can use the ActiveX Data Objects directly in your applications, the ADO Data control has the advantage of being a graphic control (with Back and Forward buttons) and an easy-to-use interface that allows you to create database applications with a minimum of code.

Figure 7.4   The ADO Data Control

Several of the controls found in Visual Basic's Toolbox can be data-bound, including the CheckBox, ComboBox, Image, Label, ListBox, PictureBox, and TextBox controls. Additionally, Visual Basic includes several data-bound ActiveX controls such as the DataGrid, DataCombo, Chart, and DataList controls. You can also create your own data-bound ActiveX controls, or purchase controls from other vendors.

Previous versions of Visual Basic featured the intrinsic Data control and the Remote Data control (RDC) for data access. Both controls are still included with Visual Basic for backward compatibility. However, because of the flexibility of ADO, it's recommended that new database applications be created using the ADO Data Control.

For More Information   A complete list of data-bound controls can be found in "Controls That Bind to the ADO Data Control." To find out how to use the intrinsic Data control or the Remote Data control, see "Using the Data Control" or "Using the Remote Data Control." For details about creating a data provider, see "Creating Data-Aware Classes."

Possible Uses

• Connect to a local or remote database.
  
  
• Open a specified database table or define a set of records based on a Structured Query Language (SQL) query or stored procedure or view of the tables in that database.
  
  
• Pass data field values to data-bound controls, where you can display or change the values.
  
  
• Add new records or update a database based on any changes you make to data displayed in the bound controls.

To create a client, or front-end database application, add the ADO Data control to your forms just as you would any other Visual Basic control. You can have as many ADO Data controls on your form as you need. Be aware, however, that the control is a comparatively "expensive" method of creating connections, using at least two connections for the first control, and one more for each subsequent control.

Creating a Front-end Database Application with Minimal Code

It's possible to create a database application using a minimum of code by setting a few properties at design time. If you are using an OLE DB data source, the Microsoft Data Link Name (.UDL) must be created on your machine. See "Creating the Northwind OLE DB Data Link" for a step-by-step example.

To create a simple front-end database application

1. Draw an ADO Data Control on a form. (The icon's ToolTip is "ADODC.")

   If the control is not available in the Toolbox, press CTRL+T to display the Components dialog box. In the Components dialog, click Microsoft ADO Data Control.

2. On the Toolbox, click the ADO Data Control to select it. Then press F4 to display the Properties window.
   
   
3. In the Properties window, click ConnectionString to display the ConnectionString dialog box.
   
   
4. If you have created a Microsoft Data Link file (.UDL), select Use OLE DB File and click Browse to find the file on the computer. If you use a DSN, click Use ODBC Data Source Name and select a DSN from the box, or click New to create one. If you wish to use create a connection string, select Use ConnectionString, and then click Build, and use the Data Link Properties dialog box to create a connection string. After creating the connection string, click OK. The ConnectionString property will be filled with a string like:

   driver={SQL Server};server=bigsmile;uid=sa;pwd=pwd;database=pubs
   

5. In the Properties window, set the RecordSource property to a SQL statement. For example,

   SELECT * FROM Titles WHERE AuthorID = 72
   

   You should always include a WHERE clause when accessing a table. Failing to do so will lock the entire table, which would be a major hindrance to other users.

6. Draw a TextBox control on the form to display the database information.
   
   
7. In the Properties window, set the DataSource property for Text1 to the name of the ADO Data control (ADODC1). This binds the text box to the ADO Data control.
   
   
8. In the Properties window, click DataField and a list of available fields will drop down. Click the name of the field you want to display.
   
   
9. Repeat steps 6, 7, and 8 for each additional field you want to access.
   
   
10. Press F5 to run the application. You can use the four arrow buttons on the ADO Data control to move to the beginning of the data, to the end of the data, or from record to record through the data.

Setting the ConnectionString, Source, DataSource, and DataField Programmatically

The code below shows how to set these four properties programmatically. Note that setting the DataSource property requires the Set statement.

Private Sub Form_Load()
   With ADODC1
      .ConnectionString = "driver={SQL Server};" & _
      "server=bigsmile;uid=sa;pwd=pwd;database=pubs"
      .RecordSource = "Select * From Titles Where AuthorID = 7"
   End With
   Set Text1.DataSource = ADODC1 
   Text1.DataField = "Title"
End Sub

ADO Data Control Events

The ADO Data control features several events that you can program. The table below shows the events and when they occur; however the table is not meant to be a complete list all of the conditions when the events occur. For complete information, see the reference topic for the individual event.

For More Information   To try other step-by-step procedures using the ADO Data control, see "Creating a Simple Database Application with the DataGrid and ADO Data Control," "Creating a Simple DataCombo Application," and "Creating a DataGrid Linked to a DataList Control."