Using ADO Data Control
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.
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.