Employees Record Management using VB 6.0 and MS Access Database

In this article, we will learn to create an application in Visual Basic 6.0 for Employee Record Management in an organization.

The application does following tasks

1. Insert, Delete, Update employee records.
2. Compute the Gross Pay and Net Pay of each employee.
3. Maintain records of departments for each employee.
4. Each employee should be able to login to the system with own password.

The development of the application – Employees Record Management is done in following ways.

• Develop System Model.
• Define the Data Model (E-R Diagram)
• Design the User Interface in VB 6.
• Rename the components.
• Write codes for components.
• Test and verify Output.

System Model

E-R Diagram for Employee Record Management

Working with MS Access Database

Based on the E-R Model you can create a New Access Database and save it as the emp.mdb file. To create a blank database

Open Microsoft Office Access 2007 ( Check MS office documentation for other versions) > Click “Blank Database”.

Create three table for the emp.mdb database.

Dependent Table Schema

Employee Table Schema

Login Table in Design View

Note: Each Table should have at least one field as Primary Key.

Relationship between Tables

Accessing Access Database from Visual Basic 6

You can use a common module from visual basic to connect to the access database. Functions from this module will be called from all Forms in the visual basic project, so that we do not need to rewrite the same code for database connectivity.

Add the ADODB components in the VB Toolbox.

Add an Adodb component, [Right Click] on the Control Object > Click Properties.

Under the Adodc properties windows > Click Build.

Under the Connection tab > Select the Database you created previously.

Now Click “Test Connection” to check if the database connection is successful. Click on the “Provider” tab again and we now have our connection string.

This new Connection String will be used in Module.Bas file for accessing the database at run-time. The existing ADODC control is no longer required and you can delete it.

Creating Module1 for Database Connectivity

To create a new database connection at run-time, do the following. Under Project.vbp windows > Project1 [Right-Click] > Add [Select] > Module [Click].

A new module will open as shown in the following figure.

Type the following code in the Module Code Editor.

Public con As New adodb.Connection 
Public rs As New adodb.Recordset 
Public constr As String 

Public Sub loadcon() 
    constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Peter\Documents\emp.mdb;Persist Security Info=False" 
  
    con.Open constr 
    MsgBox ("connected") 

End Sub

Labels

Name: lblEMPMANAGE
Caption: EMPLOYEE RECORD MANAGEMENT

Buttons

Name: cmdLogin
Caption: Login

Code for frmEMPMANAGE

Private Sub cmdLogin_Click() 

    Unload Me 
    frmLogin.Show 

End Sub

Components of frmLogin

Labels

Name: lblUserID
Caption:UserID

Name: lblPassword
Caption: Password

Textboxes

Name: txtUserID
Text: 'leave blank'

Name: txtPassword
Text; 'leave blank'

Buttons

Name; vbOK
Caption: Login

Name: vbCancel
Caption: Cancel

Code for frmLogin

Option Explicit 
Public LoginSucceeded As Boolean 

Private Sub cmdCancel_Click() 
    'set the global var to false 
    'to denote a failed login 
    LoginSucceeded = False 
    Me.Hide 
End Sub 

Private Sub cmdOK_Click() 
    With 
        rs .Open "SELECT * FROM LOGIN", con, adOpenDynamic, adLockOptimistic 

        'check the username 

        .MoveFirst While Not .EOF 

    If txtUserID = !UserID And txtPassword = !Password Then 
        frmMenu.Show 
        LoginSucceeded = True 
        con.Close 
        MsgBox ("Connection Closed") 
        Exit Sub 
        Unload Me 
    ElseIf 
        txtUserID <> !UserID Then 
        .MoveNext 
    Else 
        MsgBox "Invalid Password, try again!", , "Login" 
        txtPassword.SetFocus SendKeys "{Home}+{End}" 
    Exit Sub 
    End If 
    Wend 
    .Close 
    End With 

End Sub 

Private Sub Form_Load() 
    Call loadcon 
    Unload frmEMPMANAGE 
