Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MS Access user level security - read only forms?

Posted on 2007-04-04
17
2,521 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

809 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