Only giving users data entry

[Evan Goldin]

Hi all,

I have just completed a database I've been working on for a long time, and
now it's time to set it up for use. However, I'm a little stuck in how I
should go about doing this. There are essentially groups of users of this
database: The employees and the boss. I only want the employees to be able to
enter data in a form I have created (I don't even want them to be able to see
previous records). I want to hide everthing else from them.

The boss, however, needs to have access to everything. And, if it's
possible, I'd like some kind of in-between capabilities -- where a supervisor
can review records but not modify the database.

It seems as though splitting the database would be the way to go, but won't
that still allow users to view queries, other forms, etc? Thanks for the help!

 

[Allen Browne]

Okay, there is quite a bit of work to set up user permissions.

One option is to secure the database, create groups of users, and assign
permissions to each group. That handles denying access to the data. You also
need to handle the interface, so users are not trying to get to aspects of
the software they don't have permission for. For example, if a command
button does an OpenForm on a table the user does not have read-permission
on, you want to set the command button's Visible or Enabled to False.

Since you have to handle the interface anyway, you might consider just doing
the interface code and not worrying about the security. This is not suitable
for truly sensitive data operated by nosy geeks, but it handles the typical
users and is simpler to maintain. Any table starting with the prefix USys is
considered a user-system table, and Access hides it by default.

So you create a table of permission levels named (say) USysPerLevel, with
records:
0 users
1 supervisors
2 db administrators

And a table of user permissions with fields:
- DocType Form or Report
- DocName Name of the form or report
- CtlName Normally blank. See below
- MinLevel Permission level required to view this object.
If CtlName is blank, the permission indicates whether the user can see the
form/report. If the ClName is not null, the permission applies to a
particular control on the form/report.

You can now write function that is called in the Open event of all forms and
reports. The function accepts the object being opened, and returns True or
False as to whether the user can open the form/report. If the code finds the
user has the rights to the object, but some controls should be hidden, it
loops through the controls on the form/report, and hide the control if its
Name matches a CtlName the user does not have permission for.

Now you also need to know the user. In some scenarios, you can just use a
command line argument on the shortcut of the supervisors and db admins, and
check Command(). Or you can set up a modal popup form that fires when the
mdb opens, get the user to log in, and then hide (don't close) the form so
you can discover the user later.

 

[Tim Ferguson]

I only want the
employees to be able to enter data in a form I have created (I don't
even want them to be able to see previous records). I want to hide
everthing else from them.

As Allen says, the definitive way to hide data from nosy geeks is
Access's own user-and-group level security. If you are happy that they
will do as they are told, however, you can exert a lot of control just by
giving them different front ends. For example, these users need an mdb
that has only data-entry forms set to data entry only, so they can't see
old records. Don't forget to allow them some way back, however, to
recover from and correct errors.

The boss, however, needs to have access to everything.

Well, read-only access actually. Don't put any forms in this mdb; just a
whole bunch of reports and queries for the things that they need.

And, if it's
possible, I'd like some kind of in-between capabilities -- where a
supervisor can review records but not modify the database.

Actually, I'd prolly give the supervisor the widest access of all because
someone has to go an remove all those garbage records that the trainees
put in by accident and change the n/d/y dates into d/m/y and so on and so
on...

It seems as though splitting the database would be the way to go,

Absolutely; but don't be limited by thinking you have to have just one
front end. Come to that, don't be limited by thinking you have to use
Access itself for a front end. Consider creating a bunch of user queries
in an Excel spreadsheet for the bosses -- VBA in Excel is exactly the
same as VBA in Access -- or giving a small number of your power users
access via MS Query and so on.

Hope that helps


Tim F