Best recommendation for permissions-management in Access 2007

[kharke]

I have a small Access 2007 .accdb file for which I would like to manage permissions. I'm less worried about security as I am about data-integrity. The database is located on a shared network, the access to which is managed by the campus IT. But some staff need more access to the data than others (and some need more restrictions than others). Also, I would like to protect from honest attempts to modify/change forms, queries, code and tables. Most users are not Access-proficient, but a few are more independent than others.

I've scanned this forum and read some stuff on how Access 2007 no longer provides user-level security for accdb files. But I haven't been able to decide which route to take:

1. Convert to MDB and implement ULS (what will I loose? will it make db management (particularly updating) more difficult?)
2. Simply create an accdbe and forget about ULS.
3. Try to program permission-management (can it be done with VBA?...I'm a VBA novice, but a quick-study).
4. Another option?

Please advise.
Thanks
Karen

 

[The_Doc_Man]

The approach I am taking depends on whether you have a formal domain environment. We do.

When you log in to your domain and open Access, and use the Environ("USERNAME") function from VBA, that gives you the name you used to log in. So your identification is then provided by the domain management.

I use a startup form that queries a table (hidden, of course) that shows my list of known users. In my case, I create a guest-level account for anyone who can get to me because the network guys have already done some permission magic to block folks from the wrong Windows Org.Unit. The startup form populates some global module variables from the user record. For a guest record, those values are the lowest level they can be.

The startup form is a switchboard that maximizes itself and hides the database window. I also use the methods to block folks from bypassing the startup form. If your role as stored in the user tables says you are a guest, you are read-only so cannot change anything. The forms you can bring up from the switchboard read the global settings available from the general module and run a sweep of all controls to lock them if you are a guest. If you are at a higher level of access, they don't lock everything. Makes for a few nasty little subroutines, but a loop of FOR EACH control-variable IN Me.Controls can do the trick, with a SELECT CASE control-variable.ControlType to guide just how you disable the control - or whether you need to. Like, who cares about labels being disabled, right? For that matter, if anything needs to be obscured, I have special-case code after the FOR EACH loop to separately make invisible any controls that a guest should not see.

The higher levels of access in my case are designed to be true supersets. If you are a guest, you are read only. If you are one of the staff members, you have write access to certain fields. If you are in the DBA group, you have write access to everything. That kind of subset.

This whole thing depends on the switchboard NEVER allowing you to see the DB window unless you are a DBA. So you make the switchboard Form_Open and Form_Load routines extremely picky.

 

[kharke]

This method may work for us...I have custom switchboards and I'm looking at other controls. I'm really peeved that MS got rid of ULS - that's like throwing the baby out with the bathwater. Many users of Access are aware of and could accept the weaknesses - the holes were just not that important at our level.

Thanks for the reply. I'll look into that kind of solution more.
Karen