UNIT 6 : VISUAL BASICPROJECT
Key Unit Competency
To be able to connect Visual Basic Interface to Database and create a simple Visual
Basic standard desktop applications for a real life situation.
Introductory activity
GS Gisakura is a Twelve- Years- Basic- Education School that has a big number of students. The school is facing the problem of students’ information management and is
looking for an application to manage its students’ registration.
After elucidating the requirement specifications of the GS Gisakura's students’ registration system do the following:
1. Using Visual basic, create a front end interface for the GS Gisakura
students’ registration system.
2. Using MS access, create the database for the GS Gisakura’s students
registration system.
3. Using Open Database Connectivity (ODBC), link the front and back end
interfaces for the GS Gisakura students’ registration system.
4. Using Data Access Objects (DAO), Data controls and properties;
manipulate data of the GS Gisakura students’ registration system.
5. Design the front end interface basing on ergonomics rules
6.1. PROJECT 1: REQUIREMENT ANALYSIS AND PROJECT
PLANNING
In every software project, you need to collect and analyze the information about
what you are going to do. This phase in Software Development Life Cycle (SDLC) is
called requirement’ analysis.
To develop a Visual Basic Project, all requirements specifications must be gathered
so that the planning of the project can be realized.
6.1.1. VB Project Planning
The project planning is an important phase of a software development. It works
on the breakdown of activities, the scheduling, the human force distribution, its
recruitment and capacity development. The cost of the project is also done by
considering the size of work and manpower needed for its realization.
As each project should have its objectives, for example, the student registration
system should manage student registration information where every student
information in the system has to be recorded, stored and should be modifiable (edit,
add new, delete and update) by the authorized users.
6.1.2. Software requirement analysis
The software requirement or specification requirement phase allows both the
developers and their clients to have a common understanding of the project and
avoids mistakes that should cause failures. The requirements can be functional and
nonfunctional. Functional requirements are those requirements concerning actions
to be performed by the new system while nonfunctional requirements concern the
constraints, performance, safety, security, timing and the quality of the new system.
In case of the software that manages a school, the Software requirement should help
to understand the status of the school in terms of computerization, the intended
software and how it will work with the users in this phase. So, there is a need to
take sufficient time by conducting extensive interviews with all the people who are
going to use the software. In this case, the school administrators, teachers, students
and the school secretary have to give their views.
After, it is better to structure all collected data and analyze them for the development
of the program. All technical limitations that may arise on the client’s side must be
considered before the development of a list of specifications that meet the user’s
needs.
The template to be followed for the software functional requirements is described
hereafter. The main template is follows the points in the tablebelow.
3. System Features
This template illustrates how to organize the functional requirements for the product
by system features and the major services provided by the product. This section can
be organized bymode of operation, user class, object class, functional hierarchy, or
combinations of these, whatever makes the most logical sense for your product.
4. External Interface Requirements
a. User Interfaces
Describe the logical characteristics of each interface between the software product
and the users. This may include sample screen images, any GUI standards or product
family style guides that are to be followed, screen layout constraints, standard buttons
and functions (e.g., help) that will appear on every screen, keyboard shortcuts, error
message display standards, and so on. Define the software components for which a
user interface is needed. Details of the user interface design should be documented
in a separate user interface specification.
b. Hardware Interfaces
Describe the logical and physical characteristics of each interface between the
software product and the hardware components of the system. This may include
the supported device types, the nature of the data and control interactions between
the software and the hardware, and communication protocols to be used.
c. Software Interfaces
Describe the connections between this product and other specific software
components (name and version), including databases, operating systems, tools,
libraries, and integrated commercial components. Identify the data items or
messages coming into the system and going out and describe the purpose of each.
Describe the services needed and the nature of communications. Refer to documents
that describe detailed application programming interface protocols. Identify data
that will be shared across software components. If the data sharing mechanism
must be implemented in a specific way (for example, use of a global data area in a
multitasking operating system), specify this as an implementation constraint.
d. Communications Interfaces
Describe the requirements associated with any communications functions required
by this product, including e-mail, web browser, network server communications
protocols, electronic forms, and so on. Define any pertinent message formatting.
Identify any communication standards that will be used, such as FTP or HTTP.
Specify any communication security or encryption issues, data transfer rates, and
synchronization mechanisms.
5. Other Nonfunctional Requirements
a. Performance Requirements
If there are performance requirements for the product under various circumstances,
state them here and explain their rationale, to help the developers understand the
intent and make suitable design choices. Specify the timing relationships for real
time systems. Make such requirements as specific as possible. You may need to state
performance requirements for individual functional requirements or features.
b. Safety Requirements
Specify those requirements that are concerned with possible loss, damage, or harm
that could result from the use of the product. Define any safeguards or actions that
must be taken, as well as actions that must be prevented. Refer to any external
policies or regulations that state safety issues that affect the product’s design or use.
Define any safety certifications that must be satisfied.
c. Security Requirements
Specify any requirements regarding security or privacy issues surrounding use of the
product or protection of the data used or created by the product. Define any user
identity authentication requirements. Refer to any external policies or regulations
containing security issues that affect the product. Define any security or privacy
certifications that must be satisfied.
d. Software Quality Attributes
Specify any additional quality characteristics for the product that will be important
to either the customers or the developers. Some to consider are: adaptability,
availability, correctness, flexibility, interoperability, maintainability, portability,
reliability, reusability, robustness, testability, and usability. Write these to be specific,
quantitative, and verifiable when possible. At the least, clarify the relative preferences
for various attributes, such as ease of use over ease of learning.
e. Other Requirements
Define any other requirements not covered elsewhere in the SRS. This might include
database requirements, internationalization requirements, legal requirements,
reuse objectives for the project, and so on. Add any new sections that are pertinent
to the project
6.1.3 Project1
Based on the request of your school to have a student registration system and by
following the software requirement specifications template given above, develop
the Requirements Document.
6.2. PROJECT2: FRONT END USER INTERFACE IN VISUAL BASIC
6.2.1. Understanding front end
In visual basic, the term Front End refers to the user interface, where the user
interacts with the program through the use of the screen forms and reports. The
Front End graphical user interface use buttons, text field and different icons to make
easy navigation of the application software.
6.2.2. Design the forms constituting a front end interface
Front-end interface for the GS Gisakura students’ registration layout are given below
and you are going to design the same layout and your own basing on SRS you have
elucidated in your school as case study.
6.3. PROJECT 3: BACK END DATABASE
(In Ms-Access, MySQL or any suitable RDBMS)
Database systems are comprised of a Front End and Back End. The Back End has the
tables that store data, including the relationships between the tables, data queries
and other behind the scenes technology that accepts information from and displays
information to the user via the Front End.
For example, when you are requesting for a birth certificate through Irembo there is
a number of information you provide through different forms and that information
is kept in the Irembo database tables.
Back ends can be built using different Relational Database Management Systems
such as Microsoft Access, SQL Server, Oracle etc. User at the front end of a system
does not need to know how data is stored and how it is modified or retrieved.
Note: If you create a program that access a database:
• Programming language are used as Front End. Example: Visual Basic, HTML.
etc.
• Database management systems are used as Back End. Example: Microsoft
Access, My SQL, SQL Server, Oracle, etc.
Below is layout of created table in design view. Create a database and name it
“school” and create a table whose “student_reg” as a name, using your suitable
Database management system (in this book we have used MS access 2016) with
the same field names as below. After, you are going to design and create your own
database basing on SRS you found in your school as case study.
6.4. PROJECT 4: CONNECTING A VISUAL BASIC 6.0 PROJECT
TO A DATABASE (ODBC configuration)
A visual basic database application has three main parts: user interface, the database
engine and the data storage.
• The user interface: Is the Media through which the user interacts with
the application. It may be form or group of forms, a window or an ActiveX
document form.
• The database engine: Connects the application program with the physical
database files. This gives you modularity and independence from the
particular database you are accessing. For all types of database, the same
data access object and programming techniques can be used in visual basic.
• The data storage: Is the source of the data. It may be a database or a
text file. Database processing has become an integral part of all types of
complex applications. A database is a system that contains different objects,
which can be used together to store data. Using visual basic, you can create
applications easily and make them have efficient access to data.
6.4.1. ODBC (Object Database Connectivity) configuration
a. ODBC overview
ODBC strands for Open Database Connectivity, a standard database access method
developed by the SQL Access group in 1992. The goal of ODBC is to make it possible
to access any data from any application, regardless of which database management
system (DBMS) is handling the data. ODBC manages this by inserting a middle
layer, called a database driver, between application and DBMS. The purpose of this
layer is to translate the application’s data queries into commands that the DBMS
understands. For this to work, both application and DBMS must be ODBC compliant.
b. Create an ODBC connection for the Database
Step 1. Click Start button, and select Control Panel. Once the control panel gets
opened, click on Administrative Tools or tape it in search control panel if you do not
locate the Administrative Tools or just tape ODBC in start button search and click
ODBC data sources(32bit)
Step 2. Now we are going to add new database, click Add button and new window
will appear “Create New Data Source”, select the Driver do Microsoft Access (*.mdb)
for MS Access, click Finish Button.
Step 3: We’re almost done, let’s configure the ODBC for MS Access, under Data
Source Name, type the MyDatabase, please leave blank the description it’s optional.
Now, let’s select the path of your database .mdb once you found it select OK button.
The image shown below means that the Database .mdb is successfully located. Click
OK button to Finish the configuration. (MS Access Database Configuration done).
6.5. PROJECT 5: DAO, RDO AND ADO OBJECTS IN VISUAL
BASIC
Using all steps and guidance below, create connectivity between your backend and
frontend
a. Introduction
You may build VB database applications using data control, however, data control is
not a very flexible tool as it could only work with limited kinds of data and must work
strictly in the Visual Basic environment. To overcome these limitations, we can use a
much more powerful data control in Visual Basic, known as ADO control.
Data control is a control that allows connecting a VB program to a text database such
as (Sql Server, MS Access, MySQL, Dbase, FoxPro…). That control allows moving in
data base records, display, and manipulating data in a list of the available data base
records. It is adjusted on the form of other objects and you can define its properties.
There are many types of data controls; in Visual Basic, there are three data access
interfaces:Dao (Data Access Object), RDO (Remote Data Object), Ado (ActiveX Data
Object). Thelatest among the three is ADO, which features a simpler yet more flexible
object model than either RDO or DAO.
1. Data access object (DAO) is an object that provides an
abstract interface to some type of database or other persistence
mechanism. By mapping application calls to the persistence layer, DAOs
provide some specific data operations without exposing details of the
database. This isolation supports the Single responsibility principle. It
separates what data accesses the application needs, in terms of domainspecific objects and data types (the public interface of the DAO), from how
these needs can be satisfied with a specific , database schema, etc. (the
implementation of the DAO).
2. Remote Data Objects (abbreviated RDO) is the name of an
obsolete data access application programming interface primarily used
in Microsoft Visual Basic applications on Windows 95 and later operating
systems. This includes database connection, queries, stored procedures,
result manipulation, and change commits. It allowed developers
to create interfaces that can directly interact with Open Database
Connectivity (ODBC) data sources on remote machines, without having
to deal with the comparatively complex ODBC API (Open Database
Connectivity- Application Programming Interface). Remote Data Objects
was included with versions 4, 5, and 6 of Visual Basic; the final version of
RDO is version 2.0.
3. ActiveX data Object (ADO)
This control helps us to access a database data offering the possibility of
working on different data sources such as text files, relational data base etc.
i.Add ADO control
ADO controls contain multitudes of objects having properties, methods and events.
Access-connection: It allows your program to access a data source using a connection.
Its environment deals with transferring data.
ii. Recordset:
this property work with the records which can be accessed by an ADO control. Most
of methods are associated to this property. You can use this property to count the
number of records.
For example using the command: CmdRecordCount()
Private Sub CmdRecordCount_click()
Adodc1.Recordset.Movelast
MsgBox Adodc1.RecordSet.RecordCount ‘to count the number of records
End Sub
• Field: correspond to the fields of a database which are connected to the
program.
• Error: Errors may occur when a program fails to connect t, execute a
command or perform a given operation.
• Event: ADO uses the concept of events as other VB interfaces do, you can
use also event procedures.
• Recordsource: Recordsource property specifies the source of the records
accessible through bound controls on your form. If you set the recordsource
property to the name of an existing table in the database, all of the fields
in that table are visible to the bound controls attached to the Data control.
• Datasource: The datasource property specifies an object containing data
to be represented as a recordset object. It is used to create data-bound
controls with the data environment in visual basics.
Eg: a datasource can be a spreadsheet, text file.
To add the ADO control on the form perform the following steps:
1. Select the command Component in the project menu
2. Click on Controls in the dialog box which opens
3. Select Microsoft Ado Data Controls 6.0 (OLEDB)
4. Click ok button
5. This control will be added to the toolbox.
6. To connect ADO objects on the data source use a « ConnectionString »
and specify the datasource (the database name you want to connect)
7. Then you have to specify the access path after clicking the connection
property.
Let us go a bit details and do practice
Select components in Project Menu or use keyboard shortcuts CTRL+T In
components check in check boxes for:
• Microsoft ADO Data Control 6.0 (OLEDB) &Microsoft DataGrid Control 6.0
(OLEDB) By right click in tool box, we obtain also components
• Double click on the form and write the following codes:
Private Sub Form_Load()
Adodc1.ConnectionString = “”
Adodc1.RecordSource = “select * from student”
Set DataGrid1.DataSource = Adodc1
End Sub
• Create a connection string by making right click on adodc control which is
on the form, then select properties.
• On the properties page which opens, choose build and select the path
b. Method of the data control
Data control properties
• Name: you can keep data1 or put the one of your choice
• Caption=put your own message or leave it blank
• Connect=access
• Database name= browse and get the Employee database.
• Recordset type= table
• Record source=name of the table
• Visible= choose false so that when you run your application, the data
control will not appear
Data control has many methods like move first, move last, move next, and move
previous which are used to move through the records.
MoveFirst: will move the record pointer to the first record in the recordset.
MoveNext: will move the record pointer to the next record in the recordset
MovePrevious: will move the record pointer to the previous record in the recordset
MoveLast: will move the record pointer to the last record in the recordset
6.6. PROJECT 6 DATA CONTROLS, PROPERTIES AND DATA
MANIPULATION AND CODING
(adodc, recordset, addnew, delete, update, movenext, movelast, movefirst,
moveprevious code in vb for data manipulation)
To create an ADO Data Control that exposes a Recordset in your application, at the
minimum you need to do the following:
• Specify a Connection by filling in the ConnectionString property.
• Specify how to derive a Recordset by setting the RecordSource property
(which is a complex property requiring its own dialog box to set up).
The detailed steps are as follows:
6.6.1. Setting up ADO Data Control in Visual Basic 6.0
a. Adding ADODC (ADO Data Control) on toolbox
To add ADO Data control, make right click on toolbox dialog box and choose
component, then you will have a dialog box and tick Microsoft ADO Data Control
6.0(OLEDB) and click OK as shown below:
b. RECORDSET
Recordset property:
A recordset object provides a logical representation of a record in a table, or in the
results of a query. There are three types of recordset object:
1. Table: this type of recordset provides access to all of the record and field
in a specified table. The contents of records can be updated using the
table type, but only one table can be referenced by this type of recordset.
Data access is faster, since it has a direct reference to the table.
2. Dynaset: the dynaset type represents a selected (by SQL) set of records
from one or more tables. The records and fields contained in a dynaset
type are specified by a query. The data can be modified in the case of
dynaset type recordset.
3. Snapshot: the snapshot type recordset is similar to a dynaset but it is a
read only. The data cannot be updated. So you cannot use snapshot type
to modify records in a table. Snapshot type can be used if you want only to
view data or fill drop down from a table.
In a recordset, two special positions of record pointer are BOF and EOF.
• BOF is the beginning of the file before the first record
• EOF is the end of the record after the last record. The record pointer is to the
first record when BOF is reached and the last record when EOF is reached
Use of ADO control to connect database fields to different VB objects
Ho to connect database fields to textbox?
1. Select a textbox to connect
2. Press f4 key to display the property window.
3. In data source property, select the name of the connection control (here it
is Adodc1)
4. To display a given field, put the field name in « DataField »
Use of methods
Add record into a database
b. To add a record into a database we use the method AddNew.
Syntax: Controlname.RecordSet.AddNew
Ex. Adodc1.RecordSet.AddNew
c. Delete a record
To delete a record we use delete method:
Private Sub CmdDelete_Click()
Adodc1.RecordSet.Delete
End Sub
To prevent the display of blank record, we move the record:
Private Sub CmdDelete_Click()
Adodc1.Recordset.Delete
Adodc1.Recordset.MoveNext’ prevent the error in case a record is not available
End sub
d. Refresh method
Refresh method is used to refresh database data; it allows to update the controls
according to the new values of the fields of a table.
Ex. Adodc1.Refresh
e. Update database fields
To modifying a database fields, you have to use the update method
Private Sub CmdUpdate_click()
Adodc1.UpDateRecord or
Adodc1.Update
End Sub
f.MoveNext method
Put the record pointer to the next record
Private Sub CmdNext_Click()
Adodc1.Recordset.MoveNext
End sub
g. MovePrevious method
Put the pointer on the previous method
Private Sub CmdPrevious_Click()
Adodc1.Recordset.MovePrevious
End Sub
h. MoveFirst method
Put the pointer on the first record of the database
Private sub CmdFirst_Click()
Adodc1.Recrdset.MoveFirst
End sub
i.MoveLast method
Put the pointer on the last record
Private Sub CmdLast_Click()
Adodc1.Recordset.MoveLast
End sub
Counting the records number
We use the method RecordCount:
Private Sub CmdCount_Click()
Adodc1.Recordset.MoveLast
MsgBox adodc1.Recordset.RecordCount
End sub
6.6.2. Building the interface and accessing the database • Interface is required to access a related database in order a user can add,
modify, retrieve data and so many other interactions.
• Drawing the interface, you have to set properties and then coding
• You have to add the data control to connect your database.
• Now let us add data control to be used to connect database with the
following steps below:
• Create a database in Microsoft access and an interface in visual basic6.0
then try to access it from VB form interface.
• Name your database created in Microsoft access for example name it
“school”
• Create three tables named for example Table1, Table2 and Table. Fill your
database in Table1 with the fields: RegNo, First name, Last name, Gender,
Mother’s name, Father’s name, ID/Passport, Province, District, Sector, Option
and class respectively.
• Using visual basic 6.0, draw the interface for your tables with the following
controls:
• 12 labels (RegNo, First name, Last name, Gender, Mother’s name, Father’s
name, ID/Passport, Province, District, Sector, Option and class)
• 12text boxes RegNo, First name, Last name, Gender, Mother’s name, Father’s
name, ID/Passport, Province, District, Sector, Option and class)
• Eight command buttons for Add, Delete, Next, Previous, Top, Bottom,
Update, and Exit.
Below is VB form that contains all above command buttons. Connect it with your
created database and access all information from school database and do the same for your own basing on created database through SRS. Below are codes that will guide you.
a. Set properties for the text box
• Data field=name of the field (for this example it is Table2)
• Setting properties for the combo box
• Data source=data control name (for the example above, data control name
is Adodc1)
• Data field=name of the field (depending on the name of field in you want
to be displayed in database)
1. Codes for combo box (Gender)
Double click the form and write the following codes:
Private Sub Form_Load ()
Combo1.AddItem “Male”
Combo1.AddItem “Female”
End Sub
Note: You may edit the name combo1.AddItem to “cmbgender” or other name you
want.
1. Codes for command buttons
• Command “Add”
Private Sub cmdadd_Click()
Data1.Recordset.AddNew
End Sub
Command “Update”
Private Sub cmdupdate_Click()
Data1.Recordset.Update
End Sub
• Command “Bottom”
Private Sub cmdbottom_Click()
Data1.Recordset.MoveLast
End Sub
• Command “Top”
Private Sub cmdtop_Click()
Data1.Recordset.MoveFirst
End Sub
• Command “Exit”
Private Sub cmdexit_Click()
Unload Form1
End Sub
Private Sub cmdexit_Click()
Unload me
End Sub
or
Private Sub cmdexit_Click()
end
End Su
• Command “Next” (using Data1)
Private Sub cmdnext_Click()
Data1.Recordset.MoveNext
If Data1.Recordset.EOF Then
Data1.Recordset.MovePrevious
End If End Sub
• Command “Next” (using Adodc1)
Private Sub Command2_Click()
Adodc1.Recordset.MoveNext
If Adodc1.Recordset.EOF Then
Adodc1.Recordset.MovePrevious
MsgBox “you are at the end”
End If
End Sub
• Command “Previous” (using Data1)
Private Sub cmdprevious_Click()
Data1.Recordset.MovePrevious
If Data1.Recordset.BOF Then
Data1.Recordset.MoveNext
End If End Sub
• Command “Previous” (using Adodc1)
Private Sub Command3_Click()
Adodc1.Recordset.MovePrevious
If Adodc1.Recordset.BOF Then
Adodc1.Recordset.MoveNext
MsgBox “you are at the end”
End If
End Sub
• Command “Delete”
Private Sub cmddelete_Click()
If MsgBox(“Are you sure?”, vbQuestion + vbYesNo,
“Deleting”) = vbYes Then
Data1.Recordset.Delete
Data1.Recordset.MoveNext
End If
End Sub
• Command “Find”
Private Sub cmdfind_Click()
find = InputBox(“Enter parent_id”)
.With Data1.Recordset
.Index = “primarykey” .Seek “=”, find
End With
End Sub
6.7. PROJECT 7: PRINCIPLES FOR DESIGNING A FRIENDLY AND
ERGONOMIC USER INTERFACE
Designing a good user interface is an iterative process. There are appropriate
techniques to use during the designing and implementation of a user interface. For
good looking and being attractive, the user interface should be SMART so that every
designed screen should support single action of real value to the users. This should
make it easier to learn, use, add, delete and other interactions for users.
a. Principles of a friendly user interface.
The following table is showing the main principles of a friendly user interface.
Each programmer should keep it as a bible to consult during the development of
applications in different Programming Languages.
b. Principles for an ergonomic user interface
Ergonomics derives from two Greek words: ergon, meaning work, and nomoi, meaning natural
laws, to create a word that means the science of work and a person’s relationship to that work.
Ergonomics is a field of study that attempts to reduce strain, fatigue, and injuries by improving
product design and workspace arrangement. It makes things comfortable and efficient.
It is intended to maximize productivity by reducing operator fatigue and discomfort. Human
characteristics, such as height, weight, and proportions are considered, as well as information
about human hearing, sight, temperature preferences, and so on, in order to reduce or
eliminate factors that cause pain or discomfort
There are five aspects of ergonomics: safety, comfort, ease of use, productivity/performance,
and aesthetics. Based on these aspects of ergonomics, examples are given of how products
or systems could benefit from redesign based on ergonomic principles.
i. Safety: This has to do with the ability to use a device or work with a device
without short or long term damage to parts of the body.
ii. Comfort: Comfort in the human-machine interface is usually noticed first.
Physical comfort in how an item feels is pleasing to the user.
iii. Ease of use: This has to do with the ability to use a device with no stress
iv. Productivity/performance: Ergonomics addresses the performance of user
interface by providing more options to the users, enabling them to easily
and quickly skip some instructions as users’ choice.
v. Aesthetics: Signs in the workplace should be made consistent throughout
the workplace to not only be aesthetically pleasing, but also so that
information is easily accessible.
Project 6 activity
Improve your project by taking into consideration the principles of a friendly user
interface and an ergonomic user interface above.