Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS Access user level security - read only forms?

Posted on 2007-04-04
17
Medium Priority
?
2,527 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
[X]
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
  • 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 375 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

610 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