Anyone with basic knowledge of MS Access can modify the behavior of a database by removing or altering any of its components: tables, queries, forms, reports, macros, or modules. The easiest and most secure way to prevent this from happening is to save the application as an .accde database; this type of files prevents any modification to its components, and can only be executed.
In a client - server model the only drawback of this format is that when an automatic update of the application is required, the original .accdb file must be modified, convert it back to the format .accde, put it in a shared folder and resorting to an external component that replaces the application with the new version.
There is another alternative to protect the client application and allow the database administrator to update the application without having to resort to an external component that replaces the application file. However it is less secure since a programmer with the necessary knowledge can hack the security and modify the components of the application, therefore, this option is recommended when the application is distributed among users without advanced knowledge of programming as in the majority of cases.
Protecting the application involves determining the user profile and decide whether to hide or show the navigation pane, ribbons and menu bars as well as disable or activate special commands that run with special keys. For example: the database administrator should have the option of modifying objects, as opposed to a normal user who only should manipulate data.
The menu bar is hidden by: DoCmd.ShowToolbar "Ribbon", acToolbarNo, however, the other options require modifying the following properties of the startup of the database:
- NavPane Closed: hides or shows the navigation pane.
- AllowBuiltinToolbars: toolbars must always be allowed; otherwise Access will display Ribbon commands.
- AllowSpecialKeys: key combinations which allow showing the navigation pane and displaying the source code.
- AllowByPassKey: enables or disables the use of the SHIFT key during the startup of the database. This key allows the user to skip the execution of the AUTOEXEC macro.
- ShowDocumentTabs: hides or displays the labels of the forms. When you click with the right button of the mouse on a form tag, you can switch to design view and therefore modify the appearance and behavior of the form.
These properties are initialized before running the Autoexec macro, so it will be necessary to restart the application if the previous user's profile it is not equal to the current user profile. For example: If the administrator opens the client application and then another user with a standard profile opens the same application, a restart will be required so that the new properties come into effect and the user cannot modify the application.
Basically, the process of protecting the client application consists of:
- Hiding the menu bar when starting up the application.
- Determining the previous and current profile of the user: the user should provide its credentials in a logon screen.
- Protecting or unprotecting the client application by changing the startup properties according to the user profile database. User administrators can access all commands in Ms Access as opposed to standard users who cannot modify the behavior of the application.
- Restarting the application if the current user profile is not equal to the previous user's profile, because properties do not change until the application is restarted.
- Display the Ribbon for administrative users and to hide standard users.
