Chapter 5. Database Programming in VB 6.0

Chapter 5. Database Programming in VB 6.0

Collection of data stored in a structured manner is called database. A database Management System (DBMS) is used to interact with the database. Business companies stores their data in a database.

          Database consists of inter related data in a table manner (rows and columns). VB is used to communicate or access the data in a database.

          So in a database program, VB act as a front end tool. Because VB application provide interface between the user and database. Also the database is called backend tool. Because database provides data needed to the application behind the application.

          A database consists of Fields, Records. Smallest unit of stored data is called field. The collection of related fields is called records.

Visual Data Manager

          VDM is a tool in VB used to designing database.

Steps for creating database

Step1:- On VB IDE Click Add-ins àVisual data manager in menu bar

It opens VisData window is given below.

Step2:- Then click FileàNewàMicrosoft AccessàVersion 7.0 MDB

It opens a saving dialogue box.

Step3:- Enter the database file name (Eg: Student) and path. Here enter the file name and select the path (drive and folder) on the above dialog box. The extension of the database file is .MDB. Then click save button

It opens database window is given below. 

Step4:- Right click on database window and select new table. 

It opens a table structure window.         

Step5:- Here enter the table name (Eg. Studentscore). Then click add fields button. 

It opens a Add Field window for entering the field name, type, size etc.

Step6:- Enter field name, data type, size etc. Click OK after each entry.

Step7:- After field entry click the close button and press the build table button in table structure window. Now the Database created with name ‘student’ and table name ‘student score’.

Step8:- To add data to the database click right button on table name on database window, then click open. 

Step9:- It opens a Dynaset window with buttons shown in figure. Then click add button for adding data to the corresponding fields. 

Step10:- Enter data to the fields Slno, name, score and grade. Click update after each entry to save data to the database. Then click close button and close the database window.

Data Control

In VB Data control is used to connect and interact with the database to the application program.

Previous record

Next record

Beginning of record

End of record

          By using this we can connect database to an application program, open table, can add new records, delete records, update records and edit records.

Important properties of data control

They are used to connect or access the data in a database to a VB application program. They are,

Connect-It specifies the type of data base. Here we select MS Access

Databasename- It contains the path name of the database file with extension .mdb

Recordsource- It is used to select appropriate table in the data base.

Readonly- It specify the database to be opened as red only (Can’t edit data)

Recordsettype- It indicate the record set like dynaset, snapshot and table

Recordset

They are views for data base. They are,

Dynaset: They created by SQL commands. By using this we can edit, add, delete and update the data in a database. So it is more flexible.

Snapshot- They are also created with SQL commands. Here we can edit, update, delete, edit and add data in to the data base.

Table- They are simple. It contains the records from a table. Here we can add, delete, edit and update data in the data base.

Record set navigation

It means locating different records of table using data control. It can be done in two ways, directly using the data bound controls and using the methods of record set property of data control.

Directly using data bound controls

The buttons on a data control is called data bound control. The buttons include first, next, previous and last. A text box containing a data from a data base is called data bound text box. For the above method the properties of text box like data source and data field are used.

Using the methods of record set property of data control.

It is the flexible way to navigate data base. They are done in two ways, By using move method and find method.

Move method

They are,

Movefirst àTo view the first record in the data base.

Eg:- Data1.Recordset.Movefirst.

Movenext à To view the next record in the data base.

Eg:- Data1.Recordset.Movenext.

Moveprevious à To view the previous record in the data base.

Eg:- Data1.Recordset.Moveprevious.

Movelast à To view the last record in the data base.

Eg:- Data1.Recordset.Movelast.

Find method

They are

Findfirst àTo view the first record in the data base with a condition.

Eg:- Data1.Recordset.Findfirst “mark>=80”.

Findnext à To view the next record in the data base with a condition.

Eg:- Data1.Recordset.Findnext “Name=Shinu”.

Findprevious à To view the previous record in the data base with a condition.

Eg:- Data1.Recordset.Findprevious “Place=Kannoor”.

Findlast à To view the last record in the data base with a condition.

Eg:- Data1.Recordset.Findlast “Price=16760”.

EOF and BOF property of record set

EOF property

It is used to detect the end of record set. If it is true we can display a message or done other operations.

Eg:- if data1.recordset.eof then

          Msgbox “End of record set”

End if

BOF property

It is used to detect the beginning of record set. If it is true we can display a message or done other operations.

Eg:- if data1.recordset.bof then

          Msgbox “Beginning of record set”

End if

Record set manipulations

They are used to add, edit, update and delete records in a data base. The following methods are used for this purpose.

Addnewà To add new record at the end of the record set in the data base.

Eg:- Data1.recordset.addnew

Delete à To delete a particular record from the data base.

Eg:- Data1.recordset.delete    

Edit à To edit the data in the current record in the data base.

Eg:- Data1.recordset.Edit

Updateà To save the changes made by the above method. This method should be used after each other manipulations to save the changes.

Eg:- Data1.recordset.update