Using Database

Speaker Notes

Slide 1:

This presentation uses the DataControl to set up a simple form bound to a database and then goes on to use the database in a variety of ways.

Slide 2:

The donor.mdb database has three tables. The table shown below is the donor table which I am going to use.

Slide 3:

First I used the data control icon and positioned it on the form. I stretched it and put the caption Donor in. I am naming the form datDonor.

Slide 4:

The DatabaseName property is set to the database that I want to use which is donor.mdb. It is stored on my floppy drive. Again note that this database was created in Access97. This makes the link to the database, but now I have to make the link to the particular table in the database. Then I will need to make links to particular fields on the data table.

Slide 5:

Note that there are table names and query names listed here.

If I wanted to combine data from multiple tables, I would use an existing query or create a query in access that had the information I need.

Slide 6:

This links the field to the DataSource that was specified in the data control. Remember, the data control is named datDonor.

Slide 7:

Now I am picking the exact field that will be associated with the box. I have picked Name. For the other text box, I will pick City.

Slide 8:

As can be seen, the data on the form corresponds to the data on the database.

Slide 9:

I changed the name on the first record to John Allen. Note that the change is made in the database. Note that to do the change, I simply keyed in the change and moved on to the next record.

Slide 10:

The navigation buttons on the data control allow you to move to the first record, the previous record, the next record and the last record. This can also be programmed using buttons that move through the Recordset. Note that you have methods to use such ast MoveFirst, MovePrevious, MoveNext and MoveLast.

Slide 11:

I decided to hide the data control and so I set the visibility of datDonor to False. This means that I can only navigate using the buttons that I programmed.

Slide 12:

The code in cmdChangeCity changes the value of city to the value that the user keyed into ChangeCity. First the edit statement makes a copy of the database record that will be edited. Next the value of the City field is replaced with the data the user typed into the box. The update statement puts the record with the change back on the database.

Slide 13:

In this slide, I clicked Add Record which adds a new record to the end of the table. Notice that when I look at the database, the record is there.

Slide 14:

AddNew adds a new record to the end of the table. However, when we view a table the default is order by index or primary key, so we see the record third in the list as opposed to last. See information about the index or primary key on the next slide.

I added these records by having the user key the data onto the screen. If you wanted to have the user key data into separate fields not linked to the database, you could then move the data into the new record by using code similar to the Change. Essentially taking the value and putting it into the field and then updating. You do not need to use the edit command, you are using AddNew instead.

Slide 15:

As you can see looking at the design window, Idno has been designated as the primary key or index. As noted, the file is indexed Yes(No Duplicates).

When you go to view and click on Indexes, you see the full explanation of the index/primary key.

Slide 16:

When you move around you will see that the record is no longer there. To confirm it, I went to the database to view the data.

Slide 17:

This slide shows the code used in the previous slides.

Slide 18:

This slide shows the results of clicking Find Id. The user is prompted to enter an id number to search for in the input box. Note that Data To Find is not used for the id. It is used for the city. Both could have been done with an input box or a field on the form. I am illustrating both approaches.

Slide 19:

This uses data keyed in to the form as opposed to data keyed into the InputBox. Notice that the field I am using on the form is not connected to the database. If there are duplicates, the first one will be found. See the next slide for the set up of the field Data To Find where Fall River was entered.

Slide 20:

Note that there is no connection to the database for the field txtDataToFind.

Slide 21:

Note that for this program I am going to enter the data to search for directly in the text boxes. This means that I want to remove the connection of the text box to the field on the database. I have removed it for idno (as shown), name and city.

Slide 22:

Note that this time I want to search for city so I have entered the City in the City field which is not connected to the database. If I wanted to find the Idno, I would enter the idno I was looking for in the text box for the idno.

Note that an error message is displayed if the city is not on the file.

Slide 23:

When the match is found, the data must be moved from the datDonor database to the text boxes. This is accomplished by specifying the field name on the database and assigning it to the text box.

Slide 24:

On the last slide, I moved in Idno and Name since the match was on City, here I am moving in Name and City since the match was on Idno.

Slide 25:

In this example I am using the DAO which was the first object-oriented interface that allows the connection between VB and Access and other databases. This is being used with Access97.

Note that you get to references via Project. These checks were made for me when I selected to use the DAO.