granting permissions

[wolfm]

Hello:
I'm a bit confused on the best and simplest way to create permissions in an Access database.


For example, by setting and using a password, I would be the only one who could add records, edit records, and database design.
I want all users to be only able to read data, run reports and queries.
I am running Access 2016, Windows 7 Enterprise, and the database is stored in a network folder.

If I create a password for the database, how do I set permissions?

Or, is it better to split the database? Everything I've read about splitting doesn't seem to give me the options I want.

Any advise would be greatly appreciated. Thanks.

[ranman256]

its always better to split the db, so many people can use it.
If you are the only one that will edit, then set all forms
.allowEdits = false
.allowAdditions = false
.allowDeletions = false

then at form load, enable them back if YOU are the user:

sub form_load()
 vID= Environ("Username")
 if vID = "bob123" then
  me.allowEdits = true
  me..allowAdditions = true
  me..allowDeletions = true
endif

[Micron]

and database design

This is irrelevant. You should only be doing development in a copy of the db (yes, on the FE of a split db) . Otherwise, not only would you need to open the production db exclusively (thereby likely locking everyone else out) should you break it, you're in trouble. Should you ever end up with more than 2 levels of permissions, you'll need a more robust approach. If that's possible, you might as well start out on that path now. It would entail a user table with fields that at least, control permissions.

[alansidman]

Here is a really good tutorial (albeit) somewhat lengthy that should help you.

https://www.mrexcel.com/forum/micros...ns-system.html

[SierraJuliet]

Create a "Permissions" attribute (i.e. column/field) where ever you have usernames and passwords saved for users.

Create standard permission levels like admin, manager, user, guest, etc.

I would then create a lookup table (tlkpPermissionLevel) containing two attributes "Permissions" and "ID" with the primary key set to the permissions attribute. This is useful if you have a form where you enter user information, such as first name, last name, username and password, and so forth, but may also set persmissions there as well by showing only permissible options in a combo box drop down list by querying the permissions table. If not using a form for adding username, password, and permissions then disregard this step. Example of form query for permissible permissions where red is the name of combo box drop down list and blue is name of attribute in the tlkpPermissionLevel table:

Private Sub txtPermissionLevel_GotFocus()

Dim strSQL As String

strSQL = "SELECT PermissionName FROM tlkpPermissionLevel ORDER BY ID "

CurrentDb.OpenRecordset strSQL

Me![txtPermissionLevel].RowSource = strSQL

Me![txtPermissionLevel].Requery

End Sub

On each form containing permission dependent functionality simply call on the permissions level then set (blue) what each user based on their permission level is permitted to perform.

Private Sub Form_Load()

On Error Resume Next

Dim Permission As String
Permission = [Forms]![frmLogin]![txtPermissions].Value

If Permission = "Admin" Then

'code whatever you want to happen

.AllowAdditions = True
.AllowEdits = True
.AllowDeletions = True

ElseIf Permission = "Manager" Then

'code whatever you want to happen

.AllowAdditions = False
.AllowEdits = True
.AllowDeletions = False

ElseIf Permission = "User" Then

'code whatever you want to happen

.AllowAdditions = False
.AllowEdits = False
.AllowDeletions = False

ElseIf Nz(Permission, "") = "" Or Nz(Permission, "") = 0 Then

'code whatever you want to happen

ElseIf Permission <> "Admin" Or Permission <> "Manager" Or Permission <> "User" Then

'code whatever you want to happen

End If

DoCmd.GoToRecord , , acNewRec

End Sub

The last two permissions are more for handling some issue in case the permissions field for users are blank or something else that is not approved.

From there you should create a hidden space on your login form that retrieves the permissions level if successfully logged in. This way when ever you call the permissions level check on your forms to allow or disallow certain features it does it from the frontend (user interface) rather than bogging down the network each time some permission needs checking. All permissions checks for permissible features should call on the login form text field holding the permissions value. Just be sure NOT to program in a close (i.e. DoCmd.Close) for the login form otherwise permission level will not be able to evaluate. It might be useful to call any fields you need to filter results of forms onto the login form but hide the text boxes by selecting each one and Property Sheet > Format > Visible = No and Property Sheet > Data > Locked = Yes. This way all lookup permission fields either user id, username, permissions, etcetera can be done.

Just drop this on your Login form VBA, where red is the name of text box set in Property Sheet > Other > Name:

Me.txtPermissions.Value = DLookup("Permissions", "tblStaff", "[UserName] = '" & Me.txtUserName & "'")

For example you may want to filter by departments as well to limit who can access certain types of records and to do that repeat the aforementioned and add an invisible text field on the login VBA like:

Me.txtDepartment.Value = DLookup("Department", "tblStaff", "[UserName] = '" & Me.txtUserName & "'")

[Gicu]

Hi all,

Please feel free to download my free utility FBA Custom Access Levels (http://forestbyte.com/ms-access-util...access-levels/). It is easy to setup (simply import all the objects in your database - you need to have the Show System Objects checked as the tables have "usys" prefixes" and they go in the back-end) and once implemented it gives you a very fine level of control for any form.

Cheers,
Vlad