Payroll Management System | Visual Basic 6.

0 & SQL Server Page

Abstract

The Payroll Management System (PMS) is a comprehensive desktop application developed using
Microsoft Visual Basic 6.0 as the front-end and Microsoft SQL Server as the back-end database. The
system is designed to automate and streamline all payroll-related activities within an organization,
replacing cumbersome manual processes with a reliable, accurate, and efficient computerized
solution.
The system provides a menu-driven interface that allows authorized users to manage employee
records, compute salaries, calculate deductions (PF, ESI, Income Tax, Professional Tax), generate
payslips, and produce various financial reports. The application handles multiple employee
categories, departments, and pay scales, ensuring flexibility across diverse organizational structures.
Key modules include: Employee Master, Salary Structure, Attendance Management, Payroll
Processing, Leave Management, Report Generation, and User Administration. Each module is
interconnected through a relational SQL Server database ensuring data integrity and consistency.
The system significantly reduces human error, saves administrative time, and provides management
with accurate, real-time financial data for decision-making. Security is enforced through role-based
access control, ensuring that sensitive payroll data is accessible only to authorized personnel.
Payroll Management System | Visual Basic 6.0 & SQL Server Page

Table of Contents

1. Introduction................................................................................................................. 5
1.1 Project Overview........................................................................................................ 5
1.2 Problem Statement.................................................................................................... 5
1.3 Objectives.................................................................................................................. 6
1.4 Scope of the Project...................................................................................................6
2. System Analysis..........................................................................................................7
2.1 Existing System.........................................................................................................7
2.2 Proposed System.......................................................................................................7
2.3 Feasibility Study......................................................................................................... 8
3. System Design............................................................................................................ 9
3.1 System Architecture................................................................................................... 9
3.2 DFD — Level 0 & Level 1.......................................................................................... 9
3.3 ER Diagram Description.......................................................................................... 10
4. Database Design (SQL Server)................................................................................11
4.1 Database Tables...................................................................................................... 11
4.2 SQL Table Creation Scripts.....................................................................................12
4.3 Stored Procedures...................................................................................................13
5. Visual Basic — System Modules..............................................................................14
5.1 Menu-Driven Main Form..........................................................................................14
5.2 Login Module...........................................................................................................15
5.3 Employee Master Module........................................................................................16
5.4 Salary Structure Module.......................................................................................... 17
5.5 Payroll Processing Module...................................................................................... 18
5.6 Leave Management Module.................................................................................... 19
5.7 Report Generation Module.......................................................................................20
6. Testing...................................................................................................................... 21
7. System Requirements...............................................................................................22
8. Advantages & Limitations......................................................................................... 23
9. Conclusion & Future Scope......................................................................................24
10. Bibliography............................................................................................................. 25
Payroll Management System | Visual Basic 6.0 & SQL Server Page

Chapter 1: Introduction

1.1 Project Overview

Payroll management is one of the most critical functions within any organization. It involves
computing employee compensation, deductions, taxes, and disbursement of salaries in a timely and
accurate manner. Errors in payroll can lead to employee dissatisfaction, legal complications, and
financial discrepancies.
This project presents a Payroll Management System (PMS) built with Visual Basic 6.0 and SQL
Server. It is a menu-driven desktop application that automates the entire payroll cycle — from
employee onboarding to salary disbursement and report generation.

1.2 Problem Statement

Many small and mid-sized organizations still depend on manual payroll processing using registers,
Excel sheets, or paper-based methods. This leads to several problems:
• Frequent calculation errors in salary, allowances, and deductions
• Time-consuming manual preparation of payslips for hundreds of employees
• Lack of audit trails and version control over payroll data
• Difficulty in maintaining and retrieving historical payroll records
• No centralized system for cross-department payroll visibility
• Compliance risks due to incorrect PF, ESI, and TDS computations
A computerized Payroll Management System addresses all the above challenges.

1.3 Objectives
The primary objectives of the Payroll Management System are:
• To automate salary computation including all allowances and statutory deductions
• To maintain a centralized, secure database of all employee information
• To generate accurate payslips and payroll reports automatically
• To enforce role-based access for data security
• To provide attendance and leave integration with payroll processing
• To store historical payroll data for audit and compliance purposes
• To reduce processing time from days to minutes

1.4 Scope of the Project

The scope of this project encompasses the following functionalities:
• Employee master data management (personal, professional, and banking details)
• Definition and management of flexible salary structures by designation
• Attendance tracking and integration with payroll
• Automated payroll computation for a selected month and department
• Statutory deduction calculation (PF, ESI, Professional Tax, Income Tax/TDS)
• Payslip generation for individual employees
• Summary and detailed payroll reports for management
• Leave management with leave balance computation
Payroll Management System | Visual Basic 6.0 & SQL Server Page

• User login and role-based access control (Admin, HR, Accounts)

Note: The system does not cover recruitment, performance appraisals, or bank integration in this
version. These can be added as future enhancements.
Payroll Management System | Visual Basic 6.0 & SQL Server Page

Chapter 2: System Analysis

2.1 Existing System

The existing payroll system in most traditional organizations relies on manual processes:
• HR staff manually collect attendance registers at month-end
• Salary sheets are prepared in Excel with formulas prone to human error
• Payslips are printed and distributed manually
• Ledger books or Excel files are used to maintain payroll history
• PF and ESI challans are computed manually

Drawbacks of the Existing System

• Highly time-consuming and error-prone
• No real-time access to payroll data
• Data redundancy and inconsistency across departments
• Difficult to generate consolidated reports
• Lack of data security and unauthorized access risks
• No systematic audit trail

2.2 Proposed System

The proposed Payroll Management System is a fully computerized, menu-driven desktop application
offering the following advantages:
• Centralized SQL Server database ensuring data consistency and integrity
• Automated salary calculation with configurable salary structures
• Instant generation of payslips and reports
• Role-based user access (Admin, HR Manager, Accounts Officer)
• Attendance and leave integration for accurate LOP (Loss of Pay) computation
• Complete audit trail with timestamped records
• Backup and restore functionality for data safety

2.3 Feasibility Study

Technical Feasibility
The project uses Visual Basic 6.0 — a widely used RAD (Rapid Application Development) tool — and
SQL Server, which is a robust RDBMS available in most organizations. The technology stack is
stable, well-documented, and the development team has the required skills.

Economic Feasibility
The cost of development is limited to developer effort and software licensing (VB6 is legacy and often
already available in academic environments). The savings in HR processing time, error correction,
and compliance penalties make this project economically viable.

Operational Feasibility
The system features a simple, intuitive menu-driven interface that can be operated with minimal
training. The GUI is designed keeping non-technical HR staff in mind, with on-screen validation
messages and help tooltips.
Payroll Management System | Visual Basic 6.0 & SQL Server Page

Feasibility Area Status Justification

Technical FEASIBLE VB6 + SQL Server — widely available

Economic FEASIBLE Low development cost, high ROI

Operational FEASIBLE Simple menu-driven GUI

Legal FEASIBLE Complies with statutory deduction norms

Schedule FEASIBLE 4-month development timeline

Payroll Management System | Visual Basic 6.0 & SQL Server Page

Chapter 3: System Design

3.1 System Architecture

The Payroll Management System follows a Two-Tier Client-Server architecture:
• Tier 1 (Client / Presentation Layer): Visual Basic 6.0 forms running on the client machine
• Tier 2 (Database / Data Layer): Microsoft SQL Server running on the server machine
Communication between the tiers happens via ADO (ActiveX Data Objects), which connects VB
forms to SQL Server using a DSN-less connection string.

Architecture Description
Layer Technology Responsibility
Presentation Layer Visual Basic 6.0 Forms, menus, data entry, reports

Business Logic Layer VB6 Modules + SQL Stored Salary calculation, validation, rules
Procedures

Data Access Layer ADO 2.8 Connection management, queries

Database Layer SQL Server 2000/2005 Data storage, retrieval, security

3.2 Data Flow Diagrams (DFD)

Level 0 DFD — Context Diagram
The context diagram shows the Payroll Management System as a single process interacting with
external entities:
• HR Manager → inputs employee data, attendance, and leave records
• Accounts Officer → runs payroll and views financial reports
• Employee → receives payslip output from the system
• Management → receives consolidated payroll reports
• Government Bodies → receive statutory deduction data (PF, ESI, TDS)

Level 1 DFD — Major Processes

Process Process Name Inputs Outputs
No.
P1 Employee Employee details Employee records stored
Management

P2 Attendance Daily attendance Monthly attendance summary

Management

P3 Leave Management Leave applications Leave balance updates

P4 Salary Structure Setup Pay components Designation-wise salary slabs

P5 Payroll Processing Attendance + Structure Payroll register, payslips

P6 Deduction Computation Gross salary, rules PF, ESI, TDS, PT values

Payroll Management System | Visual Basic 6.0 & SQL Server Page

Process Process Name Inputs Outputs

No.
P7 Report Generation Payroll data Various formatted reports

P8 User Management User credentials Role-based access control

3.3 Entity Relationship (ER) Diagram Description

The ER diagram for the Payroll Management System consists of the following entities and
relationships:

Key Entities
Entity Attributes (Key) Relationship
EMPLOYEE EmpID (PK), Name, DeptID (FK), Works in Department, Has
DesigID (FK) Designation
DEPARTMENT DeptID (PK), DeptName Has many Employees

DESIGNATION DesigID (PK), Title, GradeLevel Determines Salary Structure

SALARY_STRUCTUR StructID (PK), DesigID (FK), Defines components for Designation

E Component, Amount

ATTENDANCE AttID (PK), EmpID (FK), Month, Linked to Employee

Year, PresentDays

LEAVE LeaveID (PK), EmpID (FK), Linked to Employee

