Microsoft Access user password restrictions [close

Question

I'm trying to restrict my Access database to be used by certain people and to give those people certain restrictions depending on who they are. I'm seeing a couple articles about creating a table with users and passwords and using some VBA code for validation (or something of the sort, I'm pretty VBA-illiterate). Does anyone know where I can find a step-by-step article on how to do this?

Alternatively, maybe making it simpler where I have a form with links to other forms. The links would read "Data Entry", "Manager", etc. and the links could be password protected. I.e., when the Manager clicks the Manager link, he has to input a password and is then taken to a form where he can edit any and all information. Whereas, when Data Entry clicks, they enter a different password and are then taken to a form where they are only displayed fields in which they can edit.

Thank you in advanced.

Answer 1

You want a front end/back end database. You can find the splitting functionality in Access at Database Tools -> Move Data -> Access Database or you can build the two DBs manually by creating another database with forms and then linking tables.

The back-end is a the main database with all of the admin access. This can be password protected by going to File -> Info -> Encrypt with Password. This contains all of the data tables.

The front-end database is the user-interface with the forms. This is linked to the back-end so that it can update the back-end where forms have been provided to do so.

Answer 2

Security is a critical hitting point in MS Access. If you want to implement login function, I assume your business model contains somehow sensitive information. Such business model usually expects/requires more than average software designing skills.

At first you need to define/design your business requirements starting from

• If MS Access is something for you to start.
• Then what version of MS Access to use
• Single user, multi user, local access or internet access
• if the security level provided by MS Access sufficient for you.
• scalability
• etc...

MS Access allows to see the table contents which means, When you save your user details any user has access to the table will see the user details and able to use/bypass them.

You get some sort of security when you compile your Access database into a MDE or ACCDE which will prevent user accessing VBA functions or going into design mode. This will potentially help you in many ways to increase the security. Including releasing updates of your front-end application.

To answer your question regarding user access level. I would personally split the database before anything else to maintain centralized data throughout the application. if your application is fairly small, you can follwo this tutorial for a simple login form function. http://www.databasedev.co.uk/login.html

To distinguish user levels you need to create a user role table and assign roles for each user. Create a new function which checks if a user has/is assigned to the requested roles if yes allow to perform the action. If not warn the user. something like this:

tables like:

• tbl_user: {user_id, user_name ....}
• tbl_roles: {role_id, role_name, ...}
• tbl_user_roles: {role_id,user_id, assigned_date,...}

a simple function to check the user level:

Public Function FN_IS_USER_IN_ROLE(iUSER_ID As Long, iREQ_ROLE As String) As Boolean

    Dim MyDB As Database
    Dim MyRS As Recordset

   On Error GoTo FN_IS_USER_IN_ROLE_Error

    FN_IS_USER_IN_ROLE = False
    Dim SQL_GET As String
    SQL_GET = "SELECT tbl_user.user_name, tbl_roles.role_name " & _
              "FROM (tbl_user_roles INNER JOIN tbl_user ON tbl_user_roles.user_Id = tbl_user.user_id) INNER JOIN tbl_roles ON tbl_user_roles.role_id = tbl_roles.role_id " & _
              "WHERE (((tbl_user.user_id)=" & iUSER_ID & ") AND ((tbl_roles.role_name)='" & iREQ_ROLE & "')); "

    Set MyDB = CurrentDb
    Set MyRS = MyDB.OpenRecordset(SQL_GET)
    Dim mRc As Long
    mRc = Nz(MyRS.RecordCount, 0)
    If mRc > 0 Then
        FN_IS_USER_IN_ROLE = True
    End If


   On Error GoTo 0
    Set MyRS = Nothing
    Set MyDB = Nothing
   Exit Function

FN_IS_USER_IN_ROLE_Error:
    FN_IS_USER_IN_ROLE = False
    Dialog.Box "Error " & Err.Number & " (" & Err.description & ") in procedure FN_IS_USER_IN_ROLE", vbExclamation
End Function

and check if the user has enough privilege simply by:

Dim PR As String
PR = "Admin"
If (FN_IS_USER_IN_ROLE(PR)) Then
    'Do something
Else
    'Me.Undo
    'Cancel = True
    MsgBox "You do not have sufficient permissions to perform this task!" & vbNewLine & "Required access level: " & PR, vbCritical, "Access denied.."
    Exit Sub
End If

Try this and let us know if you succeed.