Student Records Management System in VB 6.0 with Access Database

In this post, we will create an application for Student Record Management System in Visual Basic 6 with Microsoft Access Database.

The list of Modules and Forms used in the program as follows.

(A) frmStudentManagement System
Caption: STUDENT RECORD MANAGEMENT SYSTEM

(B) frmLogin
Caption: LOGIN

(C) frmCourse
Caption: COURSE DETAILS

(D) frmStudentDetails
Caption: STUDENT DETAILS

(E) Module1.Bas

 System Design

First, we will design the System Model and simple high-level diagram of each module will be enough. There are many kind of the diagram to model a system especially when you are using Object-Oriented design. These models are specified in Unified Modelling Language (UML) which not scope of this post.

The Administrator has to log in to the system with username and password provided during registration process. No one is allowed to log in without a proper User ID and Password.

Once the Administrators logged in they can see form for Student Details where they can Insert, Delete, Update and Clear the Student Records. They also have the option to go to First Record, Last Record, Next Record and Previous Record.

Next, there is an option to maintain the Course Details, when you click a Specific button. We will discuss that later.

Data Model

ER Model

Tables

First steps after creating the data model are to make sure that you create an MS Access Database called StudentDB.mdb. If you have MS Access 2007 or higher version, save your database with the file extension (.mdb).

Create the following table with correct data types as shown in the following figures.

Components of frmStudentManagent System

Labels

Name :lblStudentManagement
Caption : Student Management System
Font: MS Sans Serif
Font-Size: 24 PX
Font-Weight : Bold

Buttons

Name : btnLogin
Caption: Log in
Font: MS-Sans-Serif
Font-weight:Bold
Font-Size: 10px

Code for frmStudentManagement System

Private Sub Login_Click()

    frmLogin.Show

End Sub

Components of frmLogin

Labels

Name: lblUserID
Caption : User Name:

Name : lblPassword
Caption: Password

Text-Boxes

Name : txtUserID
Text : 'leave blank'

Name : txtPassword
Text : 'leave blank'

Buttons

Name : cmdOK
Caption : Log in

Name : cmdCancel
Caption : Cancel

Code for frmLogin

Option Explicit
Dim rs As New ADODB.Recordset
Dim con As New ADODB.Connection
Dim constr As String
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()
    Dim userfound As Boolean
    With rs .Open "SELECT * FROM LOGIN",
    con, adOpenDynamic, adLockOptimistic
    'check the username '
    .MoveFirst While Not .EOF
    If txtUserName = !RollNo And
    txtPassword = !Password Then
    frmSTUDREC.Show
    LoginSucceeded = True
    Exit Sub
    Me.Hide
    ElseIf
    txtUserName <> !RollNo 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
    MsgBox ("connected")
End Sub

Components of frmCourse

Labels

Name : lblCID
Caption: Course ID

Name : lblCname
Caption : Course Name

Name : lblDuration
Caption : Duration(Semester):

Text-Boxes

Name : txtCID
Text : 'leave blank'

Name : txtCname
Text : 'leave blank'

Name : txtDuration
Text : 'leave blank'

Buttons

Name : cmdAdd
Caption : &ADD NEW COURSE

Name : cmdDel
Caption : &DELETE  COURSE

Name : cmdDisplay
Caption : &DISPLAY ENROLLED STUDENTS

Name : cmdExit
Caption : &EXIT

Name : cmdFirst
Caption : First Record

Name : cmdLast
Caption : :Last Record

Name : cmdNext
Caption : Next Record

Name ; cmdPrev
Caption : Previous Record

Code for frmCourse

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim constr As String