LeaveType, Days, Status

PAYROLL PayID (PK), EmpID (FK), Month, Generated per Employee/Month

Year, GrossSalary, NetSalary

USERS UserID (PK), Username, Password, Controls system access

Role
Payroll Management System | Visual Basic 6.0 & SQL Server Page

Chapter 4: Database Design (SQL Server)

4.1 Database Tables Description

Table 1: tbl_Department
Column Name Data Type Constraint Description
DeptID INT PRIMARY KEY, Department unique ID (auto)
IDENTITY

DeptCode VARCHAR(10 NOT NULL, UNIQUE Short code e.g. IT, HR, FIN
)

DeptName VARCHAR(10 NOT NULL Full department name

0)
DeptHead VARCHAR(10 NULL Name of department head
0)

CreatedDate DATETIME DEFAULT GETDATE() Record creation timestamp

Table 2: tbl_Designation
Column Name Data Type Constraint Description
DesigID INT PRIMARY KEY, Designation unique ID
IDENTITY

DesigCode VARCHAR(10 NOT NULL, UNIQUE Short code e.g. MGR, DEV
)

DesigTitle VARCHAR(10 NOT NULL Designation title

0)

GradeLevel VARCHAR(10 NULL Pay grade e.g. L1, L2

)

BasicPay DECIMAL(12, NOT NULL Basic pay for this designation

2)

Table 3: tbl_Employee
Column Name Data Type Constraint Description
EmpID INT PRIMARY KEY, Employee unique ID
IDENTITY

EmpCode VARCHAR(20 NOT NULL, UNIQUE Employee code e.g. EMP001

)

EmpName VARCHAR(15 NOT NULL Full name

0)

FatherName VARCHAR(15 NULL Father's name

Payroll Management System | Visual Basic 6.0 & SQL Server Page

Column Name Data Type Constraint Description

0)

DOB DATE NOT NULL Date of birth

DOJ DATE NOT NULL Date of joining

Gender CHAR(1) CHECK (M/F/O) Gender

DeptID INT FK → tbl_Department Department reference

DesigID INT FK → tbl_Designation Designation reference

BankAcNo VARCHAR(20 NULL Bank account number

)

PAN VARCHAR(15 NULL PAN card number

)

PFNo VARCHAR(20 NULL PF account number

)

ESINo VARCHAR(20 NULL ESI card number

)

IsActive BIT DEFAULT 1 1=Active, 0=Inactive

Table 4: tbl_SalaryStructure
Column Name Data Type Constraint Description
StructID INT PRIMARY KEY, Structure record ID
IDENTITY

DesigID INT FK → tbl_Designation Designation reference

Component VARCHAR(50 NOT NULL e.g. Basic, HRA, DA, TA

)

CompType CHAR(1) CHECK (E/D) E=Earning, D=Deduction

CalcType CHAR(1) CHECK (F/P) F=Fixed, P=Percentage of Basic

Amount DECIMAL(12, NOT NULL Fixed amount or percentage

2) value

Table 5: tbl_Payroll
Column Name Data Type Constraint Description
PayID INT PRIMARY KEY, IDENTITY Payroll record ID

EmpID INT FK → tbl_Employee Employee reference

PayMonth INT CHECK (1-12) Month of payroll

PayYear INT NOT NULL Year of payroll

BasicSalary DECIMAL(12, NOT NULL Basic salary amount

Payroll Management System | Visual Basic 6.0 & SQL Server Page

Column Name Data Type Constraint Description

2)

HRA DECIMAL(12, DEFAULT 0 House rent allowance

2)

DA DECIMAL(12, DEFAULT 0 Dearness allowance

2)

TA DECIMAL(12, DEFAULT 0 Travel allowance

2)

OtherAllowance DECIMAL(12, DEFAULT 0 Any other earning

2)

GrossSalary DECIMAL(12, NOT NULL Sum of all earnings

2)

PF_Deduction DECIMAL(12, DEFAULT 0 PF @ 12% of Basic

2)
ESI_Deduction DECIMAL(12, DEFAULT 0 ESI @ 0.75% of Gross
2)

PT_Deduction DECIMAL(12, DEFAULT 0 Professional tax

2)

TDS_Deduction DECIMAL(12, DEFAULT 0 Income tax deducted

2)

LOP_Days INT DEFAULT 0 Loss of pay days

LOP_Amount DECIMAL(12, DEFAULT 0 LOP deduction amount

2)

TotalDeduction DECIMAL(12, NOT NULL Sum of all deductions

2)

NetSalary DECIMAL(12, NOT NULL Gross - Total

2) Deductions

ProcessedDate DATETIME DEFAULT GETDATE() When payroll was

processed

4.2 SQL Table Creation Scripts

Database and Table Creation
-- Create Database
CREATE DATABASE PayrollDB;
GO
USE PayrollDB;
GO

