Solved

MS Access user level security - read only forms?

Posted on 2007-04-04
17
2,519 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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
 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

773 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