Working With Database

Working With Database

Creating a Database using Visual Data Manager

You can create a database of any type using a tool supplied with Visual Basic – Visual Data Manager. Now let us understand the steps involved in using Visual data manager to create a database of MS-Access and than a few tables in that database.

 

To start Visual Data Manager:

 

  1. Select Add-Ins menu from VBIDE ( Visual Basic’s IDE) and select  Visual Data Manager

Or

You can also run

“D:\PROGRAM FILES\MICROSOFT VISUAL STUDIO\VB98\VISDATA.EXE” to invoke Visual Data Manager.

 

Once Visual Data Manager has started it displays its main screen (see figure 16.1).

Figure  16.1: Initial screen of Visual Data Manager.

Creating a new database

The following procedure is used to create a new database of MS-Access type.

To create a new database using Visual Data Manager:

  1. Select File->New->Microsoft Access -> Version 7.0 MDB
  2. Visual Data Manager displays “Select Microsoft Access Database to Create”
  3. Enter the name of the database file that you want to create – PRODUCTS.MDB. Make sure you are in the folder in which you want to place the MDB file.
  4. Click on Save button to close dialog box and to create database. The database is created with the name PRODUCTS.MDB and it is empty.

 

After the database is created, Visual Data Manager displays two windows (see figure 16.2); one is Database Window, containing database objects, another one is SQL Statement Window, where user can enter any SQL command and execute it.

 

In database window if you click on + sign of properties node, you get the list of properties related to database.

Figure  16.2: Visual Data Manager after database is created.

Creating a table

The second step is creating a table in the database. Right now the database is empty. We will create a table called PRODUCTS, which contains the details of all products. The following is the structure of PRODUCTS table.

 

Column Name Data type Size Meaning
PRODNO Integer 2 Stores product number. Each product contains a unique product number.
PRODDESC Text 20 Stores product description.
QOH Integer 2 Stores the quantity on hand.
RPU Long 4 Store rate per unit.
PTYPE Text 1 Stores the type of the product. If product type is software then “S” is stored otherwise “H” is stored.

Table 16.1: Structure of PRODUCTS table.

 

To create Products table:

 

  1. Click right button anywhere in Database window to invoke popup menu.
  2. Select New Table option from popup menu.
  3. Visual Data Manager displays Table Structure dialog box ( see figure 16.3 ).
  4. Enter name of the table as PRODUCTS.
  5. Then click on Add Field button to add columns (see figure ).
  6. Enter the name of the first column – PRODNO.
  7. Select Integer from the Type drop down list box. Size is automatically set to 2.
  8. Check Required checkbox to indicate that this value is a mandatory value of the table.
  9. Click on Ok to add the Field to Field List list box of  Table Structure dialog box.
  10. Enter the details of PRODDES, QOH, and RPU as shown in table 16.8 .
  11. While entering details of PTYPE, enter condition for input validation as follows.

 

Validation Rule = “ in (‘S’, ‘H’) “

Validation Text = “Product type must be either S(Software) or H (Hardware)”

Default value  = “H”

 

Note: You need not enter quotes while entering the strings.

 

Validation Rule contains the condition that is to be satisfied, Validation Text contains the message that is to be displayed when the condition fails, and Default Value contains the data that is to be stored if no other value is supplied.

 

Click on Close button after all the details are entered.

Figure  16.3: Table Structure dialog box.

Figure 16.4: Add Field dialog used to add new fields.

Creating an Index

An index is used to enforce uniqueness and to increase speed of search. It is strongly advised that you create indexes on columns that you frequently use in searching. Let us create an index on PRODNO field of PRODUCTS table.

 

To create an index:

 

  1. In Table Structure dialog box click on Add Index button
  2. Visual Data Manager invokes Add Index to Products (where products is the name of the table ) dialog box as shown in figure 16.5.

Figure 16.5: Add Index dialog box used to add a new index.

3. Enter Prodno_idx as the name of the index.
4. Select prodno from the list of Avaialble Fields and that will be the column on which index is created.
5. Click on Ok to add index to Index List of Table Structure dialog box.
6. Click on Close button to dismiss Add Index dialog box.
7. Click on Build the Table button to create the table.
8. Products table appears in the list of nodes in Database Window.
9. If you open products table by clicking on + sign, you see three child nodes – Fields, Indexes and Properties.
10. Open Fields node to see the list of fields we have in the table.
11. If you click on a field, you get the list of properties of the field.

Creating Sales table

Use the same procedure as above to create sales table. Sales table contains the details of sales made so far. The structure of the table is given below.

 

 

Column Name

Data type