-- Table: tbl_Department
CREATE TABLE tbl_Department (
DeptID INT IDENTITY(1,1) PRIMARY KEY,
DeptCode VARCHAR(10) NOT NULL UNIQUE,
DeptName VARCHAR(100) NOT NULL,
DeptHead VARCHAR(100) NULL,
CreatedDate DATETIME DEFAULT GETDATE()
Payroll Management System | Visual Basic 6.0 & SQL Server Page
);

-- Table: tbl_Designation
CREATE TABLE tbl_Designation (
DesigID INT IDENTITY(1,1) PRIMARY KEY,
DesigCode VARCHAR(10) NOT NULL UNIQUE,
DesigTitle VARCHAR(100) NOT NULL,
GradeLevel VARCHAR(10) NULL,
BasicPay DECIMAL(12,2) NOT NULL
);

-- Table: tbl_Employee
CREATE TABLE tbl_Employee (
EmpID INT IDENTITY(1,1) PRIMARY KEY,
EmpCode VARCHAR(20) NOT NULL UNIQUE,
EmpName VARCHAR(150) NOT NULL,
FatherName VARCHAR(150) NULL,
DOB DATE NOT NULL,
DOJ DATE NOT NULL,
Gender CHAR(1) CHECK (Gender IN ('M','F','O')),
DeptID INT FOREIGN KEY REFERENCES tbl_Department(DeptID),
DesigID INT FOREIGN KEY REFERENCES tbl_Designation(DesigID),
BankAcNo VARCHAR(20) NULL,
PAN VARCHAR(15) NULL,
PFNo VARCHAR(20) NULL,
ESINo VARCHAR(20) NULL,
IsActive BIT DEFAULT 1
);

4.3 Stored Procedures

SP: Calculate and Insert Payroll
CREATE PROCEDURE sp_ProcessPayroll
@EmpID INT,
@PayMonth INT,
@PayYear INT,
@LOPDays INT
AS BEGIN
DECLARE @Basic DECIMAL(12,2), @GrossSalary DECIMAL(12,2)
DECLARE @HRA DECIMAL(12,2), @DA DECIMAL(12,2), @TA DECIMAL(12,2)
DECLARE @PF DECIMAL(12,2), @ESI DECIMAL(12,2), @PT DECIMAL(12,2)
DECLARE @TDS DECIMAL(12,2), @LOPAmt DECIMAL(12,2)
DECLARE @TotalDed DECIMAL(12,2), @NetSalary DECIMAL(12,2)
DECLARE @WorkingDays INT = 26

-- Get basic pay from designation

SELECT @Basic = [Link]
FROM tbl_Employee e
JOIN tbl_Designation d ON [Link] = [Link]
WHERE [Link] = @EmpID

-- Standard allowances
SET @HRA = @Basic * 0.40 -- 40% of Basic
SET @DA = @Basic * 0.10 -- 10% of Basic
SET @TA = 1600.00 -- Fixed TA
SET @GrossSalary = @Basic + @HRA + @DA + @TA

-- LOP Deduction
SET @LOPAmt = (@GrossSalary / @WorkingDays) * @LOPDays

-- Statutory Deductions
SET @PF = @Basic * 0.12 -- PF: 12% of Basic
SET @ESI = CASE WHEN @GrossSalary <= 21000
THEN @GrossSalary * 0.0075 ELSE 0 END
SET @PT = CASE
Payroll Management System | Visual Basic 6.0 & SQL Server Page
WHEN @GrossSalary > 15000 THEN 200
WHEN @GrossSalary > 10000 THEN 150
ELSE 0 END
SET @TDS = 0 -- Simplified; compute annually

SET @TotalDed = @PF + @ESI + @PT + @TDS + @LOPAmt

SET @NetSalary = @GrossSalary - @TotalDed

-- Insert payroll record

INSERT INTO tbl_Payroll
(EmpID, PayMonth, PayYear, BasicSalary, HRA, DA, TA, GrossSalary,
PF_Deduction, ESI_Deduction, PT_Deduction, TDS_Deduction,
LOP_Days, LOP_Amount, TotalDeduction, NetSalary)
VALUES
(@EmpID, @PayMonth, @PayYear, @Basic, @HRA, @DA, @TA, @GrossSalary,
@PF, @ESI, @PT, @TDS, @LOPDays, @LOPAmt, @TotalDed, @NetSalary)
END
GO
Payroll Management System | Visual Basic 6.0 & SQL Server Page

Chapter 5: Visual Basic — System Modules

5.1 Menu-Driven Main Form (MDI Form)

The main window is an MDI (Multiple Document Interface) form with a structured menu bar. The
menu structure is:
Menu Item Sub-Menu Items
File Login, Change Password, Logout, Exit

Masters Department Master, Designation Master, Employee Master, Salary Structure

Attendance Mark Attendance, View Attendance, Attendance Report

Leave Apply Leave, Approve Leave, Leave Balance

Payroll Process Payroll, View Payroll, Payslip, Payroll Register

