Passwords for Access database which restrict some users so they cannot change the data.

Posted on 2012-09-13
Last Modified: 2013-03-06
I am using Access 2007.
I have a Login Screen which asks for username and password.  It allows for individual username/passwords.  
Private-Sub-cmdLogin.docximage of logon form
What I need to work out is how do I restrict some people's access.  What I want is for the Admin person to have full access to change data obviously, but other people (committee members) to be limited to viewing the data or printing out reports.  
Advice would be much appreciated.
Question by:msmerry
    LVL 10

    Assisted Solution


    Personnaly I have do like this :

    I have created a field "Fonction" that determine the users role, and I have also used the username of the account logged in to match the table, then no password is needed.

    I have used all the security with theses function below.

    Let me know if you want something else.

    Best Regards

    Function :
    Public Function GetFonctionUser() As String
    Dim WindowsUser As String
    Dim ComputerName As String
    Dim FonctionUser As String
    ComputerName = Environ("COMPUTERNAME")
    WindowsUser = Environ("USERNAME")
    WindowsUser = LCase$(WindowsUser)
    GetFonctionUser = Nz(DLookup("[Fonction]", "[TUsers]", "[Username]='" & [WindowsUser] & "'"), 0)
    End Function
    Public Function GetWindowsUser() As String
    Dim WindowsUser As String
    Dim ComputerName As String
    Dim FonctionUser As String
    ComputerName = Environ("COMPUTERNAME")
    WindowsUser = Environ("USERNAME")
    GetWindowsUser = LCase$(WindowsUser)
    End Function

    Open in new window

    Usage example :

    FonctionUser = GetFonctionUser()
    If [FonctionUser] = "committee" Then
    DoCmd.OpenForm "FormTest", acNormal, , , acFormReadOnly
    End If
    If [FonctionUser] = "admin" Then
    DoCmd.OpenForm "FormTest", acNormal, , , acFormEdit
    End If

    Open in new window

    LVL 20

    Accepted Solution

    I basically agree with MadShiva - if all users have their own password-protected Windows logins then you will only annoy them by asking for another username and password.

    However, I would implement it differently - either using "Roles" or "Security levels".

    Using Security Levels is straighforward if you have an ascending scale of access - for example:
      Level 1 = limited readonly
      Level 2 = everything readonly
      Level 3 = limited modify access
      Level 4 = more modify access
      Level 5 = God (aka Admin")

    You can look up a user table that has a "SecurityLevel" field to find the level for the user and store it in a global variable.  Then, whenever a form is opened, you can take action depending on the SecurityLevel.  Here are some examples:

    Me.AllowEdits = (SecurityLevel >= 3)

    Me.txtEmployeeSalary.Visible = (SecurityLevel >= 5)

    If SecurityLevel < 2 Then Cancel = True ' prevent form from opening

    Using Roles is more flexible because it allows some users full access in some areas but not in others.  You need a table of Roles (RoleID, RoleName) and one of UserRoles (UserName, RoleID).  RoleNames could be such as "Sales Staff" or "Reception" or "Admin".

    To ascertain whether the current user "owns" a role, you need to look up a query of RoleNames and UserNames to see if there is a match.

    To find out the Windows username, MadShiva had suggested using Environ("USERNAME").  However, I don't regard this as very secure, as it is easy to launch a command window and spoof USERNAME with a Set command.  Instead, use a function (or readonly property) that calls the Windows API like this:
    Option Compare Database
    Option Explicit
    Private m_UserName As String
    Private Declare Function GetUserName _
      Lib "advapi32.dll" _
      Alias "GetUserNameA" ( _
        ByVal lpBuffer As String, _
        nSize As Long _
      ) As Long
    Public Property Get Username() As String
    Dim nLen As Long
      If Len(m_UserName) = 0 Then
        nLen = 100
        m_UserName = String(nLen, 0)
        GetUserName m_UserName, nLen
        If nLen > 0 Then m_UserName = Left(m_UserName, nLen - 1)
      End If
      Username = m_UserName
    End Property

    Open in new window

    Good luck!
    Graham [Access MVP since 1996]
    LVL 10

    Expert Comment

    Hi Graham,

    Thanks for the usefull information and other way to implement this.

    Has for the username depend on the security that apply on the computer I don't have succesfully spoofed the username, but anyway it's better with the windows API :)

    LVL 84
    Was there some reason you closed this virtually identical question out without responding to any Expert comments (and awarding B grade):

    Had you asked for more guidance in that question you very likely would have received it. Closing with a "B" grade without giving the Experts the courtesy of a "Thank you" won't win you many friends here ...

    Author Comment

    In response to LSMConsulting:-
    Yes, there was a reason for giving points to people who had at least gotten me an answer to the part of my question about setting up a login screen and then starting this as a new Question - I had been told off once before for continuing on when I should have started a new Question!  So I am a confused person who can't work out when to do what!

    As for not giving you the courtesy of a Thank You, I am amazed, because, as I usually do, I wrote quite a lengthy Thank You to each and every respondent, including you, in which I explained how useful I found each referral  - for instance, the WikiHow one appeared to be written for an earlier version of Access, and I was unable to work out how to translate that to Access 2007.  I gave the bulk of the points to the respondent who had given me 4 referrals, three of which were effective, but I always give some points to any person who takes the time to answer me.  You referred me to peterssoftware - my comment was that I preferred to learn, and therefore took the other advice.
    What I cannot recall is giving a B, so I make no apologies for something I cannot recall.
    WHY my response did not get recorded is beyond me. I have often written a response several times over because it simply disappears - there is no provision for saving a draft while I go and check something else - like look up a previous question.  To check this, I just opened another of my questions 9without asking to open in a new tab), and sure enough when I came back this response had disappeared - but this time I had been smart enough to copy this text, just in case, otherwise I would be typing it all again.
    My apologies for offending you, or the other respondents, even though it was unintentional.

    Author Comment

    To madshiva and Graham, I will work through your suggestions - thank you for your input - and I will be back either with further questions or with thanks

    Author Comment

    My apologies, I have been unwell, and haven't been able to utilise this information as yet.
    Thanks to all of you.

    Author Closing Comment

    Even though I have been unable to test the advice as yet, I have given an A Grading, because what I read has made sense.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now