Databases - A Beginners Guide

by John Percival

Databases - A Beginners Guide

Welcome to the exciting world of database development! In this series of articles, I will introduce you to databases, and explain how to use them in your applications.

What are databases? Well the easy answer to this is that they are somewhere to store data. However they can do much more than this. I am going to begin at the very basics, which is were I was not so long ago. I will try to point you towards some good books, but the best free reference is found in the VB online help. Although you may get away with using the Learning or Standard edition, you will probably need the Professional or Enterprise Edition to use the data objects which we will be looking at later on.

Before we start looking at how to use databases in VB, we will have a look at their structure. The data is held in tables, stored within the database. Each table is divided up into columns, called fields, and rows, called records. In VB talk, tables are called RecordSets (ie. a set of records). For now, this is all the information you need to know, although later on in the article I will explain in more detail how the recordsets, tables and fields work together.

There are several ways that VB can access databases. The one that we will be using for now is DAO (Data Access Objects), but there is also RDO, ADO, and OBDC. DAO is the oldest form of data access engine, and is also the most popular. Microsoft estimates that over 75% of all professional Visual Basic applications use DAO! The reason for this high statistic stems from the ease that a simple database application can be created using the Data Control and built in VB commands.

The easiest way to set up a connection to a database at design time is to use the Data control. Set up the Database property and select the Biblio database (found in the VB directory). Now you can select the RecordSource (or table) that we are going to use. Select the 'Authors' table from the list. We will see later how we can use the RecordSource property to do SQL queries.

Next, draw two textboxs on the form, and set up the DataControl properties to Data1. Then set the DataField property of the first text box to 'Author', and set the second to 'Au_ID'.

The text boxes have been bound to the database - This is to say that Visual Basic will automatically fill the text boxes with the contents of the Authors and Au_ID fields as the user moves through the database with the data control.

We now have a working database application - and no code at all! Run it and see what happens when you press the arrows on the Data control - you are now looking at the database. If you type something new into the text box, it will be saved into the database when you click on the arrowsr. Try setting up additional text boxes and data controls to access different RecordSets and Fields.

Using the Validate Event

The validate event is called when the current record is about to change. It is invoked either by code, or when the user clicks on one of the arrows on the data control. It can be used to confirm the data entered in associated text boxes, and to stop data being saved into the database.

Private Sub object_Validate _
   ([ index As Integer,] _
   action As Integer, save _
   As Integer)

The action part contains infomation about why the event was invoked. Values of 1 to 4 indicatate that the user has clicked on one of the arrows, or the equivalent action has been called in code. The other actions are invoked in code, which we will look at later. If you want to cancel, you can set action to 0. For example, to stop the user going back in the database, you could:

If Action = vbDataActionMoveFirst Or _
Action = vbDataActionMovePrevious Then
Action = 0
End If

The save part determines whether or not any changes will be saved into the database. If there have been no changes, then it will be false, but if one of the bound controls have been changed, or if the record has been changed in code, then this will be true. To stop data being saved into the database, you can set it to false. To see whether the individual bound controls have changed, you can use the control's DataChanged property. If this is true, then the content has been changed. You can then set this property to false if you don't want this individual field to be changed, but want all the others to be updated. For example, in a database of pupils, you may want to change the class that the pupil is in, but not the name or birthday. You could use the following code to confirm whether the user wants to update the database:

If Save = True Then
If MsgBox _
   ("Are you sure that _
   you want to make changes", _
   vbYesNo + vbDefaultButton2 _
   + vbQuestion) = vbNo Then
Save = False
End If
End If

That is about it for the data control, and what you can do with it, without significant amounts of code.

This article was originally published on Wednesday Nov 20th 2002
Mobile Site | Full Site