Reports Employee List, Department-wise Report, Monthly Summary, Annual Report

Admin User Management, Backup Database, Restore Database

Help About System, User Manual

MDI Form Code (VB6)

' Module: [Link] — Global Variables
Public g_UserID As Integer
Public g_UserRole As String
Public g_UserName As String
Public g_ConnStr As String
Public g_Conn As [Link]

Public Sub OpenConnection()

Set g_Conn = New [Link]
g_ConnStr = "Provider=SQLOLEDB;Data Source=.;" & _
"Initial Catalog=PayrollDB;" & _
"Integrated Security=SSPI;"
g_Conn.Open g_ConnStr
End Sub

Public Sub CloseConnection()

If g_Conn.State = adStateOpen Then g_Conn.Close
Set g_Conn = Nothing
End Sub

Public Function ExecuteQuery(sql As String) As [Link]

Dim rs As New [Link]
[Link] sql, g_Conn, adOpenStatic, adLockReadOnly
Set ExecuteQuery = rs
End Function

' MDI Main Form — Menu Click Handlers

Private Sub mnuEmployee_Click()
[Link]
End Sub

Private Sub mnuPayroll_Click()

[Link]
End Sub
Payroll Management System | Visual Basic 6.0 & SQL Server Page

Private Sub mnuExit_Click()

If MsgBox("Exit Application?", vbYesNo, "Confirm Exit") = vbYes Then
Call CloseConnection
End
End If
End Sub

5.2 Login Module

The login form is the entry point of the application. It validates credentials against the tbl_Users table
and assigns user roles (Admin, HR, Accounts).
' frmLogin — Login Form
Private Sub cmdLogin_Click()
Dim rs As [Link]
Dim sSQL As String
Dim sPwd As String

If Trim([Link]) = "" Then

MsgBox "Please enter username!", vbExclamation
[Link] : Exit Sub
End If
If Trim([Link]) = "" Then
MsgBox "Please enter password!", vbExclamation
[Link] : Exit Sub
End If

' Hash password (MD5 or simple encryption)

sPwd = HashPassword([Link])

sSQL = "SELECT UserID, UserName, Role FROM tbl_Users " & _

"WHERE UserName='" & Trim([Link]) & "' " & _
" AND Password='" & sPwd & "' AND IsActive=1"

Set rs = ExecuteQuery(sSQL)

If [Link] Then
MsgBox "Invalid username or password!", vbCritical
[Link] = ""
[Link]
Else
g_UserID = rs("UserID")
g_UserName = rs("UserName")
g_UserRole = rs("Role")
[Link]
[Link]
End If
[Link]
End Sub

5.3 Employee Master Module

The Employee Master form allows HR to add, edit, view, and deactivate employee records. It uses a
DataGrid for display and individual TextBoxes for data entry.

Form Controls
Control Name Purpose
TextBox txtEmpCode Employee code input

TextBox txtEmpName Employee full name

ComboBox cboDept Department selection

Payroll Management System | Visual Basic 6.0 & SQL Server Page

Control Name Purpose

ComboBox cboDesig Designation selection

DateTimePicker dtpDOJ Date of Joining

TextBox txtPAN PAN number

TextBox txtBankAcNo Bank account number

DataGrid dgEmployee Display employee list

CommandButton cmdSave Save record

CommandButton cmdUpdate Update record

CommandButton cmdDelete Deactivate employee

CommandButton cmdClear Clear form

' frmEmployee — Save New Employee

Private Sub cmdSave_Click()
If Not ValidateForm() Then Exit Sub

Dim sSQL As String

sSQL = "INSERT INTO tbl_Employee" & _
"(EmpCode,EmpName,FatherName,DOB,DOJ,Gender," & _
" DeptID,DesigID,BankAcNo,PAN,IsActive) VALUES (" & _
"'" & [Link] & "','" & [Link] & "'," & _
"'" & [Link] & "','" & [Link] & "'," & _
"'" & [Link] & "','" & optGender() & "'," & _
[Link]([Link]) & "," & _
[Link]([Link]) & "," & _
"'" & [Link] & "','" & [Link] & "',1)"

g_Conn.Execute sSQL
MsgBox "Employee saved successfully!", vbInformation
Call LoadGrid
Call ClearForm
End Sub

Private Function ValidateForm() As Boolean

ValidateForm = True
If Trim([Link]) = "" Then
MsgBox "Employee Code is mandatory!", vbExclamation
ValidateForm = False : Exit Function
End If
If [Link] = -1 Then
MsgBox "Please select Department!", vbExclamation
ValidateForm = False : Exit Function
End If
End Function

5.4 Salary Structure Module

