General discussion

Locked

Access 97 - database security

By kpike2 ·
I know how to set sign-on's for an Access 97 database and create some permissions. What I need to know: Is there a way to restrict a user from one column, but give them access to the rest of the record. I know how to restrict from updates, but that restricts the entire record not certain columns of the record. EXAMPLE: I have a database that has a column for patient name, x-ray receive date and x-ray return date. I need to restrict any changes to the patient name and x-ray received date, but get access to change the x-ray return date. Is there anyone who know how to accomplish this? Thanks! -KPike-

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access 97 - database security

by sl-campbell In reply to Access 97 - database secu ...

If you allow your users to change the data ONLY via Forms, then you can lock or dis-able the fields/columns on the form so that the user can only view, but not change the data for that field/column.

Other than that, if a user can open a table in the database window and edit some on the fields, then they can edit all of the fields. Again, the idea is to allow users to be-able to edit, (and poss. view), data only through forms.

Collapse -

Access 97 - database security

by sl-campbell In reply to Access 97 - database secu ...

Just to add one more idea:

In my larger apps, (VB apps - not Access), I have tables in a second MDB or database that defines users and groups and a seperate Exe that opens this MDB and allows the administrator to assign Read/Write/Update permissions for every forms in my app. The tables in this second MDB could as well be placed in the main MDB and other forms added to do the same as the Exe which I have that assigns permissions.
With Access, one could do the same, or, if security is set up via Workgroups you could use the Groups and Users for this. Of course, that is the idea of setting up some type of security. But when assigning permissions with Workgroups, you are restricted to form/table level permissions.

I have groups of users that may be allowed to open and edit data on a form, BUT, different from other groups of users with the like permissions, have certain specific permissions revoked such as clicking on a button to print a report or open a sub form, or edit a certain field.
The groups that the users belong to, with permissions given, (or revoked), must of course be identified in code when the app. starts. Then, if they are allowed to open a form you can add code in the form open event that restricts a certaingroup from doing something, (opening a sub form, seeing a field, changing data on a certain field, or whatever). Then, you do not need to create multiple instances of your forms for different users. You only need to create different groups, which have different permissions.

You could go a step further in the case where Workgroups are used, and add two new tables with-in your database that list these groups, (or users - I suggest using only groups to assign permissions), and what special ADDITIONAL permissions need to be given or revoked, (you could also hard code it).

Collapse -

Access 97 - database security

by kpike2 In reply to Access 97 - database secu ...

Poster rated this answer

Collapse -

Access 97 - database security

by Joseph Moore In reply to Access 97 - database secu ...

I agree with the previous answer. I set up an Access97 db for my wife (she does billing for an allergist), and she needed a database for some of her accounts. One one form, she enters in cust# and name; on another form (the one she uses more often),the cust# and name are visible, as well as some more fields (charges, date of services, etc). This second form on the cust# and custname fields, I locked them.
Right-click on each field -> Properties -> Data tab. The option Locked, set to Yes. Savethe form.
Now, open it. The field can be seen, and as the records change, the data changes accordingly. You can even hightlight the data, but you CAN'T change it! This way, the data can't be accidentally changed, thereby messing things up.

Having users use Forms instead of wrinting to the tables directly is exactly why there are forms in Access.

Hope this helps.

Collapse -

Access 97 - database security

by kpike2 In reply to Access 97 - database secu ...

Poster rated this answer

Collapse -

Access 97 - database security

by Bob Sellman In reply to Access 97 - database secu ...

I'm going to assume that you already have access to the data set up to only be through forms. The trick is to set up a different application for each type of users.

If each user logs into the network, then you can have different directories for each type of user. The version of the application will be different in each directory, although they will all link to the same back end database (where the tables are located). With this approach you can use network security to restrict access to the each front end directory. Write one big application with different forms for each type of user where needed and just change the selection of the initial form opened in each version.

A second method, which is needed when everybody will be connected with the same login to the network (or if you are running the application as a stand alone system), is based on the actual user login to your application. You can take the same approach with different forms set up where the functions and data access need to be different. When the user logs in, determine the type of user and open the appropriate starting form (which will connect only to the forms that the user should be able to use). Or, on your forms set up all fields that my need to be locked as locked when you define the form. When each form is opened, check the type of user (which could be saved in a public variable when the user logs in) and enable the appropriate fields if the user should be able to change them.

If you are using Access security, having separate forms means that you don't give the restricted users permission to use the versions of forms that allow the user the change the fields in question.

Additional ideas to improve security are:

1. Create an MDE version of the app.
2. Make the database window unavailable (go to Tools, Startup on the menu).

3. Hide tables

Collapse -

Access 97 - database security

by kpike2 In reply to Access 97 - database secu ...

Poster rated this answer

Collapse -

Access 97 - database security

by kpike2 In reply to Access 97 - database secu ...

This question was closed by the author

Back to Software Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums