Computers are mainly used for keep track of information. In this unit we will go on creating files by using Visual Basic data manager and adding deleting the records and so on…
Visual data manager
Visual Data is an application that was created using Visual Basic itself. A built version of Vis Data is used as an add-in, accessible from the add-Ins menu in the Visual Basic figure
Figure: Visual Data Manager Option in Visual Basic Ad-Ins menu, which is used to activate the Visual Data Manager.
It is used to create database, tables, queries, and indexes and modify them. When an existing database is opened the visual data manager displays the contents and properties manager window with the BIBLIO database open in design mode.
Figure Vis Data Manager
Creating a database using a Visual Data Manager
To create a new database, choose file| New| Microsoft Access| Version 7.0 MDB. A dialog box will appear asking path and filename to store the database being created. Give a file name and choose desired location to save the database.
There is one more option in the Microsoft Access Submenu, Version 2.0 MDB, which is used to create database Inversion 2.0 format. Still man concerns use applications, which are built using earlier version of Visual Basic. They may require database in older format. In this case along, the required database can be created using version 2.0 MDB option. Otherwise database can be created in latest version format (why to look back the stone Age world).
| Note: to open an already existing access database choose File |Open database Microsoft ccess Access |.And select the database name in the open Microsoft access database dialog box. |
Creating a simple database
This section shows how to create a simple database students with a table students Dt1 (student details), which contain fields student, student name and Total Marks. It will be useful for rest of this chapter.
Choose File |New | Microsoft Access| version 7.0 MDB.
Type students in the file name text box and click OK.
Right click on te database window and choose New table.
In the table Structure box type student Dtls in the table Name Text box.
Click Add field button in the Table structure box to ad fields.
Type the value as given below in the corresponding text boxes.
Text box Value
Name Student Id
Type Text
Size 10
Validation Text Student Id is not specified
Validation Rule <>’’’’
- Click Ok to add the field name. The field Dialog box will still remain to accept another field. Proceed for the field student name with the value given below.
Text box Value
Name Student Id
Type Text
Size 30
Validation Text Student Id is not specified
Validation Rule <>’’’’
Now click Ok and proceed for the field Total Marks with the values given in the following:
Text box Value
Name Total marks
Type Integer
Validation Text Total marks is not specified or negative
Validation Rule >=0
Click OK and close to close the Add field dialog box.
Click build Table button in the Table structure dialog box to complete the creation of table students.
Creating a table
Once a database is created, it is ready to give birth to tables. To create a table, Right clicks in the database window and choose New table from the context menu Figure 9.3
Choosing the above option will bring a dialog box called Table structure dialog box Enter the name of the table being created in the table name textbox. And to add fields to the table follow the steps given in the next section.
Adding Fields
In the table structure dialog box click the Add field button to display the Add field dialog box. It appears as in figure 9.4
Add Field Dialog Box Options
The options of the Add field dialog box are given below.
Name
Allows to type the name of the field being added.
Ordinal position
Allows to determine the relative position of the field.
Type
Lets to determine the operational or data type of the field such integer, text etc.
Validation text
Lets to add a text of the message that the application displays if a user tries to enter an invalid value for a field.
Size
Lets to determine the maximum size, in bytes, of the field.
- Fixed field – If selected creates a field with a fixed size.
- Variable field – If selected, allows the user to modify the size of the field by dragging its borders.
Validation rule
Lets to determine what data is valid in a field as it is added. It is a string that describes a comparison in the form of an SQL WHERE clause, but without the WHERE reserved word. For example, if the value of the customer Age must be positive, then its validation Rule string will be “>0”
Default value
Lets to determine the default value for the field
Autoincrfield
Automatically gets value that is incremented by one from the last record when a new record is added.
Allowzerolength
Allows having a zero-length string as a valid setting.
Required
Indicates if the field requires a non NULL value.
Ok
Appends the current field definition to the current table.
Close
Closes the form when you are finished adding fields.
Opening the tables
To open a table select the table in the database window, right clicks on it, and chooses open, – or – Simply double clicks
This will open the table in a separate form (can be termed as table form) as shown in the following figure 9.5
Figure: Opened Publishers table in VisData.
In this form records can be added, deleted and updated. The following steps how to add, delete, and Edit the records to the student Dtls table.
Manipulating recodes using Visual Data Master
Double click on the table name student Dtls in the database window to open it
As no record is added to the table, the student Dtl table form will appear in ready state to accept values for new record as given below figure 9.6
Figure 9.6 Student Dtls table form in ready state to accept data. Enter the following values in the corresponding text boxes.
Field name Value
Student Id 1005
Student name Malarvizhi
Total marks 450
Click update button to commit new record addition.
Now the cancel button will be replaced by add button.
From now onwards to add recodes, Add button is to be clicked first, values to be entered next and finally update button is to be clicked. By following these steps enter the data given below to add five more records.
Student Id Student Name Total Marks
1001 Kumar 475
1002 Raju 480
1003 Shanmugam 466
1004 Vanisaraswathy 450
1005 Selva sudari 455
| Note: while entering records instead of checking update button, cancel button can be clicked to |
cancel the record addition process.
At the bottom to the student Dtls, these are a control called Data control. It contains four buttons that enables to navigate the records such as moving to first record, moving to previous record, moving to next record and moving to last record. The following figure shows the data controls with its button’s description.
Figure 9.10 Data control
To delete a record, use the data control to move to the record, which is to be deleted and click delete button. When VisData manager ask confirmation, click yes deletion. Clicking no will abort the deletion.
To edit a record, use data control to move to the required record, modify the record and either click update or any one of navigation button, VisData will ask whether to commit changes as shown below.
Figure: VisData asking confirmation to commit changes.
Click yes to commit changes or No to cancel the changes.
Introduction to data control
Many modern applications are concerned with the storage, organization and retrieval of data, to address the needs of the software developers creating these products, visual basic provides a rich set of data access features. Visual Basic provides many standard controls, such as text boxes and labels that can be bound to the Data control, in addition to the DBCombo, DBGrid and DBList. These controls are provided specifically for binding to a data control.
A data control binds data aware controls to Microsoft Access or to other ODBC data sources, enabling to move from record to record and to display and manipulate data from the records in bound controls. A bound control is a control that is assigned to a field in a database.
Most data access operations can be performed with a data control without writing any code. If a database and a record source a table, a view a SQL statement is set to a data control. Then it is automatically populated with data from that database when the form that has the data control, is activated.
The following figure shows the data control and DAO with bound controls.
Figure: The data control, DAO, and bound controls
The intrinsic Data control on the Visual Basic toolbox, implements data access by using the Microsoft jet Database engine the same database engine that powers Microsoft Access. This technology gives seamless access to many standard database formats and allows to create data aware applications without writing any code. The intrinsic Data control is best suited to smaller (desktop) database, such as Access and ISAM databases.
Data control’s Icon
The intrinsic Data control can be used to create applications that display, edit, and update information from many types of existing databases, including Microsoft access, Btrieve, dbase, Microsoft FoxPro, and paradox.
Data control properties
Align Set or return a value that determines how the control is placed on the form.
Appearance Which determines whether or not to use 3D appearance.
Back color A numeric values that determines the control’s background color
BOF Action Which determines how the data control reacts to EOF and BOF conditions.
Caption Set or return a sting representing the object’s caption
Connect Set or return the type of database being used.
Database Which is read only property that exposes the data controls underlying data-
base object.
Data field Which sets or return a value that binds the control to a field in the current
record.
Drag Icon Set or return the icon to be used for drag operation
Drag Mode Set or return the type of drag mode available.
EOF Action Which determines action when the record set’s EOF is reached
Exclusive This reads or set whether a database is opened for single user or multi-user
access. Read and write at both design time and runtime.
Options Set or return one or more characteristics of the record set object
Recordset The data (set of recorded) available to the data control
Recordset type Sets or return the type of Recodest object of the data control
Record source Which specifies the part of the database seen by the control.