Is there a way to protect a field so that only certain users can edit?

Question

I have an Access 2010 DB that I need to update a field in. Right now I have a button on one of my forms that will duplicate the selected row. I know that I can make a field not duplicate by setting the Locked property to Yes on that field.

What I need to do is find a way to make a field not duplicate, but still be editable to certain people. I don't want to set the Locked property as that will lock it for everyone.

I've no idea how to do this except to change the working of the Duplicate record button. Right now it simply makes a copy of the row that is selected and pastes it into the New Record row. This takes all fields that are not Locked.

I read about possibly using a bunch of variables and setting them to the values of each of the fields, but this seems cumbersome since I have 160+ fields that I would have to setup. And then pasting them into the New Record row. I would prefer not to have to do this.

I have used VBA just not with Access very much. A VBA solution is fine with me, I have not tried anything as I don't know where to even start.

So when you click your button, it will create a new row in the table with all fields from the old record except for any fields that are set to 'Locked'? How will you decide which 'Locked' fields you really want someone to be able to edit? And how will you decide which users you want to allow? — Wayne G. Dunn, Apr 19 '17 at 19:25
@WayneG.Dunn There is only one field that I want to not copy and have only certain users able to edit. I don't know yet the user list that will be allowed to edit. I only maintain the DB, the person who created it has left the company. — Mike, Apr 19 '17 at 19:49

score 1 · Accepted Answer · edited May 23 '17 at 12:09

I suggest a sub to lock / unlock the controls, like this:

Private Sub SetControlsLocked(ForceLocked As Boolean)

    Dim bLocked As Boolean
    Dim sUser As String

    If ForceLocked Then
        ' Always lock before copying
        bLocked = True
    Else
        ' More secure method than Environ("USERNAME")
        sUser = CreateObject("WScript.Network").UserName
        ' Lock for everyone except some users
        ' If the list is longer or changes regularly, lookup the Username in a table instead
        bLocked = Not (sUser = "jane" Or sUser = "bob" Or sUser = "mike")
    End If

    ' do the locking
    Me!Control1.Locked = bLocked
    Me!Control2.Locked = bLocked

End Sub

(refer to comments on https://stackoverflow.com/a/32565953/3820271 for "more secure" claim).

and apply in your copy button routine...

Private Sub cmdCopy_Click()

    ' Lock before copying!
    Call SetControlsLocked(True)

    ' ... your existing copy routine

    ' Unlock for special users
    Call SetControlsLocked(False)

End Sub

... and when opening the form:

Private Sub Form_Load()
    ' Lock for non-special users
    Call SetControlsLocked(False)
End Sub

This did it! Thanks for the help! – Mike Apr 20 '17 at 12:18 — Mike, Apr 20 '17 at 12:18

score 0 · Answer 2 · answered Apr 19 '17 at 19:43

0

What I do is have a table of users and their permissions level and Windows login username. I capture username with Environ("USERNAME") and match with the users record and set up forms appropriately, usually just disable/hide particular buttons.

But really have to do a lot more setup of the database to prevent users from accessing restricted data. Like hide navigation pane, disable ribbon, disable right click shortcut menu, disable function keys, maybe even publish as executable.

But if they know where the backend is, what stops them from just opening it? Unless you go to something like SQLServer database, really nothing.

answered Apr 19 '17 at 19:43

June7

19,874
8
24
34

I'm not worried about the end users messing with anything in the backend. None of them even know there is one. The Access DB is just a frontend for the SQL DB. I'm the only one who can directly access the SQL. I just need a way to restrict what users can edit one field. – Mike Apr 19 '17 at 19:51
Okay, what I suggest is one way and it is transparent to the users, they won't even know because they are not required to do a login. When frontend opens, code can check if already a record for them and then respond accordingly, maybe refusing them access until they 'contact administrator' in case someone neglected to add new employee to the Users table. – June7 Apr 19 '17 at 19:54
So the `Environ("USERNAME")` captures the windows username of whoever opens the DB? If that's the case I might be able to just use a list of the users who do need the edit ability and just not allow anyone else. – Mike Apr 19 '17 at 19:58
It captures username of whoever is logged into that computer where the database is opened. I haven't tested situation where more than one user has logged onto the same computer and independently opens the database. Everybody in the office has their own computer. – June7 Apr 19 '17 at 20:10
Note that a simple batch file like this will spoof `Environ("USERNAME")` : `SET USERNAME=superuser // start "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" "C:\Users\neo\Documents\Database1.accdb"` – Andre Apr 19 '17 at 20:23
Yes, as they say "locks are made to be picked". If someone really wants to do something bad enough they will find a way. – June7 Apr 19 '17 at 20:36

Is there a way to protect a field so that only certain users can edit?

2 Answers2