One of the best things about Visual Basic is it's easy way of accessing and modifying databases. You will find many applications for this knowledge.

There are many ways to work with databases in Visual Basic, there’s the Data control. I will not cover the Data control further in this text, because it is so easy to use and it can be too limited to be interesting for a professional developer.

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.

Database Object

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:

Dim dbMyDB As Database   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:   Set dbMyDB = OpenDatabase("MyDatabase.mdb")

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 with foreign keys, and there are ways to handle this too.

RecordSet Object

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.

Accessing records

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 "Phone" 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.MoveFirst 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 name of the field. The while loop checks to see if there are more records to add.

Searching the RecordSet

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.

Updating the Database

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 phone number 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

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

Adding records is much like updating, except you use AddNew instead of Edit. Let's add one more command button to our application. Let's call it 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