Size Meaning
INVNO Long 4 Stores the invoice number. Invoice number is automatically incremented by one for each new record.
PRODNO Integer 2 Stores number of the product that was sold.
QTYSOLD Integer 2 Stores the quantity that was sold.
RPU Long 4 Stores rate per unit at which product was sold.
DISCOUNT Long 4 Stores the amount of discount given on the total amount.

Table 16.2: Structure of SALES table.

 

Note: Turn on AutoIncrField checkbox for INVNO field and set Default value of Discount to 0.

 

AutoIncrement Field

As INVNO is to be automatically incremented for each new record, make INVNO an auto increment field by checking  AutoIncrField check box in Add Field dialog box at the time of creating  INVNO field.

 

 

Manipulating data using Visual Data Manager

Visual Data Manager could be used to add records to tables. Let us add few records to PRODUCTS and SALES table.

 

To add new records to Products table:

 

  1. Select products table in Database Window and click on right button to invoke popup menu.
  2. Select open option from popup menu.
  3. Visual Data Manager displays a dialog box, which allows you to perform various operations on the selected table products.

Click on Add button.

Figure 16.6: Dynaset form used to edit a table.

Enter details of a product. And click on  update button. You may use Cancel button to canel the change and move back without saving changes.

 

 

To edit records using Visual Data Manager:

 

  1. Invoke popup menu of the table that you want to edit
  2. Select Open option to invoke Dynaset/Snapshot
  3. Go to the record that you want to edit using Move next and Move Previous buttons at the bottom.
  4. Click on Edit button to enter into edit mode and make the necessary changes.
  5. Click on Update button to save changes.

 

To change the structure:

 

  1. Select the table whose structure you want to modify.
  2. Invoke popup menu using right button and select Design
  3. Make necessary modification. You can also add new columns and delete exisiting columns.
  4. Click on close to close Table Structure dialog

 

Ther are so many other operations that you can perform using  Visual Data Manager. For further details please consult online documentation on Visual Data Manager.

 

Working with DAO collections

DAO object model has a set of collections. It provides object that access and manipulate the data. It also provides objects that could modify the structure of the database, such as adding new tables, adding new columns to existing tables and so on.

 

In this section we concentrate on how to access and modify the structure of the database using collections provided by DAO. In chapter 17, we will discuss about object that are used to access and manipulate data.

 

What is a collection?

A collection is a collection of items, which can be accessed either by the position of the item or by the key of the collection. You will understand how to create and use collections in chapter 24. For now, we concentrate on a few collections in DAO object model that allows you to get information regarding the database.

 

The following are the important collections in DAO.

 

Collection What it contains
Tabledefs Contains the list of Tabledef objects. Each tabledef object contains the details of a single table in the database.
Fields Contains the list of Field objects.  Each field object contains the details of the a single filed of a table.
Indexes Contains the list of Index objects. Each index objet contains the details regarding an index.

Table 16.3: DAO collections used to get information regarding database.

 

There are a few collections other than what is listed in table 16.3. However the procedure for using all collections is the same. So once you understand how to use a collection, the rest will be easier to understand.

 

Now let us write a simple application to get information regarding the database that is selected by the user.

 

But before we start the application we have to understand a few more things about DAO and jet engine.

 

First, so far we have used DAO object Recordset through data control. But we didn’t create the object directly, instead Data Control created the objects. But now we have to create database object and use the object to access the database that is selected by the user.  For this we have to take the following steps.

 


Loading  Data access object into project

 

DAO is a collection of objects. Unless you load DAO into your project, you cannot access these object. When we used a data control then data control has done it for us. But now we have to make DAO available to the project.

 

To load DAO into project:

 

  1. Select Project-> References.
  2. Visual Basic displays list of libraries avaialable in the system.
  3. Goto Microsoft DAO 3.51 object library and turn on the check box.
  4. Click on Ok

 

Once you load DAO object library into your project you can create objects of the classes provided by DAO and through those objects you can access data as well as the structure of the database.

 

Opening a  Database

If you want to connect to “PRODUCTS.MDB”, which is a database file of MS-Access, you have to create an object of Database type and use OpenDatabase functions as follows.

Figure  16.7: References dialog where list of servers is displayed.

 

 

Dim db As Database

 

Set db = OpenDatabase(“d:\srikanth\vb60\products.mdb”)

Print db.Name

 

 

If you execute the above piece of code, it displays the name of the database file on the form.

 

First we have created an object of Database class, which is one of the avaialable classes in DAO object model. Then we have opened database by supplying the complete name of the .MDB file. Once database is opened it will be accessed using object DB. The last statement is to prove that we have established a connection to the database. If it is displaying the name of the database then connection is successful otherwise it is a failure.

 

Once we open a database then we can access any part of the database using database object. So now we proceed with our sample application in which we let user select an .MDB file using File related controls (FileListbox, DirListbox and DriveListBox). Once user selects the name of the MDB file we open the database and displays the list of tables in Tables listbox. When user selects a table in tables list box, we display the list of columns and indexes of the selected table.  All the details are accessed using the collections.

 

 

Sample Application  to Display the structure of database

  1. Start a new project in File->New project and select Standard Exe as the type of the project.
  2. Place the following controls on the form

 

  • Drive list box to display list of drives
  • Dir list box to displays the list of directories
  • File list box to display the list of .MDB files
  • list box to display the list of tables in the seleted .MDB file
  • list box to display list of columns of the selected table
  • list box to display the list of indexes of the selected table
  • command button to quit
  • A few label controls to display label list boxes and file controls

 

  1. Arrange all controls as shown in figure 8.

 

  1. Change the following properties of the controls

 

Control

Property

Value
File1 Pattern *.MDB
List1 Name LstTables
List2 Name Lstfields
List3 Name Lstindexes
Command1 Name Cmdquit
Caption &Quit
Label1 Caption Drives
Label2 Caption Directories
Label3 Caption .MDB Files
Label4 Caption Tables
Label5 Caption Fields
Label6 Caption Indexes
Form1 Caption Database Structure

 

  1. Declare an object of Database class at module level using General/Declarations.

 

General/Declarations

Dim db As Database

 

Write the following code to connect three file controls.

 

Private Sub Dir1_Change()

File1.Path = Dir1.Path

End Sub

 

Private Sub Drive1_Change()

 

Dir1.Path = Drive1.Drive

 

End Sub

Listing  16.1: Code to connect DriveListBox to DirListBox. And DirListBox to FileListBox.

 

When user selects an .MDB file in file listbox, open that database and get the list of tables. While taking the list of tables ignore system tables. System tables do not contain user data. They contain information regarding the entire database.

 

The code to get the list of tables is as follows.

 

Private Sub File1_Click()

Dim fulldbpath As String

Dim td As TableDef

 

fulldbpath = File1.Path

If Right(File1.Path, 1) <> “\” Then

fulldbpath = fulldbpath & “\”

End If

fulldbpath = fulldbpath & File1.FileName

 

Set db = OpenDatabase(fulldbpath)

 

‘ get the list tables and add them to lsttables

lsttables.Clear

‘ Take each element of the Tabledefs collection and add the name of the element to litsbox.

For Each td In db.TableDefs

‘ add only nonsystem tables

If (td.Attributes And dbSystemObject) = 0 Then

lsttables.AddItem td.Name

End If

 

Next

 

End Sub

Listing 16.2 : Displaying lits of tables when user selects a data file.

 

The above code has used OpenDatabase function to open the MDB file.  It also used Tabledefs collection of the database to get the list of tables. To get the list of all items in the collection we have used the special version of for loop that is meant for collections.

 

For Each .. Next statement

This loop is used to repeatedly execute statements for each element of a collection or an array.

 

The syntax of it is as follows.

 

For Each element In group
[statements] [Exit For] [statements]

Next [element]

 

Element is a variable of variant type or a specific object type or a generic object type (declared as Object).

Group is the name of the collection or array.

 

When user selects a table in lstTables list box, the list of fields and list of indexes should be added to lstFiled and lstIndexes listboxes respectively.  Here is the code to do that.

 

 

Private Sub lsttables_Click()

Dim tn As String

Dim fld As Field

Dim idx As Index

 

tn = lsttables.Text

‘ get the list of fields for the selected table

lstFields.Clear

 

For Each fld In db.TableDefs(tn).Fields

lstFields.AddItem fld.Name

Next

 

‘ get the list of indexes and add to listbox

lstindexes.Clear

For Each idx In db.TableDefs(tn).Indexes

lstindexes.AddItem idx.Name

Next

End Sub

Listing 16.3:Code to get the list of Tables & Indexes.

 

The name of the table is used to get the required element from Tabledefs collection. With the help of Tabledef object (retrieved using Tabledefs(tn)) we accessed  Fields and Indexes collection.

 

Test run

Run the application that we have created to test whether it does the job or not.

 

  1. Run the sample application using F5.
  2. Select MBD file  in .MDB files list.
  3. It displays the list of user tables of NWIND.MDB.See figure 16.8
  4. Goto Tables list and select EMPLOYEE table.
  5. It displays the list of Fields and Indexes of EMPLOYEES table in lstFields and lstIndexes list boxes. See figure 16.8.

Figure  16.8: Sample application during runtime.