Brief introduction to the usages of Access data bases

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:

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