Locking a table (1 Viewer)

Kundan

Registered User.
Local time
Today, 08:11
Joined
Mar 23, 2019
Messages
118
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
Staff member
Local time
Today, 08:11
Joined
Oct 29, 2018
Messages
21,358
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

Active member
Local time
Today, 10:11
Joined
Jan 30, 2020
Messages
671
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

Immoderate Moderator
Staff member
Local time
Today, 10:11
Joined
Feb 28, 2001
Messages
26,999
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.
Local time
Today, 08:11
Joined
Mar 23, 2019
Messages
118
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

Immoderate Moderator
Staff member
Local time
Today, 10:11
Joined
Feb 28, 2001
Messages
26,999
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.
Local time
Today, 08:11
Joined
Mar 23, 2019
Messages
118
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

Top Bottom