Locking a table

[Kundan]

If I don't want anybody to delete or change data from a table. How do I achieve it?

 

[theDBguy]

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]

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):

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]

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]

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]

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]

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!!!!!!!!!!!!