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.

Setting Up the ADO Data Control

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:

STEP BY STEP
Creating an ADO Data Control

1. Add the Microsoft ADO DataControl 6.0 (OLEDB) from the Project, Components menu dialog box. The ADO Data Control icon should now appear in the VB toolbox.

Adding the Microsoft ADO Data Control to your project's components.

    2.  Place an instance of the ADO Data Control on the form.

 

Placing an instance of the ADO Data Control on a form.

3. Change the control's Name and Caption from their default values. (The Caption is for information only, so you can set it to whatever you think will be most informative for the user.) 

4. Set the ConnectionString property using steps 5–9.

5. Click the ellipsis next to the ConnectionString property in the ADO Data Control's Properties window to bring up the Property Page dialog box for this property, as shown below.

The first and only Property Page dialog box for the ADO Data Control's ConnectionString property.

6. As Source of Connection, choose one of the following three options:

• Ese Data Link File. If you choose this option, you will be able to click the Browse button to specify an existing *.UDL file).
• Use ODBC Data Source Name. If you choose this option, you will be able to choose an existing ODBC DSN from the drop-down list, or you can create a new DSN by clicking the New button.
• Use Connection String. If you choose this option, you will be able to click the Build button to bring up the Data Link Properties tabbed dialog box. 

The following steps assume that you have chosen this option. 

 

On the Provider tab of the Data Link Properties tabbed dialog box, choose an OLE DB data provider, such as Microsoft Jet 3.51 OLE DB

Choosing a provider for the ADO Data Control's ConnectionString property.

7. The Connection tab of the Data Link Properties tabbed dialog box will vary in appearance, depending on the provider specified in the preceding step. In the case if the Microsoft Jet 3.51 OLE DB, you are prompted to choose an Access data file and set some security options.

Setting up connection information for the Jet provider

   

8.  Click OK to accept the ConnectionString options you have built.

9. Still in the ADO Data Control's Properties window, navigate to the RecordSource property and click the ellipsis button.

10. On the RecordSource tab (see Figure 8.18) of the resulting Property Page dialog box, choose the CommandType (adCmdUnknown, adCmdText, adCmdTable, adCmdStoredProc).

The Property Page dialog box for an ADO Data Control's RecordSource property.

  

11. Complete the dialog box appropriately for the CommandType that you chose:

• If you chose adCmdText, fill in the text of a valid Select statement in the Command Text field

A valid Select statement as command text for the RecordSource property.

•  If you chose adCmdTable or adCmdStoredProc, fill in the appropriate table or stored procedure     name in the Table or Stored Procedure Name drop-down list.

A table or stored procedure name for the RecordSource property.

12. Click OK to end the RecordSource dialog box.