What I think is the most compelling thing about Visual Basic is it's easy way of accessing and modifying databases. This is what I think you should learn next; you will find many applications for this knowledge. I almost never make a program without using a database for data storage.

There are many ways to work with databases in Visual Basic, and I would think you have at least glanced at the Data control. I will not even mention the Data control further in this text, since it is so easy to use and too limited to be interesting for a professional developer. (Ok, there are some exceptions to this.)

What I will teach you to use in this text is DAO (Data Access Objects). You will get familiar with opening a database and retrieving/adding/deleting/updating records from tables. I will only use an Access Database (*.mdb) in my examples, since this is the most used DBMS (DataBase Management System) for smaller applications made in Visual Basic. We will at the end of this lesson have made a simple, yet functional, phone book application.

This text requires some knowledge of the Visual Basic programming language and you should be familiar with the Visual Basic IDE (Integrated Development Environment).

The first thing you must do in your application is to open a database where your tables are stored. You need to declare a variable to hold your database in order to do this. This is done with:

This gives you a variable/object that can hold a reference to your database. To open a simple Access database named "MyDatabase.mdb", do this:

You should really specify the complete path to the db, but if your current directory is the directory where the database is situated, this will work.

So, now you have opened a database. This won't give you any data. What you need to do is open a table in the database. You're not limited to open a single table; sometimes you have two or more tables that are related to each other and linked together w ith foreign keys, and there are ways to handle this to. But in this "Visual Basic - Database Primer" I will only show you how to open a single table.

Visual Basic uses an object called RecordSet to hold your table. To declare such an object and to open the table, do this:

Dim rsMyRS As RecordSet

Set rsMyRS = dbMyDB.OpenRecordSet("MyTable", dbOpenDynaset)

What happened there? Well, I declared a RecordSet object and used the Database object's OpenRecordSet method to open a table of type Dynaset. You can open a RecordSet in several modes. VB's online help file explains the different modes and what they ar e for. The Dynaset mode is the mode I use mostly. It gives you a RecordSet that you can add, delete and modify records in.

Now that we have opened a table (referred to as RecordSet from now on) we want to access the records in it. The RecordSet object allows us to move in it by using the methods MoveFirst, MoveNext, MovePrevious, MoveLast (among others). I will use some of these to fill up a list box with the records of our RecordSet.

To get this example to work, make a database (with Access) called "MyDatabase.mdb" with the table "MyTable" in it. This table should have the fields "ID" of type "Counter" that you set to be the primary key, the field "Name" of type Text and a field "P hone" of type Text. Add some records to it. Put a list box on a form and call it "lstRecords".

Dim dbMyDB As Database
Dim rsMyRS As RecordSet

Private Sub Form_Load()

Set dbMyDB = OpenDatabase("MyDatabase.mdb")
Set rsMyRS = dbMyDB.OpenRecordSet("MyTable", dbOpenDynaset)

If Not rsMyRS.EOF Then rsMyRS.MoveFirst
Do While Not rsMyRS.EOF
	lstRecords.AddItem rsMyRS!Name
	lstRecords.ItemData(lstRecords.NewIndex) = rsMyRS!ID
	rsMyRS.MoveNext
Loop

End Sub

This will make the list box fill up with your records when the form loads. I have introduced some new concepts with this example. We have all ready covered the first part where we open the table. The line that says If Not rsMyRS.EOF Then rsMyRS.M oveFirst tells the program to move to the first record in case there are any records at all. The EOF is a Boolean property that is true if the current record is the last. It is also true if there are no records in the RecordSet.

Then we make the program add the "Name" field of all records to the list box by adding the current records field "Name" and moving to the next record. You ask for a field of a RecordSet by putting a ! between the name of the RecordSet object and the na me of the field. The while loop checks to see if there are more records to add.

You might have wondered why I put the value of the field "ID" in the list box's ItemData property. I did this so that we would know the primary key for all the records in order to search for a record.

Put a text box somewhere on the form and call it "txtPhone". Then copy the following code to the project.

Private Sub lstRecords_Click()

rsMyRS.FindFirst "ID=" & Str(lstRecords.ItemData(lstRecords.ListIndex))
txtPhone.Text = rsMyRS!Phone

End Sub

This will display the phone number of the selected person when clicking in the list box. It uses the FindFirst method of the RecordSet object. This takes a string parameter that is like what is after WHERE in a SQL expression. You state the field that you want to search in (here "ID"), then the evaluation criteria (here "=") and last the value to search for (here the ItemData of the selected item in the list box).

So what we did was to search for the record with the "ID" field value that was the same as the ItemData property of the selected item in the list box. Then we show the value of the "Phone" field in the text box.

You will probably want to be able to update some value of some field when doing database programming. This is done with Edit and Update. We will try to change the value of the "Phone" field by editing the text in the text box and clicking a button.

Put a command button on the form and name it "cmdUpdate". Then copy the following code to the project.

Private Sub cmdUpdate_Click()

rsMyRS.Edit
rsMyRS!Phone = txtPhone.Text
rsMyRS.Update

End Sub

Could it be that simple? Yes. This changes the phonenumber of our selected person. Or to put it technically: This changes the value of the "Phone" field of our current record. Imagine the current record being a set of boxes, with a field in each box. T he Edit method takes the lid off all of the boxes and Update puts them back on. When we write rsMyRS!Phone = txtPhone.Text we replace the content of the "Phone" box with the content in the text box.

Deleting records couldn't be simpler. To delete the current record you just invoke the Delete method of the RecordSet object. We will put this feature in our little project. Make one more command button named "cmdDelete" and the following code will do the work of deleting our currently selected person.

Private Sub cmdDelete_Click()

rsMyRS.Delete
lstRecords.RemoveItem lstRecords.ListIndex

End Sub

I won't even bother to explain that in greater detail =). The first statement deletes the record and the second removes the list box entry.

Adding records is much like updateing, except you use AddNew instead of Edit. Let's add one more command button to our application. Let's call it...errh...let me see...yea! "cmdNew" =). Here is the code that adds a new record.

Private Sub cmdNew_Click()

rsMyRS.AddNew
rsMyRS!Name = "A New Person"
lstRecords.AddItem rsMyRS!Name
lstRecords.ItemData(lstRecords.NewIndex) = rsMyRS!ID
rsMyRS!Phone = "Person's Phone Number"
rsMyRS.Update

End Sub

I will use the box analogy to explain this. The AddNew method takes a set of new boxes and adds them to our RecordSet. We then put some new values in them and close the lids with Update. As you can see we never stated any valu e for "ID", but as you remember, this is a field of type "Counter" wich automatically gets a unique value. The code also adds this new record to the list box so that we will be able to change the phone number of this person. I leave it up to you to add th e feature of changing the name.

Join the mailing list to recive updates and source code Click Here