End Sub

Components for frmEmpDetails

Labels

Name: lblEmpID
Caption: EMPID

Name: lblFirstname
Caption: FirstName

Name: lblLastname
Caption: LastName

Name: lblHRA
Caption: HRA

Name: lblDA
Caption: DA

Name: lblBasic
Caption: Basic

TextBoxes

Name: txtEmpID
Text: 'leave blank'

Name: txtFirstname
Text: 'leave blank'

Name: txtLastname
Text: 'leave blank'

Name: txtHRA
Text: 'leave blank'

Name: txtDA
Text: 'leave blank'

Name: txtBasic
Text: 'leave blank'

Buttons

Name:  cmdAdd
Caption: &ADD

Name: cmdDel
Caption: &DELETE

Name: cmdUpdate
Caption: &Update

Name: cmdClr
Caption: &Clear

Name: cmdExit
Caption: &Exit

Name: cmdFirstname
Caption: &FirstName

Name: cmdLastname
Capttion: &LastName

Name: cmdNext
Caption: Next Record

Name: cmdPrev
Caption: Previous Record

Code for frmEmpDetails

Private Sub cmdAdd_Click() 
    con.Execute "INSERT INTO emp(EmpID,FirstName, 
    LastName,Basic, HRA, DA) 
    VALUES (" &   txtEmpID & ",'" & 
    txtFirstname & "','" & 
    txtLastname & "'," & 
    txtBasic & "," & 
    txtHRA & "," & 
    txtDA & " );" 

    MsgBox ("Record Inserted") 
End Sub 

Private Sub cmdCLR_Click() 

    txtEmpID = "" 
    txtFirstname = "" 
    txtLastname = ""
    txtBasic = "" 
    txtHRA = "" 
    txtDA = "" 

End Sub 

Private Sub cmdDel_Click() 

    con.Execute "DELETE * FROM emp 
    WHERE EmpID = " & 
    txtEmpID & "" 

    MsgBox ("Record Deleted") 
    txtEmpID = "" 

End Sub 

Private Sub cmdExit_Click() 
    Unload Me 
    con.Close 
End Sub 

Private Sub cmdUpdate_Click() 
    con.Execute "UPDATE emp SET FirstName = '" & 
    txtFirstname & "', LastName = ' " & 
    txtLastname & " ' , Basic = " & 
    txtBasic & " , HRA = " & 
    txtHRA & " , DA = " & 
    txtDA & " WHERE EmpID = " & 
    txtEmpID.Text & "" 
End Sub 

Private Sub Form_Load() 
    Call loadcon 
End Sub

Component for frmSalary

Labels

Name: lblCalculateS
Option: CALCULATE SALARY

Name: lblEmpID
Caption: ENTER EMPLOYEE ID

Name: lblGrossP
Caption: GROSS PAY

Name: lblNetP
Caption: NET PAY

Textboxes

Name: txtEmpID
Text: 'leave blank'

Name: txtGross
Text: 'leave blank'

Name: txtNet
Text: 'leave blank'

Buttons

Name : cmdGross
Caption: GROSS PAY

Name: cmdNet
Caption: NET PAY

Name: cmdExit
Caption: EXIT

Code for frmSalary

Private Sub cmdExit_Click() 
    Unload Me 
    con.Close 
End Sub 

Private Sub cmdGross_Click() 
Dim gross As Double 
Dim net As Double 
Dim tax As Double 
    With 
        rs rs.Open "SELECT * FROM emp WHERE EmpID = " & 
        txtEmpID & "", con, adOpenDynamic, adLockPessimistic 
        gross = !Basic + !HRA + !DA 
    End With 
        txtGross.Text = gross 

    con.Execute "UPDATE emp SET GrossPay =  ' " & 
    gross & " ' WHERE EmpID = " & 
    txtEmpID & "" 

    rs.Close 
End Sub 

Private Sub cmdNet_Click() 
Dim gross As Double 
Dim net As Double 
Dim tax As Double 
    With rs 
        rs.Open "SELECT * FROM emp 
        WHERE EmpID = " & 
        txtEmpID & "", 
        con, adOpenDynamic, adLockPessimistic 

        gross = !Basic + !HRA + !DA 
        tax = gross * 10 / 100 
        net = gross - tax 
    End With 
    rs.Close 
    txtNet.Text = net 
    con.Execute "UPDATE emp 
    SET NetPay =  ' " & net & " ' 
    WHERE EmpID = " & txtEmpID & "" 

End Sub 

Private Sub Form_Load() 
    Call loadcon 
End Sub

Components for frmDependent

Labels

Name: lblDID
Caption: Department ID

Name: lblDname
Caption: Department Name

Name: lblRelation
Caption: Relation

Name: lblDependent
Caption: Employee Dependent

Textboxes

Name: txtDID
Text: 'leave blank'

Name: txtDname
Text: 'leave blank'

Name: txtRel
Text: 'leave blank'

Name: txtEmpID
Text: 'leave blank'

Buttons

Name: cmdAdd
Caption: &ADD

Name: cmdDel
Caption: &DELETE

Name: cmdUpdate
Caption: &UPDATE

Name: cmdClr
Caption: &CLEAR

Caption: &EXIT
Name: cmdExit

Code for frmDependent

Private Sub cmdAdd_Click() 
    con.Execute "INSERT INTO 
    Dependent(DependentID, DependentName,Relation,EmpID ) 
    VALUES (" & txtDID & ", '" & 
    txtDName & "','" & 
    txtRel & "'," & 
    txtEmpID & ");" 

    txtDID.Text = "" 
    txtDName.Text = "" 
    txtRel.Text = "" 
    txtEmpID = "" 

    con.Execute "DELETE * FROM Dependent 
    WHERE DependentID = " & txtDID & "" 
End Sub 

Private Sub cmdClr_Click() 
    txtDID.Text = "" 
    txtDName.Text = "" 
    txtRel.Text = "" 
    txtEmpID = "" 
End Sub 

Private Sub cmdExit_Click() 
    Unload Me 
End Sub 

Private Sub cmdUpdate_Click() 

    con.Execute "UPDATE Dependent 
    SET  DependentName = ' " & 
    txtDName & " ' , Relation = ' " & 
    txtRel & " ' WHERE DependentID = " & 
    txtDID & " " 
    MsgBox ("Record Updated") 

End Sub 

Private Sub Form_Load() 
    Call loadcon 
End Sub

frmMenu [MDI Form]

MDI Form are Multiple-Document Interface in which you can open multiple forms. All our previously created will be child form of MDI.

This form is called frmMENU. 

This form has menu editor to add different menu items and each menu item is linked to one of the forms created earlier. Before we get started with MDI form, you must set the MDI Child property of following Forms to ‘True’.

• frmEmpDetails
• frmSalary
• frmDependent

Create  a New MDI Form

To create a new MDI From, go to Project1.vbp > [Right Click] and then [Select ] Add > [Click] MDI Form. 

Right Click the form and you will see the Menu Editor, using the editor create following menu items.

Caption : Employee Details
Name : employee
Index : 0

Caption : Salary Calculation
Name : salary
Index : 1

Caption : Dependents
Name : dependents
Index : 2

Caption :  Exit
Name : exit
Index : 3

Code for frmMENU

The code for frmMENU is simple, you click on one of the menu item and a new form opens up, however the new form does not open in a separate windows, but it will open within frmMENU as MDI Child form.

Private Sub depend_Click(Index As Integer) 
    frmDependent.Show 
End Sub 

Private Sub empdetails_Click(Index As Integer) 
    frmEmpDetails.Show 
End Sub 

Private Sub exit_Click(Index As Integer) 
    Unload Me 
    Unload frmLogin 
End Sub 

Private Sub salary_Click(Index As Integer) 
    frmSalary.Show 
End Sub