More on VB ADO and Access97 or Access2000
Other examples of ADO - basic.
These examples use a Access 97 database but they could also use an Access 2000 database.
This shows the connection string using the ADO control to link to InvenTre.mdb which is on my C drive under AccessDatabases.
This shows the steps to create that connection. Use Connection string made me select a provider since done was selected. Then I needed to select the database. The connection can be tested with test connection.
The database has now been selected, next we need to go to Record Source and select from the tables and queries available. Note that I selected 2 - adCmdTable and then selected the table by name.
Note that the name of the data control is Adodc1. This will be used on the next slide when I connect the fields to the data control.
Notice that each field needs to be linked to the data source and to the specific data field on the table.
This display uses a DataGrid control to hold the data. The sizing of the grid is important. Note that if the grid area is not big enough to hold the data, scroll bars are used.
Note that I am using an Access 97 database still. I could be using an Access 2000 database with the ADO control.
The data grid is connected to the ADO data control which was called Adodc1
The SQL associated with the Adodc1, selected the three fields ItemNo, ItemName and OnHand for records in the tree table where the OnHand was less than 25. Note that the command type is set to 1 - adCmdText. This means the grid will only show the records that were selected to be part of the record set by the ADO Control.
This code is using two tables: Tree and Vendor. The relationship between the two tables is that the VendorNo must match. The fields that are being selected are ItemNo, ItemName, VendorNo and Company. Only one of these fields, VendorNo appears on both files. In that case, when I list the fields I must specify whether I want the VendorNo on the Vendor table or the VendorNo on the Tree table. Clearly it does not matter logically since I am looking only for information where the vendor number on the two tables match.
Note again that the two tables are linked in the ADO control and the combination of the two tables (resulting query) is what is presented to the grid.
This shows the ADO link to the InvenTre.mdb for the Vendor ADO control There is a similar link for the Tree ADO control to the InvenTre.mdb.
This shows that the VendorADO is linked to the vendor table and will only show the VendorNo and the Company.
This shows that the Tree ADO control is linked to the tree table and will show the vendorno and the itemname.
The code that brings up only the inventory products that go with the matching vendor is shown in the next slide.
Essentially when the currVendor is found it takes the first field in the row on the grid.