Introduction - Access 2003.
When developing an application, even for personal use, it is important to design each object as if it will be used by someone else. Only then will we take a careful look at each control’s functionality, strengths, and limitations. Users may not interact with a table, form, or report in the way we envision. They might enter incorrect data, attempt unintended actions, or experiment with the design of controls and forms.
For example, consider a Date of Birth or Invoice Date field on a form. If the field allows any date value, users may accidentally enter invalid dates, such as a future date. To prevent such errors, we should enable built-in validation checks on the field, alert users to mistakes, and enforce the entry of valid values.
Data Field Validation Checks.
We can enforce this by setting the Validation Rule and Validation Text properties of the date field.
The Validation Rule property can be set to <Date() to accept only date values earlier than today, suitable for a Date-of-Birth field.
If you want to restrict entries to a reasonable range, for example, limiting ages to 100 years, you can use a rule like:
>=(DateAdd("yyyy",-100, Date())) And <Date()
This ensures that the entered date is not more than 100 years in the past and not a future date.
The Validation Text property can be set with a message such as:
"Future date is invalid and Age Limit is 100 years"
It is recommended to implement this directly on the Table field rather than just on the Form, as this ensures data integrity regardless of how the data is entered.
Preventing Changes to Important Objects.
Equally important is ensuring that users cannot modify critical objects such as Forms, Queries, Reports, Macros, and VBA code. This is where User-Level Security in Microsoft Access comes into play.
Note, however, that this security feature is available only in Access 2003 and earlier versions.
Keep Users' Freedom within Limits.
Another effective way to prevent users from straying into unintended areas is to create custom menus and toolbars for your application, while disabling all built-in menus and toolbars. This ensures that users cannot access object designs or other sensitive features through the default Access interface.
Once your customized menus and toolbars are in place, you can disable the built-in menus by removing the relevant check marks in the Access Options. This creates a controlled environment, allowing users to interact only with the parts of the application you intend them to use.
Caution: Take a backup of the database before making the following changes otherwise you may not be able to get back into the database for making design changes yourself later.
Select Office Button -> Access Options -> Current Database -> Ribbon and Toolbar Options
Remove check marks from the following options:
- Allow Full Menus
- Allow Default Shortcut Menus
- Allow Built-in Toolbars
After removing the check marks, you must close and reopen the database. Now, only the customized menus and toolbars you created will be available to the user.
However, your database objects—Forms, Reports, etc.—are still not fully protected from unauthorized changes. While users cannot right-click an object in the Navigation Pane to enter Design View, they can still access objects via the VBA Modules. For example, they might browse the Navigation Pane, select a Form or Report, and then choose the View Object option.
In the VBA Navigation Pane, Forms and Reports with associated Class Modules are visible to users. To limit access, you can hide these objects by right-clicking in the Navigation Pane and setting the Hide option.
For more comprehensive protection, you can also disable the Navigation Pane entirely by removing the check mark from Display Navigation Pane, in addition to disabling the built-in menus as explained earlier. This ensures that users cannot navigate to the module or object views at all.
Unless the user is very clever, you can consider your database objects reasonably safe—but not completely secure from a determined or knowledgeable individual. For instance, anyone familiar with keyboard shortcuts can open the VBA Editor by pressing ALT+F11. From there, the story unfolds on its own. Even if you hide the Navigation Pane within the VBA window, it can still be accessed via the Object Browser button on the toolbar.
Since User-Level Security is no longer available in Access 2007 and later versions, these are some of the methods you can use to implement security within an Access database.
Once you remove the full menus option, it becomes difficult to make design changes to Forms, Reports, or other objects. For this reason, it is highly recommended to take a backup before implementing these security measures.
Even during the design phase, make it a regular practice to create backups of your database. This ensures that if your database becomes corrupted or is accidentally deleted at any stage, your work is safe, and you do not risk losing all your progress.
Restoring Full Menus.
If you want to restore the full menus, there is a simple trick to regain access to the database and re-enable the Allow Full Menus option—without going through the Office Button menu directly.
Open the database.
Press ALT+F11 to display the VBA Window
Press CTRL+G to display the Debugging Window (Immediate Window).
Type the following command in the Debug Window and press Enter Key:
CurrentDb.Properties("AllowFullMenus").Value = TrueClose and reopen the database.
Now, you can approach Access Options through the Office Button (top left corner) and make changes to Options there.
Earlier Post Link References:
No comments:
Post a Comment
Comments subject to moderation before publishing.