Solved

MS Access user level security - read only forms?

Posted on 2007-04-04
17
2,517 Views
Last Modified: 2013-11-28
I'm trying to use user-level security in an MS Access 2003 database to disallow access to some forms (this I can do without much trouble), and allow read-only access to others so that the user can see data but not edit the value of any control on the form. Is this read-only access restriction possible?
0
Comment
Question by:KnowledgeWare
  • 7
  • 5
  • 4
  • +1
17 Comments
 
LVL 75
ID: 18853199
Yes.

mx
0
 

Author Comment

by:KnowledgeWare
ID: 18853215
Right. Good. How?
0
 
LVL 13

Expert Comment

by:adraughn
ID: 18853227
Set the AllowEdits property to False. While you're at it, also check
out AllowDeletions and AllowAdditions.

adria
0
 
LVL 75
ID: 18853265
Are you talking about when using ULS ?

mx
0
 
LVL 75
ID: 18853275
If not, the try adraughn's suggestion, noting however that is easily circumvented.

Sorry ... did mean such a short answer in my first post ... someone came in my office!

mx
0
 
LVL 13

Expert Comment

by:adraughn
ID: 18853320
If you are going to use user level security, you can assign the user to a group and make that group read only.

If you need help let us know.

adria
0
 

Author Comment

by:KnowledgeWare
ID: 18853413
Yeah, I'm trying to use ULS. Setting the Form's properties would involve a whole custom security setup, would it not? I suppose it wouldn't be that hard to build, but I'd rather not do it.

Ok, say I want to have Form A be completely blocked, and Form B to be read only, when JoeUser is logged in. I've set up the JoeUser account, and put him in the JoeUserGroup. I set the Users group to have no permissions at all, on any form, because I want to be sure that the JoeUserGroup is controlling everything. This group leaves form Form A without any permissions (specifically NOT "Open/Run"), so now Form A is blocked. I select Form B in the list, and review my options... they are:

-Open/Run
-Read Design
-Modify Design
-Administer

None of these produce the result I'm looking for (user can open the form, but not enter any data into it). 'Read Design' prevents the user from changing the design of the form, but not from changing the data it displays.

The options for controlling access to data (Read, Update, Insert, Delete data) are all disabled when I'm working with a form. I can set those on a table or query, but that doesn't help as I really only want to block access to the form, not the table (which may be used by other forms).
0
 
LVL 13

Expert Comment

by:adraughn
ID: 18853492
you could use something like this on the form's on open event:

If IsUserIngroup(JoeUserGroup) Then
Do something
Else
So Something Else
End If
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:KnowledgeWare
ID: 18853532
Yeah, I was just going over that possibility myself. Just need to figure out / be told (oh please :) ), what to call in the code to get the logged on user and his group(s).
0
 
LVL 13

Expert Comment

by:adraughn
ID: 18853590
you can use this:

Function IsUserInGroup(GroupName As String) As Boolean
Dim ws As DAO.Workspace
Dim usr As DAO.User
Dim intLoop As Integer
IsUserInGroup = False
Set ws = DBEngine.Workspaces(0)
Set usr = ws.Users(CurrentUser())
For intLoop = 0 To usr.Groups.Count - 1
If StrComp(usr.Groups(intLoop).Name, GroupName, vbTextCompare) = 0 Then
IsUserInGroup = True
Exit For
End If
Next intLoop
End Function
0
 
LVL 75
ID: 18853665
'JoeUserGroup'

Hey ... don't abuse my name, LOL :-)

mx
0
 
LVL 13

Expert Comment

by:adraughn
ID: 18853685
don't listen to him... joe's are the ones you have to look out for....

:)

0
 
LVL 13

Expert Comment

by:adraughn
ID: 18853721
If IsUserInGroup(CurrentUser(), "JoeUserGroup") Then
    'lock the form
Else
    'don't lock the form
End If
0
 

Author Comment

by:KnowledgeWare
ID: 18853733
That looks good, thanks. I'll give it a go. :)
0
 

Author Comment

by:KnowledgeWare
ID: 18853817
Oh, and sorry Joe... I'll use a WilburUserGroup next time.
0
 
LVL 13

Expert Comment

by:adraughn
ID: 18853818
haha
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 18855110
A much quicker way to determine Group membership:

Public Function IsUserInGroup(GroupName As String, UserName As String) As Boolean

Dim usr As DAO.User
Dim wsp As DAO.Workspace

On Error Resume Next
Set wsp = DBEngine.Workspaces(0)
Set usr = wsp.Groups(GroupName).Users(UserName)

IsUserInGroup = Err.Number <> 3265

End Function

Use it like this:

IsUserInGroup "Admins", CurrentUser

would return True if the currentuser were a member of the Admins group ...

There is really no reason to iterate the group members; DAO will always throw the 3265 error if you pass in the wrong group or a user who isn't a member.

The Access User Level Security FAQ has some good code examples of how to determine User and Group membership ... download it from the link of this page: http://www.jmwild.com/Accesssecurity.htm

Also, please make sure that you fully understand ULS and how it interacts with your database. The link to Jack MacDonald's at the page referenced above is (IMO) required reading for anyone wishing to use ULS in their app.

0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now