Locking a table (1 Viewer)

Kundan

Registered User
Joined
Mar 23, 2019
Messages
78
If I don't want anybody to delete or change data from a table. How do I achieve it?
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,684
Hi. You can't prevent this 100%. The recommended approach is to use forms for data entry. Try not to give users direct access to the tables.
 

zeroaccess

Member
Joined
Jan 30, 2020
Messages
119
In addition to the advice for only using forms, you'll want to hide everything else.

Go into File > Options > Current Database

Uncheck:
Display Navigation Pane
Allow Full Menus
Allow Default Shortcut Menus

Put the following in VBA for the Form Load event of your startup form (hopefully you have a startup form):

Code:
Private Sub Form_Load()
    DoCmd.ShowToolbar "Ribbon", acToolbarNo
End Sub

When you need to work on the database, use the bypass key on startup.
 

The_Doc_Man

Happy Retired Curmudgeon
Joined
Feb 28, 2001
Messages
15,558
The answer depends on whether your users are garden-variety or exotic users.

The garden-variety users can be blocked as theDBguy suggests, by never showing your users anything but forms. Then on any form where a critical delete COULD happen, you assure that the form's AllowDelete and AllowEdit property is set to No. You also would want to perhaps have user-role sensitivity in that you have people log in to the DB by a username. In your app, have a user table that identifies a role or a special key or something to define what each user can do. For the super user, don't set the form's AllowXXXX options to No.

If you are in a domain environment, a simple (if not perfectly reliable) way is the Environ("Username") function. It can be spoofed, but you trust your users or believe they aren't very savvy, it is a decent way to find out your user's login name. (I.e. make it match the domain login name.)

For the more exotic or determined or bull-headed user, make good backups so you can manage data recovery because a really determined malicious user will find or even create ways to be malicious.
 

Kundan

Registered User
Joined
Mar 23, 2019
Messages
78
The answer depends on whether your users are garden-variety or exotic users.

The garden-variety users can be blocked as theDBguy suggests, by never showing your users anything but forms. Then on any form where a critical delete COULD happen, you assure that the form's AllowDelete and AllowEdit property is set to No. You also would want to perhaps have user-role sensitivity in that you have people log in to the DB by a username. In your app, have a user table that identifies a role or a special key or something to define what each user can do. For the super user, don't set the form's AllowXXXX options to No.

If you are in a domain environment, a simple (if not perfectly reliable) way is the Environ("Username") function. It can be spoofed, but you trust your users or believe they aren't very savvy, it is a decent way to find out your user's login name. (I.e. make it match the domain login name.)

For the more exotic or determined or bull-headed user, make good backups so you can manage data recovery because a really determined malicious user will find or even create ways to be malicious.
If I give access only to forms and remove the ribbon. How can I incorporate Filter by form and other options required for searching. Also printing of records becomes a bit complicated.
 

The_Doc_Man

Happy Retired Curmudgeon
Joined
Feb 28, 2001
Messages
15,558
Your question is valid, but the difficulty of doing something is not an excuse for exposing the data to unwanted alteration. This becomes a question of priority. How important is it to prevent alteration? (That is a rhetorical question.) How much work are you willing to do to protect the data?

The question might be resolved by having two back-end files and open one of the back-ends read-only. The other one would keep records of whatever you were doing. Here, though, the problem is whether any of those users who are normally read-only would EVER become capable of full read/write operations? And are you running in a domain-based environment or a "free-for-all" (non-domain) environment?

A long-term permanent solution for a domain environment is easy - put the restricted users in a group that doesn't have write permissions to the back-end file. Put unrestricted users in a group with MODIFY permissions. However, if (a) users can dynamically change rights or (b) you don't have a domain environment where you could create groups, then controlling user access becomes more of a problem.
 

Kundan

Registered User
Joined
Mar 23, 2019
Messages
78
Your question is valid, but the difficulty of doing something is not an excuse for exposing the data to unwanted alteration. This becomes a question of priority. How important is it to prevent alteration? (That is a rhetorical question.) How much work are you willing to do to protect the data?

The question might be resolved by having two back-end files and open one of the back-ends read-only. The other one would keep records of whatever you were doing. Here, though, the problem is whether any of those users who are normally read-only would EVER become capable of full read/write operations? And are you running in a domain-based environment or a "free-for-all" (non-domain) environment?

A long-term permanent solution for a domain environment is easy - put the restricted users in a group that doesn't have write permissions to the back-end file. Put unrestricted users in a group with MODIFY permissions. However, if (a) users can dynamically change rights or (b) you don't have a domain environment where you could create groups, then controlling user access becomes more of a problem.
Thanks. I will try out your suggestions. GOD BLESS YOU!!!!!!!!!!!!
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom