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:
- Select Add-Ins menu from VBIDE ( Visual Basic’s IDE) and select Visual Data Manager
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:
- Select File->New->Microsoft Access -> Version 7.0 MDB
- Visual Data Manager displays “Select Microsoft Access Database to Create”
- 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.
- 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:
- Click right button anywhere in Database window to invoke popup menu.
- Select New Table option from popup menu.
- Visual Data Manager displays Table Structure dialog box ( see figure 16.3 ).
- Enter name of the table as PRODUCTS.
- Then click on Add Field button to add columns (see figure ).
- Enter the name of the first column – PRODNO.
- Select Integer from the Type drop down list box. Size is automatically set to 2.
- Check Required checkbox to indicate that this value is a mandatory value of the table.
- Click on Ok to add the Field to Field List list box of Table Structure dialog box.
- Enter the details of PRODDES, QOH, and RPU as shown in table 16.8 .
- 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:
- In Table Structure dialog box click on Add Index button
- 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.
|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.
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:
- Select products table in Database Window and click on right button to invoke popup menu.
- Select open option from popup menu.
- 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:
- Invoke popup menu of the table that you want to edit
- Select Open option to invoke Dynaset/Snapshot
- Go to the record that you want to edit using Move next and Move Previous buttons at the bottom.
- Click on Edit button to enter into edit mode and make the necessary changes.
- Click on Update button to save changes.
To change the structure:
- Select the table whose structure you want to modify.
- Invoke popup menu using right button and select Design
- Make necessary modification. You can also add new columns and delete exisiting columns.
- 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:
- Select Project-> References.
- Visual Basic displays list of libraries avaialable in the system.
- Goto Microsoft DAO 3.51 object library and turn on the check box.
- 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”)
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
- Start a new project in File->New project and select Standard Exe as the type of the project.
- 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
- Arrange all controls as shown in figure 8.
- Change the following properties of the controls
- Declare an object of Database class at module level using General/Declarations.
Dim db As Database
Write the following code to connect three file controls.
Private Sub Dir1_Change()
File1.Path = Dir1.Path
Private Sub Drive1_Change()
Dir1.Path = Drive1.Drive
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 & “\”
fulldbpath = fulldbpath & File1.FileName
Set db = OpenDatabase(fulldbpath)
‘ get the list tables and add them to lsttables
‘ 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
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]
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
For Each fld In db.TableDefs(tn).Fields
‘ get the list of indexes and add to listbox
For Each idx In db.TableDefs(tn).Indexes
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.
Run the application that we have created to test whether it does the job or not.
- Run the sample application using F5.
- Select MBD file in .MDB files list.
- It displays the list of user tables of NWIND.MDB.See figure 16.8
- Goto Tables list and select EMPLOYEE table.
- 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.