WellMan: Creating User Access Control through Login Screen in Microsoft Access
In the last blog we talked about creating a centralized database that can manage all data related to wells. We also gave an overview of WellMan, our take on creating this database that will not only include parameters and information related to a well but also link to all the documents.
In continuation on our blog series, this installment will discuss creation of user profiles for a database. Access control is an important element of any database where confidential, sensitive and restricted information is stored. If we are to centralize all the information related to a well in one place, it is paramount that there is a logical system of how, when, who can retrieve that information. This logical access is achieved through the creation of user profile and creating a login screen which is the first thing the user is greeted with when they access the database.
Although we will be customizing this login screen for our software this workflow can be used in any database created on Microsoft Access to create user profile and login screen. So, without further ado let's start.
Creating the GUI - Login Form
There are different ways in which every programmer builds their program that consists of back and code and frontend UI experience. I personally, always design the front-end form first (whenever possible) before writing any code. This not only gives me perspective but also motivates me internally as I see the front end of the program completed and in some way in a polished form.
There is no limit to the beauty you can add on forms in Microsoft Access however I like to keep my forms simple, easy to read and in minimalistic fashion. Following is a pictorial of the login form in WellMan. As seen, the form only contains two labels and two user textboxes to enter the user id and password. This form shows up as the program is opened that prompts user id and password from the user.
Creating the User Table
The second phase in creating the user access module is to create a sub database that stores information about the user profile, their user id & passwords and level of access available to that user. Every time the program is opened and a user enters their details in the login screen, the program cross checks the entered details with this user table and proceeds accordingly if the details are found or shows an error message otherwise. As with the case in designing the login form, the details that can be stored for each user is customizable but for the sake of simplicity and ease of creation, we will limit this table on 6 columns; first & last name, user ID, password, user team & access level. Following is a pictorial of this simple table created in Microsoft Access with few dummy values populated in the table:
Programming the Login Form
Once the login form and user table is created, we can program the command buttons on the login form to execute a cross checking algorithm between the user table and details entered in the textboxes of the login form. A very simple if then else code can perform this activity where, if the user id and password entered by user equals to the values stored in the table, then access is granted, else access is denied. However to add functionality regarding access level, display warning and or information message boxes and fine tune the overall working, we have modified this code to make it a bit more detailed. Following is the simplified version of code used in our login screen.
Dim User As String Dim UserLevel As Integer Dim TempPass As String Dim ID As Integer Dim UserName As String Dim TempID As String
If IsNull(Me.txtUserName) Then MsgBox "Please enter UserName", vbInformation, "Username required" Me.txtUserName.SetFocus
ElseIf IsNull(Me.txtPassword) Then
MsgBox "Please enter Password", vbInformation, "Password required" Me.txtPassword.SetFocus
ElseIf (IsNull(DLookup("UserLogin", "tblUser", "UserLogin = '" & Me.txtUserName.Value & "' And UserPassword = '" & Me.txtPassword.Value & "'"))) Then
MsgBox "Invalid Username or Password!"
Else
DoCmd.OpenForm "WMMain"
End If
Finally implementing all the three phases discussed above, we have successfully created user access control in our database.
As a very crude example, lets assume we have two users, the first one is a project manager who has access to the master hopper of all the wells in the company while second user is an engineer working specifically on one technology like hydraulic fracturing. Now when both users open the program, each of them are greeted with the login screen were they enter their respective details. As the first user has more access, fig-1 shows his main navigation screen where he can access the master hopper and all the other hoppers. Figure-2 below shows the main navigation screen of the second user which has limited access and can only work on hydraulic fracturing hopper while all other options are disabled for this user.
Figure-1: Main navigation screen for project manager
Figure-2: Main navigation screen for user with limited access - Note how all other hoppers are disabled for this user
We hope that this blog was a help in explaining how user access control can be created in Microsoft access databases. This was a simple way to achieve the objective, and as it is with everything, there are numerous ways in which this can be customized to suit your requirement.
If you like this blog, kindly comment and share.
