Lesson 3: Writing Code with ActiveX Data Objects

The ADO Data control provides an easy way to access data, but you can accomplish more if you write a bit of code, as well. For example, you can execute SQL queries and display the results in a bound DataGrid control.

This lesson uses the bound DataGrid control you created in Lesson 2, with a few changes, to display the results of a SQL statement. At the end of this lesson, you will be able to enter a capacity into a text field and find the rooms that hold that capacity or greater. Such a form might be handy for a meeting planner.

To execute a SQL statement through ADO:

Click on the Text control in the Toolbox.

Drag your cursor in the Form1 window to create a Text control.

This creates a text entry box your user can use to provide input on the form.

Click on the Label control in the Toolbox.

Drag your cursor in the Form1 window to create a Label control. This creates a label for the text box.

In the Caption field of the Properties window, type Enter Capacity:. This provides an instruction for the user.

Now that the controls are in place, you can write the SQL statement that retrieves the records you want to see.

Choose View > Code from the main menu. Visual Basic displays the Code window.

In the leftmost list box, choose the Text1 object. Visual Basic creates a blank sub procedure called Text1_Change. Enter the following code to create the SQL statement.

Click the Start button on the Toolbar. Visual Basic runs the application and displays the results.

To test out your application, type a value in the text box. Note the results in the DBGrid.

When you change the value in the text box, the Microsoft Jet engine parses the SQL statement and issues an ODBC request, which is passed to the Pervasive.SQL engine. Because the Microsoft Jet engine parses the SQL statement, your application's SQL statement must comply with Jet SQL syntax. For more information about this syntax, refer to the Visual Basic help.

When finish testing the application, close the Form1 window and return to the form design view.