The Salary Structure module allows Admin/HR to define earning and deduction components for each
designation. Components can be fixed amounts or percentages of basic pay.
' frmSalaryStructure — Load structure for selected designation
Private Sub cboDesig_Click()
Dim rs As [Link]
Dim sql As String
sql = "SELECT * FROM tbl_SalaryStructure" & _
" WHERE DesigID=" & _
Payroll Management System | Visual Basic 6.0 & SQL Server Page
[Link]([Link])
Set rs = ExecuteQuery(sql)
Set [Link] = rs
End Sub

Private Sub cmdAddComponent_Click()

If [Link] = -1 Or [Link] = "" Then
MsgBox "Fill all fields!", vbExclamation : Exit Sub
End If
Dim sql As String
sql = "INSERT INTO tbl_SalaryStructure" & _
"(DesigID,Component,CompType,CalcType,Amount) VALUES (" & _
[Link]([Link]) & "," & _
"'" & [Link] & "','" & _
optType() & "','" & optCalc() & "'," & _
CDbl([Link]) & ")"
g_Conn.Execute sql
MsgBox "Component added!", vbInformation
cboDesig_Click
End Sub

5.5 Payroll Processing Module

This is the core module of the system. The Accounts Officer selects a month, year, and department,
then clicks 'Process Payroll'. The system calls the stored procedure for each employee.
' frmPayroll — Process Payroll
Private Sub cmdProcess_Click()
Dim rs As [Link]
Dim sqlEmp As String
Dim nMonth As Integer
Dim nYear As Integer
Dim nDeptID As Integer

nMonth = CInt([Link]([Link]))
nYear = CInt([Link])
nDeptID = CInt([Link]([Link]))

' Confirm before processing

If MsgBox("Process payroll for " & [Link] & _
" " & nYear & "?", vbYesNo) = vbNo Then Exit Sub

' Delete existing payroll for month/year/dept (re-process)

Dim sqlDel As String
sqlDel = "DELETE FROM tbl_Payroll WHERE PayMonth=" & nMonth & _
" AND PayYear=" & nYear & _
" AND EmpID IN (SELECT EmpID FROM tbl_Employee" & _
" WHERE DeptID=" & nDeptID & ")"
g_Conn.Execute sqlDel

' Loop through all active employees in selected dept

sqlEmp = "SELECT EmpID FROM tbl_Employee" & _
" WHERE DeptID=" & nDeptID & " AND IsActive=1"
Set rs = ExecuteQuery(sqlEmp)

Dim nCount As Integer : nCount = 0

Do While Not [Link]
Dim nEmpID As Integer
Dim nLOP As Integer
nEmpID = rs("EmpID")

' Get LOP days from attendance

Dim rsAtt As [Link]
Dim sqlA As String
sqlA = "SELECT 26 - ISNULL(PresentDays,0) AS LOPDays" & _
" FROM tbl_Attendance WHERE EmpID=" & nEmpID & _
Payroll Management System | Visual Basic 6.0 & SQL Server Page
" AND AttMonth=" & nMonth & " AND AttYear=" & nYear
Set rsAtt = ExecuteQuery(sqlA)
If [Link] Then
nLOP = 0
Else
nLOP = rsAtt("LOPDays")
End If
[Link]

' Call stored procedure

Dim cmd As New [Link]
[Link] = g_Conn
[Link] = "sp_ProcessPayroll"
[Link] = adCmdStoredProc
[Link]
[Link]("@EmpID",adInteger,adParamInput,,nEmpID)
[Link]
[Link]("@PayMonth",adInteger,adParamInput,,nMonth)
[Link]
[Link]("@PayYear",adInteger,adParamInput,,nYear)
[Link]
[Link]("@LOPDays",adInteger,adParamInput,,nLOP)
[Link]
nCount = nCount + 1
[Link]
Loop
[Link]
MsgBox "Payroll processed for " & nCount & " employees!", vbInformation
Call LoadPayrollGrid
End Sub

5.6 Leave Management Module

The Leave Management module handles leave applications, approvals, and balance tracking. It
integrates with payroll to compute LOP deductions.
Leave Type Code Days Allowed LOP Impact
Casual Leave CL 12 days/year No

Sick Leave SL 12 days/year No

Earned Leave EL Accrued @ 1/11 of No

days worked

Loss of Pay LOP Unlimited (unapproved) Yes — full deduction

Maternity Leave ML 180 days (female only) No (statutory)

' frmLeave — Apply Leave

Private Sub cmdApply_Click()
' Validate dates
If [Link] > [Link] Then
MsgBox "From date must be before To date!", vbExclamation
Exit Sub
End If

Dim nDays As Integer

nDays = DateDiff("d", [Link], [Link]) + 1

' Check leave balance

Dim rs As [Link]
Dim sql As String
sql = "SELECT Balance FROM tbl_LeaveBalance" & _
Payroll Management System | Visual Basic 6.0 & SQL Server Page
" WHERE EmpID=" & g_CurrentEmpID & _
" AND LeaveType='" & [Link] & "'"
Set rs = ExecuteQuery(sql)

If Not [Link] Then

