How To Implement A Simple System User Level In VB6 and MS Access (The Cool Dude Way)

In an organization or company, each employee has certain access level to company's sensitive information. The company keeps an organizational chart which enforce the hierarchy of employee and their current  position. This will enable us to determine which user has access to which part of the system. Since we already have our login facility added to our fancy Project, we will now implement the System User Level.

The login form will simply filter valid users to the system. But once valid users are in, we still need to implement further security check. The main purpose of the SUL is to limit the access of those valid users to the modules or elements of our system.

Note: "This tutorial will just introduce a simple (lame) approach on how to implement SUL. However, I encourage you to come up with your own method or approach, a kick-ass one. Once again, use some logic."

We will jump-start by expanding our menu list, follow the structure below:

Masterfiles

• Manage Student
• Manage Subject

 Transactions

• Enroll
• Grade Entry

Queries

• Search Student

Reports

• Student list
• Student Grade

Help

• About

Log-out


For this tutorial, we will assume that there are three types of user: Administrator, Teacher and Encoder.
Below are their user levels:

Administrator - Overall access
Teacher - Can only access Grade Entry, Search Student, Student list and Student Grade
Encoder - Can only Manage Student and Manage Subject

Now we are ready to edit the database. We need to add another field to the 'User' table, see illustration below:

Table: User
Field Name               Data Type         Attribute           Value
username                  Text                    Field Size          15
password                  Text                    Field Size           8
fullname                   Text                    Field Size          100
usrlevel                    Number             Field Size          Byte

The next step is not very impressive, but it will work for now. For the sake of simplicity (but lame) we will device a picture box and a label control to hold our user's user level variable. Add a picture box to your MDIForm. Inside the picturebox, draw a label and name it 'lblUserLevel'.

At this point, we are now ready to write some code. Copy and paste the snippet below to your login form. The exact place on where to put the code is for you to figure out. Use some logic lads.

        MDIForm1.lblUserLevel.Caption =Adodc1.Recordset.Fields("usrlevel")       
        Dim lvl As Integer   

        lvl = MDIForm1.lblUserLevel.Caption
    
        If lvl = 1 Then
            'The following block was written by Miss Mendoza
            MDIForm1.muTransGradeEntry.Enabled = True
            MDIForm1.mnuQSearchStud.Enabled = True
            MDIForm1.mnuRepStudlist.Enabled = True
           
            MDIForm1.mnuStudForm.Enabled = True
            MDIForm1.mnuSubjForm.Enabled = True
           
            MDIForm1.mnuTransEnroll.Enabled = True
            MDIForm1.mnuRepStudGrade.Enabled = True
            MDIForm1.mnuHelpAbout.Enabled = True
            'End of Miss Mendoza's code
        

       
        ElseIf lvl = 2 Then
           
            MDIForm1.muTransGradeEntry.Enabled = True
            MDIForm1.mnuQSearchStud.Enabled = True
            MDIForm1.mnuRepStudlist.Enabled = True
           
            MDIForm1.mnuStudForm.Enabled = False
            MDIForm1.mnuSubjForm.Enabled = False
           
            MDIForm1.mnuTransEnroll.Enabled = False
            MDIForm1.mnuRepStudGrade.Enabled = False
            MDIForm1.mnuHelpAbout.Enabled = False
        
       'Code of Miss Lito   
        ElseIf lvl = 3 Then
            MDIForm1.mnuStudForm.Enabled = True
            MDIForm1.mnuSubjForm.Enabled = True           
           
            MDIForm1.mnuTransEnroll.Enabled = False
            MDIForm1.muTransGradeEntry.Enabled = False
            MDIForm1.mnuQSearchStud.Enabled = False
            MDIForm1.mnuRepStudlist.Enabled = False
            MDIForm1.mnuRepStudGrade.Enabled = False
            MDIForm1.mnuHelpAbout.Enabled = False                         
           
        End If
        'End of Miss Lito's code 

For the logout module:

        lblUserLevel.Caption = ""
        frmLogin.Show 1

Finally, we have one thing left to do and that is to test the  program. Run the application and login using our user accounts.

How to Create a Simple Login Form in VB6 Using MS Access Database

Viral Stuff and Trending News

 

In every database system, a very important function which contributes to the security of the application is the login form. Using a login facility, we can filter or prevent the unauthorized access to our application.

In this simple tutorial you will learn how to create a dynamic login form using VB6 and Microsoft Access Database. For connectivity, we will be using Microsoft ADO. I will assume that you already have your MS Access database, if not start a new database. Follow the schema of our ‘user’ table below: 

Table Name: user 
Field Name                       Data Type                          Attribute                             Value 
username                           Text                                       Field Size                           15
password                           Text                                       Field Size                            8
fullname                              Text                                       Field Size                           100

Next step would be the adding of new form to your Project. Please see description below:

Graphical User Interface

Controls and their Attributes 

Label     Control Type                                      Attribute                       Value 
1            TextBox                                            Name                              txtUser
2            TextBox                                            Name                              txtPasswd
                                                                        PasswordChar              *
3             Adodc                                             Name                              Adodc1
                                                                        CommandType              1 – adCmdText
                                                                        RecordSource               SELECT * FROM User
4              CommandButton                          Name                              cmdLogin
5              Label                                             Caption                           User name:
6              Label                                             Caption                           Password:

Next, let us setup your connection using Adodc control. Please follow the steps below: 

1. Right click on your Adodc1 control.
2. Select ADODC Properties.  
3. Toggle ‘Use Connection String’ option box and click Build.  
4. Under Connection tab,  type or browse your MS Access database  and set the credential if there is any, otherwise leave the credential box as is.  
5. Finally, check your connection by clicking Test Connection button. If you have successfully set the connection, you will receive a message box saying “Test Connection Succeeded.”.  
6. Click OK. 
7. Lastly, simply copy and paste the code below to your code window:

Source Code: 

Private Sub cmdLogin_Click()
    Dim user As String
    Dim passwd As String
    Dim result As Integer
    Dim sql As String

    user = txtUser.Text     'fetch the username from the box

    passwd = txtPasswd.Text 'fetch the password from the box

    'sql query below

    sql = "SELECT User.username, User.password, User.fullname " _

    & "From [user] " _

    & "WHERE (((User.username)='" & user & "') AND " _

    & "((User.password)='" & passwd & "'))"

    Adodc1.RecordSource = sql   'run query

    Adodc1.Refresh  'refresh recordset

   

    result = Adodc1.Recordset.RecordCount   'count the number of query result

   

    If result > 0 Then 'if result is greater than zero, it means that the user is valid

       

       

       

        Unload Me   'unload login form

    Else

        MsgBox "User name or password is incorrect!", vbExclamation

    End If

   

End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)

    If UnloadMode = 0 Then  'X button was clicked

        End

    End If

End Sub

Viral Stuff and Trending News