VB with ADO and Access 2000
This presentation will look at a variety of uses of VB with an Access 2000 database. ADO will be the control.
The components selected to use the ADO Data Control and the DataGrid are shown on this slide. Note that Selected Items Only is clicked so I just see these items. Clearly, I do not want this selected when I am making choices. Note that this is reached through Projects/Components.
The selected components on the form are also shown.
This code uses the studentrel00 database that was created in Access 2000. This slide shows the connection string and the record source as well as the caption. for the ADO data control that is the connection to the database.
To make the connection, I first clicked on custom and then in the property pages box, I clicked on build. You can see the results of the building process in the slide above. The information here is what is stored in the ConnectionString property.
Note that the database being connected to is C:\Access2000DB\studentrel00.mdb.
To get the Property Pages shown above, I clicked on RecordSource. I selected 1-adCmdText because I want to use SQL to select information from the student00 table.
The data grid is linked to the DataSource ADOStudent which was previously defined.
When Process is clicked, the GetStudents InputBox comes up. I entered PR and the results show the people who have a PR majorcode.
The explanation of the code involves breaking up a line that is not broken up in the program.
Note that the connection to the database is made as shown in the previous example. This shows the connection to the specific table in the database. The RecordSource uses 2 - adCmdTable and selects student00 as the table to use.
The user enters Susan Ash and the cursor moves to the record for Susan Ash.
Bookmark points to the current record. In this example, holdBookmark has the record that the cursor is pointing to prior to the attempt to find a specific record. If the attempt to find a record fails then EOF is reached and holdBookmark is moved into the bookmark so that the current record becomes the one prior to the attempt.
To find a record, you can use ADOStudent.Recordset.Find when you are looking at a table. The find shows the comparison between the field called name and the data that was entered in the InputBox and stored as findStudent. Note that findStudent must be enclosed in single quotes so concatenation is used.
This form has two ADO connections to two different tables. It also has two grids to show the information from the two tables. This slide starts the examination of the ADO Student control which connects to the table student00. On the next slide, I click on Custom and show the link.
The link is to C:\Access2000DB\studentrel00.mdb. This is the link to the database only.
Note that record source Command Type is 1 - adCmdText which allows me to select information from the table using an SQL select (shown on slide).
This shows the link between the dbgStudent grid and the ADO control named ADOStudent.
Note that this form has two separate ADO controls each related to their own grid.
This shows the Connection String associated with ADO Grades. On the next slide, I will use custom to show the data that is there.
This makes the link between the ADO control and the studentrel00.mdb database.
This shows the Record Source connection to the records that are shown in the second grid.
This shows the connection between the dbgGrid on the right with the ADOGrades control.
The link between the two grids is made in the code that is executed when Connect is clicked.
We will examine that code on the next slide.
The " after studentidno = ends the main part of the select statement which is then concatenated with a single quote, the name of the variable that contains the data to be compared to and then with another single quote.
This slide shows the select statement that goes with the ADOStudent control.
This shows the select associated with ADOGrades.
This links the txtIdno field to ADOStudent and specifically the studentidno field. The second text box is the name, which is linked to the name field in ADOStudent.
The datagrid called dbgGrades is linked to ADOGrades.
When process is clicked, the input box screen comes up and asks for a student idno. When it is entered, the student and the grades for the student are retrieved. If you work with this program, you will see that students without grades are not retrieved. This is because of the relationship that was established between the tables in Access.
This is the code behind the form. We will examine parts of the code on this screen and on the next.
Note that the select lines were split without continuation symbols because they are being explained here. The code on the previous slide is the executable code.