If rs("Balance") < nDays Then
MsgBox "Insufficient " & [Link] & " balance!", vbWarning
Exit Sub
End If
End If

' Insert leave application

sql = "INSERT INTO tbl_Leave" & _
"(EmpID, LeaveType, FromDate, ToDate, Days, Reason, Status)" & _
" VALUES(" & g_CurrentEmpID & "," & _
"'" & [Link] & "'," & _
"'" & [Link] & "'," & _
"'" & [Link] & "'," & _
nDays & ",'" & [Link] & "','PENDING')"
g_Conn.Execute sql
MsgBox "Leave applied for " & nDays & " day(s). Pending approval.", vbInformation
End Sub

5.7 Report Generation Module

The report module uses Crystal Reports integrated with VB6 to generate formatted reports. Reports
can be previewed on-screen, printed, or exported to PDF.
Report Name Description Output
Payslip Individual monthly payslip with earnings, Print / PDF
deductions, net pay

Payroll Register All employees salary register for selected Print / Excel
month

Department Summary Department-wise salary cost summary Print / PDF

PF Statement Employee-wise PF contribution statement Print

ESI Statement ESI contribution report for challan Print

Leave Report Employee leave balance and availed report Print / PDF
Annual Salary Card Full year salary breakdown per employee Print

Tax Statement (Form 16) Annual tax deduction certificate Print / PDF

' frmReports — Load Payslip Report

Private Sub cmdPayslip_Click()
If [Link] = -1 Then
MsgBox "Select an employee!", vbExclamation : Exit Sub
End If

Dim nEmpID As Integer

Dim nMonth As Integer
Dim nYear As Integer
nEmpID = [Link]([Link])
nMonth = CInt([Link]([Link]))
nYear = CInt([Link])

' Verify payroll exists

Dim rs As [Link]
Dim sql As String
Payroll Management System | Visual Basic 6.0 & SQL Server Page
sql = "SELECT PayID FROM tbl_Payroll WHERE EmpID=" & nEmpID & _
" AND PayMonth=" & nMonth & " AND PayYear=" & nYear
Set rs = ExecuteQuery(sql)
If [Link] Then
MsgBox "Payroll not processed for this period!", vbWarning
Exit Sub
End If

' Load Crystal Report

[Link] = [Link] & "\Reports\[Link]"
[Link] = _
"{tbl_Payroll.EmpID} = " & nEmpID & _
" AND {tbl_Payroll.PayMonth} = " & nMonth & _
" AND {tbl_Payroll.PayYear} = " & nYear
[Link] = 1 ' Preview
End Sub
Payroll Management System | Visual Basic 6.0 & SQL Server Page

Chapter 6: Testing

6.1 Unit Testing

Each module of the Payroll Management System was individually tested for correctness, boundary
conditions, and error handling.
Test Module Test Description Input Expected Output Stat
Case ID us
TC-001 Login Valid credentials admin / Login successful, PASS
admin123 redirect to main form

TC-002 Login Invalid password admin / Error message PASS

wrongpwd displayed

TC-003 Employee Save new employee All fields filled Record saved, grid PASS
refreshed

TC-004 Employee Duplicate EmpCode EMP001 Unique constraint error PASS

(exists) shown

TC-005 Salary Add component % HRA = 40% of Stored as percentage PASS

Basic type

TC-006 Payroll Process payroll EmpID=1, Net salary computed PASS

Month=5, correctly
Year=2025,
LOP=2

TC-007 Payroll ESI eligibility Gross salary = ESI = 0 (above limit) PASS
22000

TC-008 Leave Apply leave CL, 3 days, Applied, balance PASS

balance=5 updated

TC-009 Leave Excess leave SL, 15 days, Insufficient balance PASS

balance=8 error

TC-010 Report Payslip generation EmpID=1, Payslip displayed PASS

Month=5 correctly

6.2 Integration Testing

Integration testing verified that data flows correctly between modules:
• Attendance data correctly feeds into LOP calculation during payroll processing
• Salary structure defined in Masters correctly used in payroll computation
• Leave approvals correctly update attendance records
• Payroll data correctly populates payslip reports
• Department selection in payroll correctly filters employees

6.3 Sample Payroll Calculation

Test Employee: Rajesh Kumar | Designation: Senior Developer | Month: May 2025 | LOP Days: 2
Payroll Management System | Visual Basic 6.0 & SQL Server Page

Component Type Calculation Amount (₹)

Basic Salary Earning As per designation 35,000.00

HRA Earning 40% of Basic 14,000.00

DA Earning 10% of Basic 3,500.00

Travel Allowance Earning Fixed 1,600.00

Gross Salary Sum of Earnings 54,100.00

LOP Deduction Deduction (54100/26) × 2 4,161.54

Provident Fund Deduction 12% of Basic 4,200.00

ESI Deduction Gross > 21000, exempt 0.00

Professional Tax Deduction Gross > 15000 200.00

TDS Deduction Annualized computation 0.00

Total Deductions Sum 8,561.54

