• The ADO (ActiveX Data Object) data control is the primary interface between a Visual Basic application and a database. It can be used without writing any code at all! Or, it can be a central part of a complex database management system. This icon may not appear in your Visual Basic toolbox. If it doesn’t, select Project from the main menu, then click Components. The Components window will appear. Select Microsoft ADO Data Control, then click OK. The control will be added to your toolbox.
• As mentioned in Review and Preview, previous versions of Visual Basic used another data control. That control is still included with Visual Basic 6.0 (for backward compatibility) and has as its icon:
Make sure you are not using this data control for the work in this class. This control is suitable for small databases. You might like to study it on your own.
• The data control (or tool) can access databases created by several other programs besides Visual Basic (or Microsoft Access). Some other formats supported include Btrieve, dBase, FoxPro, and Paradox databases.
• The data control can be used to perform the following tasks:
1. Connect to a database.
2. Open a specified database table.
3. Create a virtual table based on a database query.
4. Pass database fields to other Visual Basic tools, for display or editing. Such tools are bound tools (controls), or data aware.
5. Add new records or update a database.
6. Trap any errors that may occur while accessing data.
7. Close the database.
• Data Control Properties:
Align Determines where data control is displayed.
Caption Phrase displayed on the data control.
ConnectionString Contains the information used to establish a connection to a database.
LockType Indicates the type of locks placed on records during editing (default setting makes databases read-only).
Recordset A set of records defined by a data control’s ConnectionString and RecordSource properties. Run-time only.
RecordSource Determines the table (or virtual table) the data control is attached to.
• As a rule, you need one data control for every database table, or virtual table, you need access to. One row of a table is accessible to each data control at any one time. This is referred to as the current record.
• When a data control is placed on a form, it appears with the assigned caption and four arrow buttons:
The arrows are used to navigate through the table rows (records). As indicated, the buttons can be used to move to the beginning of the table, the end of the table, or from record to record.
The ADO Data Control is a very powerful way to link your database to Visual Basic. In this example, we will be using it to connect to the Access Database called Inventre.mdb. This database has been used in other examples. The specific table that we are going to be using is called Tree.
You need to go to Project/Components and select the Microsoft ADO Data Control as shown below. In the example below, I had already made the selection so you can see the icon at the bottom right in the Toolbox.
Setting Up The ADO Data Control In Visual Basic 6 (VB6)
Put a ADO Data Control on your form. You then want to go to Properties and Select (Custom). This will then give you the three dots to bring up the Window shown below.
As you can see in the screen below, you can now specify where the data source is etc. First, we will use the Use Connection String option and the Build Button. You will then see the screen entitled Data Link Properties. That is shown in the second screen picture below.
Select the Microsoft Jet 3.51 OLE DB Provider and then click the NEXT button that is on the bottom of the screen. The Connection portion of the Data Link Properties will now show. This screen should be filled in to point to the database that you will be using. By clicking next to the button after 1. Select or enter a database name, you will get the browse ability and you can select the name of your database. All other options on this screen are left alone. Note that I selected InvenTre.mdb and its path on my hard drive.
Note that the Use Connection String was filled in automatically.
Now on the Property Page, go to the Record Source. Here we are going to select 2. - adCmdTable to indicate that we are going to be using a table for this project. And then go down to Table or Stored Procedure Name and select the name of the Table that we are planning to use. In this case it is Tree.
For the most flexibility, it is now recommended that you go to the Properties for the ADO and select adOpenDynamic under the Cursor Type. Notice that the Command Type and Connection String are filled in with the information that was provided. If you scroll down you will also see that the RecordSource contains Tree from the selection above.
We will now put some fields on the form to contain information from the Tree table. We will then Bound them to the data control so that the data from the Tree table will appear on the form.
In this screen, I bound the fields to the DataSource Adodc1. I have not changed the name from the original just for consistency on the screens. Note: I copied and pasted these fields from another form so they originally came in with another DataSource and I had to select Adodc1. If you look at this example, you will see the inherited information from the other form as an alternative under DataSource.
Now I can execute. The form showing the first record on the Tree table is shown below.
Now I am going to create another form with the ADO Control in the same way. This time I am going to add The Microsoft Data Grid Control 6.0 to my Toolbox and use it to create a grid on the form to hold the data. I need to establish the Record Source as I did before. The results are shown below.
Now I am going to use this Microsoft Data Grid Control 6.0 to select only certain records from my database. To do this, I first need to go back to the Record Source on the Property Page of the ADO Data Control and change the Command Type to 1 - adCmdText. Then I can enter a SELECT statement under Command Text [SQL].
In this SELECT statement, I am saying that I want to see the ItemNo, the ItemName, and the OnHand fields from the Tree table where the OnHand field is less than 25. As can be seen in the screen below, only those records meeting the criteria are shown in the Grid.
