Database Programming in VB

First a more general overview of reational databases and then into the VB interfaces.

Relational Databases Chapter 24

Relational databases were developed in the '60s. THey are built around a data model that records information in table form. Each row in a table represents a single instance of the data. Each column is a facet of the information. So, if we were keeping track of our book collection, each row would represent one book and each column would be something like the title, the author, etc.

This is a very natural way to record data and it has many nice mathematical properties as well. These are expressed in something called relational algebra. We see this in the form of SQL or Structured Query Language. Actually covering SQL is way beyond the scope of this class but we will be using examples so you should get a feel for it.

Here is a small sample of a MS Access database that has one table and contains a small part of my comic book collection.

Each row represents on comic. The columns are the title, the publisher, the issue number, the cost, the current value and the name of the box it is stored in. This is not a very efficient way to record this data. Each title appears many times. As do the publishers and boxes. Also, the value is something that changes over time and should be calculated.

The process of removing these redunancies is called normalization. One way to do that in this database is to use what are called lookup tables. These are database tables that have only 2 fields. One is a unique id value and the other is the value. For example, we could create a table of the titles and assign each a number. Then in the main table, we could store the number. We could also do this for the Publisher and Box fields.

The Data Control (Chapter 25)

For our first example of how to use VB with databases, we will use the first version of the DB because it makes the code a little simpler. We will be using the Data control. This is a very simple way to create forms to access the database. Overall, you use the control to connect other controls, like text boxes to fields in the database.

After you have created a new form, you draw the Data control on it. Change the Name and Caption properties as usual. Then change the DatabaseName property. This uses a file browser window to select the Access database (.mdb) file to use as the data source for the program. You can also set this in the form initialization code, which is what we will do.

Next we need to select the data from the database that we want to use. This creates a recordset. A recordset is the result of running a query. We will start with a query that selects all the records in the database. That is,

		select * from comics

Now we need to add some controls to the form so we can see and change the data. In this case we will add a bunch of text boxes and some buttons. Each of the text boxes will hold the data from one of the fields in the database table. These are known a bound controls because the value they display is tied to the field on the current record. If we change the text box, we change the field. And if we change the record, we see a different field value.

After we create the text boxes, we have to bind them to a database and a field. We do this by altering two design time properties. First we set the DataSource property to the Data control that we are using. We can have more than one Data control on a form. In this case, we only have one. The Data control doesn't allow this to be set a runtime. Once we have chosen a DataSource, we have to set the DataField. When we click on the ... button, we see a list of the fields in the database table attached to the Data control. Pick the one you want to bind to this control.

Once all the controls are bound, we can run the program. The Data control shows up looking like a CD player control. By clicking on the arrows, we can move through the set of records we selected. Each time we change records, the values in the fields change. We can edit the fields and the values are updated in the database.

We can also change the query that is the source of the records. To do this we have a couple of buttons to alter the query. The click code for these buttons is like this

Private Sub marvel_Click()
    Comics.RecordSource = "select * from comics where pub = 'marvel'"
    Comics.Refresh
End Sub

Adding and deleting records requires a seperate step. We add buttons to control this. To add a records, simply fill in the fields and click on the button. The code calls an addnew method on the recordset.

Private Sub addrec_Click()
    Comics.Recordset.AddNew
End Sub

Private Sub delrec_Click()
    Comics.Recordset.Delete
    If Not Comics.Recordset.EOF Then
        Comics.Recordset.MoveNext
    Else
        Comics.Recordset.MoveLast
    End If
    numrecs.Caption = Comics.Recordset.RecordCount
End Sub

If we don't want to use the data control arrow buttons, we can replace them with other controls. We can change the visible property on the Data control and use the move and find methods on the recordset to move around in the data.

Automatic Form Generation

VB has a wizard that can generate a basic form for a database given the database. Under the Add-In menu there is a Add-in Manager option. If you select this. you will see a list of tools that can be added to the VB environment. You should select the
VB 6 Data Form Wizard
The word Loaded should appear in the right most column. Click OK to add the wizard to the environment. In Chapter 25 there is an example that walks through this process and shows the screen as you go.