[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

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

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.
2 Solutions

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

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]
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 :)

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 ...
msmerryAuthor Commented:
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.
msmerryAuthor Commented:
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
msmerryAuthor Commented:
My apologies, I have been unwell, and haven't been able to utilise this information as yet.
Thanks to all of you.
msmerryAuthor Commented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now