Avatar of rrhandle8
rrhandle8
Flag for United States of America asked on

User permissions in Access 2010

How do you create permissions in Access so user "A" and edit data, but user "B" can only view data?
Microsoft Access

Avatar of undefined
Last Comment
PatHartman

8/22/2022 - Mon
Jim Dettman (EE MVE)

Nothing is built into Access to handle that.

 Some add a security system in the form of a couple of tables.  One for forms, with a permission flag for Open, Add, Edit, and Delete, and another for controls, with flags for enabling/disabling the controls.

The other way to do it is use a different data store rather than the ACE database engine.  Something like SQL Server, which allows for security on tables and views to be set by user and/or group.

 That doesn't really touch objects though, so most build something in Access.

Jim.
ASKER CERTIFIED SOLUTION
Scott McDaniel (EE MVE )

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
rrhandle8

ASKER
Thanks guys.  I wonder if I can make this simple.  There are only two user types: full permissions and read only.  Based on the user's login information could I make the database readonly?  Or maybe use a second database just for logging in, and open the database as readonly for particular users?
Scott McDaniel (EE MVE )

You can set all of your Forms to be ReadOnly when opening them like this:

Me.AllowEdits = False
Me.AllowAddition = False
Me.AllowDeletes = False

This would allow the user to view data, but not modify it.

Of course, if you're allowing users access to your Tables or Queries, then you have no control over that.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
rrhandle8

ASKER
Scott, possible to loop through all forms when database opens, and set the properties you stated?
Scott McDaniel (EE MVE )

You just write them in the Open or Load event of the Form, based on the user login. In most cases, you'd have a hidden form somewhere that would store your "User Level", and your forms could refer to that:

If Forms("Hidden_Form").txtUserLevel > 1 Then
  Me.AllowEdits = True
  Me.AllowDeletes = True
  ect ect
Else
  Me.AllowEdits = False
  etc etc
End If

Or you can do this:

Me.AllowEdits = Forms("Hidden_Form").txtUserLevel > 1

This would set AllowEdits to TRUE if the Userlevel is greater than 1, otherwise it would set it to FALSE.
SOLUTION
PatHartman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.