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
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.