NET SALARY Gross − Total Deductions 45,538.46

Payroll Management System | Visual Basic 6.0 & SQL Server Page

Chapter 7: System Requirements

7.1 Hardware Requirements

Component Minimum Requirement Recommended
Processor Pentium IV, 1 GHz Intel Core i3 or above

RAM 512 MB 2 GB or more

Hard Disk 10 GB free space 50 GB or more

Monitor 800 × 600 resolution 1024 × 768 or higher

Network LAN for multi-user 100 Mbps Ethernet

Printer Any dot matrix / laser Laser Printer recommended

7.2 Software Requirements

Software Purpose Version
Microsoft Windows Operating System Windows XP / 7 / 10

Visual Basic 6.0 Front-end Development VB 6.0 SP6

Microsoft SQL Server Back-end Database SQL Server 2000 / 2005 / 2008

ADO 2.8 Database Connectivity ActiveX Data Objects 2.8

Crystal Reports 8.5 Report Generation Crystal Reports 8.5 / 9

Microsoft Office Excel Export MS Office 2003 / 2007+

7.3 Network Requirements

• TCP/IP protocol for client-server communication
• SQL Server must be accessible on the network
• ODBC / OLE DB provider installed on all client machines
• Firewall configured to allow SQL Server port (default: 1433)
Payroll Management System | Visual Basic 6.0 & SQL Server Page

Chapter 8: Advantages & Limitations

8.1 Advantages of the System

• Speed: Payroll processing for 500+ employees completes in under a minute
• Accuracy: Automated calculations eliminate manual arithmetic errors
• Security: Role-based access ensures data confidentiality
• Audit Trail: All payroll records are timestamped and non-deletable
• Compliance: Automatic PF, ESI, PT, and TDS deductions as per rules
• Reports: Instant generation of payslips, registers, and statutory reports
• Scalability: SQL Server backend handles growth in employee count
• User-Friendly: Menu-driven GUI requires minimal training
• Data Integrity: Foreign key constraints prevent orphaned records
• Cost-Effective: Saves significant HR processing time and cost

8.2 Limitations of the System

• The system is a desktop application and not web-accessible without modification
• VB6 is a legacy technology; no active Microsoft support since 2008
• No direct bank integration for salary transfer (requires manual bank file)
• Tax computation (TDS / Form 16) is simplified; a CA should review annually
• No email functionality for automated payslip distribution
• Does not cover recruitment, appraisal, or asset management
• Biometric/time-punch integration is not included in this version
Payroll Management System | Visual Basic 6.0 & SQL Server Page

Chapter 9: Conclusion & Future Scope

9.1 Conclusion
The Payroll Management System successfully demonstrates the application of Visual Basic 6.0 and
SQL Server in building a practical, real-world enterprise solution. The menu-driven interface,
structured database, stored procedures, and Crystal Reports integration combine to deliver a
complete payroll automation solution.
The project covered all stages of the Software Development Life Cycle (SDLC) — from requirement
gathering and system analysis to design, implementation, and testing. The final system achieves all
stated objectives: it automates salary computation, ensures statutory compliance, generates
formatted reports, and enforces data security through role-based access.
This project demonstrates the power of client-server application development and the effective use of
ADO for database connectivity. It serves as an excellent foundation for understanding enterprise
application development in Visual Basic.

9.2 Future Scope

The following enhancements are planned for future versions of the system:
• Web-based interface using [Link] or [Link] WinForms for modern deployment
• Mobile application for employees to view payslips and apply for leaves
• Direct bank file generation (NEFT/RTGS format) for salary transfer
• Biometric and RFID integration for automated attendance capture
• Advanced TDS computation with Form 16 generation as per Income Tax Act
• Email integration for automated payslip and leave approval notifications
• ESS (Employee Self Service) portal for employees
• Integration with ERP modules (Recruitment, Performance, Asset Management)
• Business Intelligence dashboard with charts and KPIs for management
• Cloud deployment on Microsoft Azure or AWS for remote access
Payroll Management System | Visual Basic 6.0 & SQL Server Page

Chapter 10: Bibliography

Books & References

1. Balagurusamy, E. — Programming in Visual Basic 6.0, Tata McGraw-Hill, 2005
2. Petroutsos, Evangelos — Mastering Visual Basic 6.0, SYBEX Publishers
3. Deitel & Deitel — Visual Basic 6.0 How to Program, Prentice Hall
4. Hotek, Mike — Microsoft SQL Server 2008 Step by Step, Microsoft Press
5. Forta, Ben — SQL in 10 Minutes, Sams Publishing, 2004
6. Cornell, Gary — Visual Basic 6 from the Ground Up, Osborne Media

Online Resources
1. Microsoft MSDN Library — [Link]
2. SQL Server Documentation — [Link]
3. W3Schools SQL Tutorial — [Link]
4. CodeProject VB6 Articles — [Link]
5. Stack Overflow Community — [Link]