Results 1 to 6 of 6
  1. #1
    marisacoul is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jun 2017
    Posts
    27

    Red face Managing Users and Permissions in MS Access 2013


    Hi all,

    Can anyone tell me how to restrict access to certain queries, forms, and tables for certain users? I already have a working login screen as well as a User table which lists staff and their level, e.g. User or Admin. I have checked all over the internet for this and sat through tutorials but they are way too confusing for me to implement. I have added in the User Security wizard but of course it won't run on Access 2013.

    A sample database would be great, or at least very specific instructions.

    Marisa

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    If it's the latter, I think the only way to do that is to make your database an accde file which prevents any user from doing anything but using the database as it's designed. If it's the former there are lot of other things you need to consider

    1. how many levels of users do you have
    2. are your users limited only by buttons/forms (they can click/use) or are they going to be limited by specific fields on forms as well
    3. how sophisticated are your users? the more sophisticated they are the more you have to do to plan for it (i.e. hiding forms/reports/tables from the navigation pane is step 1 for non-informed users, step 2 would be to hide the navigation pane and ribbon bar, and so on)

  3. #3
    marisacoul is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jun 2017
    Posts
    27
    Hi,

    1. I have two levels of users. I have two people who are admins and two people who are just users.
    2. They are not limited to specific fields... just the forms/queries/tables themselves.
    3. Not very sophisticated; I don't think they'll try to sabotage the database, haha. I just want to make sure Users can't open/edit things that are crucial to the function of the database, or sensitive tables containing information like usernames and passwords.

    Here's a chart I made to try and figure what I want each person to be able to see/edit, etc.

    Can see? Form Contact Entry Front End 1 Table Contacts Front End 1 Query Front End 1 Query Front End 2 Back-end Contact Entry Table 1 Permissions Table Front and Back End Employee names and passwords table Form logon
    Admin: yes No - hidden for everyone yes, can edit yes Yes, edit yes yes Yes, and edit
    User: yes No - hidden for everyone Yes, can edit no Read only no no Yes but no design view


    Hopefully this helps. I've been stumped on this for days!

    Marisa
    Last edited by marisacoul; 06-21-2017 at 12:12 PM.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Firstly, don't have passwords in the database. The user name will come from the Windows login and that is sufficient to know whether the user has permission to use the database and also what level they are. The format for this is - Environ("username"). When the database opens you can read the users table and do all the checking before opening a form.

    Your security requirements have to be handled manually, thru VBA. Such as a form opens read-only or editable. For queries put those behind a command button which you can control as to whether it is enabled or not (or visible or not).

    For actual database design changes, to forms or queries or tables, which you are allowing the admin users to do, you need a separate database. An accde is the only way to prevent a user from opening the nav pane and selecting objects. There are ways to hide it but the user can un-hide it. Or make it totally invisible but that has to apply to all users. The accdb version will be accessible to the admin users and will have to be put in a folder where the other users don't have rights. Then make a closed accde version which is where the work gets done.

    The back-end will have a startup code which says if this isn't a valid active admin user then close the database. You will have to switch off the ability to use shift/enter to bypass that. "Access Special Keys" under Options.

  5. #5
    marisacoul is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jun 2017
    Posts
    27
    Here's what I'm going to do. To simplify, I'll have two accounts, then, admin and user, instead of names.

    I know this code is wrong but I can't for the life of me figure out why... I am teaching myself VBA...

    'I want it to check if password is correct but also whether individual has input "User" in the textbox for username
    If Me.txtPassword.Value = DLookup("strEmpPassword", "Table Employees", "[lngEmpID]=" & Me.cboEmployee.Value = "User") Then

    lngMyEmpID = Me.cboEmployee.Value
    Msgbox "You have logged in as user"
    DoCmd.Close acForm, "Form Logon", acSaveNo
    DoCmd.OpenForm "Add New Story"
    [I would love a custom navigation pane to appear here for the user but I have no idea how to do this, please help]

    'I want it to check if password is correct but also whether individual has input "admin" in the textbox for username
    If Me.txtPassword.Value = DLookup("strEmpPassword", "Table Employees", "[lngEmpID]=" & Me.cboEmployee.Value = "Admin") Then

    lngMyEmpID = Me.cboEmployee.Value
    Msgbox "You have logged in as admin"
    DoCmd.Close acForm, "Form Logon", acSaveNo
    DoCmd.OpenForm "Add New Story"
    Call DoCmd.SelectObject(acTable, , True) 'navigation pane appears, but only for the admin

    Else
    MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
    Me.txtPassword.SetFocus
    End If

    Please correct my code if you can, I've been stuck on this for ages!

    Marisa

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Add the Table Employees as the record source for the logon form. In the AfterUpdate event of cboEmployee,
    Me.Filter="lngEmpID='" & Me!cboEmployeeValue & "'"
    Me.FilterOn=True
    Me.Requery

    In the AfterUpdate event of the password,
    If Me!passwordentered = Me!passwordfromtable Then
    ....open any form you want, I am not sure what you are asking about how to do a custom nav form
    If Me!cboEmployee="Admin" then
    ....

    Do not close the logon form, keep it open so that all other forms/queries/reports will know whether it is user or admin.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Users permissions
    By Mehvan in forum Access
    Replies: 8
    Last Post: 04-25-2017, 11:15 AM
  2. How to add/remove users to ms-access 2013
    By alidabiri in forum Access
    Replies: 1
    Last Post: 10-02-2015, 08:16 AM
  3. change permissions on access 2013
    By jassie in forum Access
    Replies: 1
    Last Post: 08-17-2015, 01:24 PM
  4. Replies: 4
    Last Post: 08-22-2014, 10:34 AM
  5. Access 2007 Users and Permissions Problem
    By botts121 in forum Security
    Replies: 3
    Last Post: 07-06-2009, 10:23 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums