VB Access Programming Simplified

Visual Basic Access Database Programming.



Ok. You have come to understand the basic of Visual Basic and you want to move on. 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).


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 to. But in this "Visual Basic - Database Primer" I will only show you how to open a single table.


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 are 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 . Move First 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 listRecords_Click()

rsMyRS.FindFirst "ID=" & Str(lstRecords.ItemData(lstRecords.Listlndex))
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 WHERB 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. The 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 and Adding records

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.Removeltem 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 updateing, 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


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 value for "ID", but as you remember, this is a field of type "Counter" which 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 the feature of changing the name.