Private Sub cmdAddCourse_Click()

    con.Execute "INSERT INTO
    Course(CourseID, CourseName, Duration)
    VALUES ('" + txtCID.Text + "','" +
    txtCNAME.Text + "','" + txtDURATION.Text + "')"
    txtCID.Text = "" txtCNAME.Text = "" txtDURATION.Text = ""

    con.Close

End Sub

Private Sub cmdDelCourse_Click()

    con.Execute "DELETE * FROM COURSE
    WHERE CourseID = " &
    txtCID & ""

End Sub

Private Sub cmdDisplay_Click()

    Dim rsGrid As New ADODB.Recordset
    con.CursorLocation = adUseClient rsGrid.CursorLocation
    = adUseClient rsGrid.CursorType = adOpenStatic

    rsGrid.Open "SELECT * FROM Student_Records
    WHERE CourseID = " &
    txtCID & "",
    con, adOpenForwardOnly, adLockPessimistic

    Set DataGrid1.DataSource = rsGrid

End Sub

Private Sub cmdFirst_Click()

    With rs .Open "SELECT * FROM Course",
    con, adOpenDynamic, adLockOptimistic

    While Not .EOF
    txtCID.Text = rs!CourseID
    txtCNAME.Text = rs!CourseName
    txtDURATION.Text = rs!Duration

    .MoveFirst
    Wend
    End With

End Sub

Private Sub cmdLast_Click()

    With rs .Open "SELECT * FROM Course",
    con, adOpenDynamic, adLockOptimistic

    While Not .EOF
    txtCID.Text = rs!CourseID
    txtCNAME.Text = rs!CourseName
    txtDURATION.Text = rs!Duration
    .MoveLast

    Wend
    End With

End Sub

Private Sub cmdNext_Click()

    With rs .Open "SELECT * FROM Course",
    con, adOpenDynamic, adLockOptimistic

    While Not .EOF

    txtCID.Text = rs!CourseID
    txtCNAME.Text = rs!CourseName
    txtDURATION.Text = rs!Duration

    .MoveNext
    Wend
    End With
End Sub

Private Sub cmdPrev_Click()

    With rs .Open "SELECT * FROM Course",
    con, adOpenDynamic, adLockOptimistic

    While Not .EOF
    txtCID.Text = rs!CourseID
    txtCNAME.Text = rs!CourseName
    txtDURATION.Text = rs!Duration

    .MovePrevious
    Wend
    End With

End Sub

Private Sub Form_Load()

    Call loadcon
    MsgBox ("Connected")

End Sub

Components of frmStudentDetails

Labels

Name : lblStudentID
Caption : STUDENT ID

Name : lblFirstName
Caption : FIRST NAME:

Name : lblLastName
Caption : LAST NAME:

Name : lblCourseID
Caption : COURSE ID

Name : lblGrade
Caption : GRADE

Text-Boxes

Name : txtStudentID
Text : 'leave blank'

Name : txtFirstName
Text : 'leave blank'

Name : txtLastName
Text : 'leave blank'

Name : txtCourseID
Text : 'leave blank'

Name : txtGrade
Text : 'leave blank'

Buttons

Name : cmdAdd
Caption : &ADD

Name : cmdDel
Caption: &DELETE

Name : cmdUpdatev
Caption : &UPDATE

Name : cmdClear
Caption : &CLEAR

Name : cmdExit
Caption : &EXIT

Name : cmdDisplay
Caption : &DISPLAY

Name : cmdStudentCourses
Caption : Student Courses

Name: cmdNext
Caption : &Next Record

Name : cmdPrev
Caption : &Previous Record

Name : cmdFirst
Caption : &First Record

Name : cmdLast
Caption : &Last Record

Data Grid Control

Name : DataGrid1

Code for frmStudentDetails

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

Dim constr As String

Private Sub btnCourse_Click()
    Unload Me
    frmCourse.Show
End Sub

Private Sub cmdData_Click()
    Dim rsGrid As New ADODB.Recordset
    con.CursorLocation = adUseClient
    rsGrid.CursorLocation = adUseClient
    rsGrid.CursorType = adOpenStatic
    rsGrid.Open "SELECT * FROM Student_Records", 
    con, adOpenForwardOnly, adLockPessimistic
    Set DataGrid1.DataSource = rsGrid
End Sub

Private Sub cmdDel_Click()

    con.Execute "DELETE * FROM
    STUDENT_RECORDS WHERE RollNo = " &
    (Text1.Text) & ""
    MsgBox ("Record Deleted Successfully")

End Sub

Private Sub cmdExit_Click()
    Unload Me
End Sub

Private Sub cmdFirst_Click()

    With rs
    .Open "SELECT * FROM STUDENT_RECORDS", 
    con, adOpenDynamic, adLockOptimistic

    Text1.Text = rs!RollNo
    Text2.Text = rs!FirstName
    Text3.Text = rs!LastName
    Text4.Text = rs!CourseID
    Text5.Text = rs!Grade
    .MoveFirst
    .Close

    End With

End Sub

Private Sub cmdLast_Click()
    With rs
    .Open "SELECT * FROM STUDENT_RECORDS", 
    con, adOpenDynamic, adLockOptimistic
    Text1.Text = rs!RollNo
    Text2.Text = rs!FirstName
    Text3.Text = rs!LastName
    Text4.Text = rs!CourseID
    Text5.Text = rs!Grade

    .MoveLast
    End With

End Sub

Private Sub cmdNext_Click()

    With rs
    .Open "SELECT * FROM STUDENT_RECORDS", 
    con, adOpenDynamic, adLockOptimistic
    While Not .EOF

    Text1.Text = rs!RollNo
    Text2.Text = rs!FirstName
    Text3.Text = rs!LastName
    Text4.Text = rs!CourseID
    Text5.Text = rs!Grade
    .MoveNext
    Wend

    .Close

    End With

End Sub

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

    While Not .EOF

    Text1.Text = rs!RollNo
    Text2.Text = rs!FirstName
    Text3.Text = rs!LastName
    Text4.Text = rs!CourseID
    Text5.Text = rs!Grade

    .MovePrevious
    Wend

    End With

End Sub

Private Sub cmdSave_Click()

    con.Execute "UPDATE STUDENT_RECORDS
    SET FirstName = '" & Text2 & "',
    LastName = '" & Text3 & "',
    CourseID =" & Text4 & ",
    Grade = '" & Text5 & "'
    WHERE RollNo = " & Text1 & ""

  MsgBox ("Record Updated")

End Sub

Private Sub Form_Load()

    constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data
    Source=C:UsersGirishDocumentsStudentDB.mdb;
    Persist Security Info=False"
    con.Open constr

End Sub

Private Sub cmdAdd_Click()

    con.Execute "INSERT INTO
    STUDENT_RECORDS(RollNo,FirstName,LastName,CourseID,Grade)
    VALUES ('" + Text1.Text + "','" +
    Text2.Text + "','" +
    Text3.Text + "','" +
    Text4.Text + "','" +
    Text5.Text + "')"

    Text1.Text = ""
    Text2.Text = ""
    Text3.Text = ""
    Text4.Text = ""
    Text5.Text = ""

    con.Close

End Sub

Code for Module1.Bas

The Module1.Bas is the code for connection to Microsoft Access Database. Since you may not want to define this connection on each form that is used in the application.

Public Sub loadcon()
constr = "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:UsersGirishDocumentsStudentDB.mdb;
Persist Security Info=False"
con.Open constr
End Sub

Note : The C:\Users\Peter\Documents\StudentDB.mdb; is path to the Access database , you need to change is according to path of your Student Database.