Versions 2.21 / 2.5           Approx 3.5 MB (zipped)                 First Published 27 Mar 2024                 Last Updated 28 Apr 2024


Preamble

This is the second part of a series on protecting the design and data in your database objects.
The first article, Protect Data in Tables and Queries demonstrated how tables and queries can be made READ ONLY whilst allowing users to work normally with forms.

As previously stated, end users should NEVER have direct access to tables or queries via the navigation pane. All user interaction should be done using forms.

In my databases, unless otherwise specified by the client, the navigation pane and ribbon are hidden and other basic security measures applied.
This is usually enough to prevent unintentional damage to database objects.

However, users who know enough to be 'dangerous' will be able to circumvent simple Access security and, in some cases, cause problems due to 'meddling'.
In my experience, significant damage can be caused by interested users who just want to explore how databases work with no malicious intent.

It is standard practice to split databases with the Access front end distributed as an ACCDE file. Doing this will protect your code and the design of forms / reports.
Also, the design of linked backend tables cannot be altered from the frontend database.

However, that will not prevent users editing or deleting records in tables or queries or exporting the data to e.g. Excel or text files.
Nor will it prevent users changing the design of any tables or queries in the front end database.

This article describes ways of helping to protect all database objects from unintentional tampering by users.



Downloads

Click to download - ACCDB or 32/64-bit ACCDE (all files are approx 3.5 MB and zipped):

          LockDownDatabaseObjects_v2.21       ACCDB file

          LockDownDatabaseObjects_v2.21       32-bit ACCDE file

          LockDownDatabaseObjects_v2.21       64-bit ACCDE file

The ACCDB file has deliberately not been fully locked down so you can explore how it works. There are additional restrictions in the ACCDE versions.



Example App

The example app opens to a startup form, frmStart, which outlines what the app does.


The app has almost identical objects to the example app in the previous article.

In any standard Access app, the right click context menus give users many options to choose from.
For example, using an English language version of Access 365:

Table

Query

However, in this example app, right clicking on any table or query displays a reduced context menu with many items hidden and all other items disabled.

Table

Query

NOTE: See important comments below about different Access versions and Office languages other than English.

For forms, the only available option is Open and for reports Open / Print / Print Preview.

Form

Report

Macros and modules are all hidden in the navigation pane but, if they are made visible, the context menus are also disabled.

Macro

Module

The above restrictions mean that users cannot view or edit the design of objects, nor export them. This applies to both ACCDB and ACCDE files.

NOTE:
In the ACCDE versions of the example app, the right click context menus have been completely removed for all database objects

It is possible to open any database object by double clicking in the navigation pane.
However, whilst forms and reports open normally, tables and queries will close again in about half a second.
The code is able to distinguish between different objects with the same name and treat each object type correctly.

Other Restrictions:
a)   Objects cannot be accessed from the ribbon as those items are disabled
b)   The status bar has been removed preventing changes to design view
c)   The Visual Basic Editor is highly locked down. If you attempt to open it directly or via the Immediate window, it will immediately close
d)   The shift bypass has been disabled.




How does the Example App work?

Once again, the app makes use of the fact that when viewing the data in tables and queries, you are actually viewing a datasheet form.
This means that certain events / actions / properties can be controlled using code.

The code checks for any datasheet (table or query) which is currently open and active.
It then closes the table / query immediately after it is opened.
The following code should be placed in a standard module e.g. modSecurity

CODE:

Public Function BlockViewTableQuery()

Dim frm As Object, I As Integer

'determine if object is within scope (table / query)
On Error Resume Next

      'restrict use of VBE
      CloseAllVBEWindows

      'create an error to exclude datasheet forms
      If Err = 0 Then Debug.Print Screen.ActiveDatasheet.Properties("linkchildfields").Value

      'set frm to active datasheet table/query if no error triggered above
      If Err = 0 Then Set frm = Screen.ActiveDatasheet Else Exit Function

      If Application.CurrentObjectName = frm.Name Then
            DoCmd.Close acTable, frm.Name
            DoCmd.Close acQuery, frm.Name
      End If

End Function

The code first closes any VBE windows that may have been opened. See my article Close All VBE Windows for details of this function

As in the earlier version of this example app, the code is called from a hidden form (frmHide) which is loaded when the app starts.
A timer event is used with a short interval to check for the currently active datasheet and close it

Private Sub Form_Timer()/span>
      Call SetTableQueryReadOnly/span>
End Sub/span>

Once again I have used an interval of 500 milliseconds (0.5 s) but this can be altered as required.

Many thanks to fellow Access developers, Chris Arnold and Xevi Batlle, both of whom tested earlier versions of this app and suggested several useful improvements.

The significantly reduced context menus used in the ACCDB version of this app were achieved in two ways:

a)   The code DoCmd.LockNavigationPane True is used to disable many context menu items:

Tables - Navigation Pane Unlocked

Tables - Navigation Pane Locked

However, it does not limit access to the Design View, Import, Export, Copy or Properties menus. For the purposes of the example app, I removed these by hiding various command bar menu items

To do so, I made extensive use of the free Advanced Shortcut Tool add-in by former Access MVP, Dale Fye.


NOTE:
Dale discussed the use of this excellent tool in an online presentation to the Access Europe User Group on Wed 3 April. A video of that session is now avilable on YouTube.
For more details, see Working with Command bars and the Access Shortcut Tool

The code used is all contained in a function LockNavPane in modSecurity

CODE:

Public Function LockNavPane()

On Error Resume Next

      DoCmd.LockNavigationPane True

      'queries / macros
      With CommandBars("Navigation Pane query or macro Pop-up")
            .Controls("&Open").Visible = False
            .Controls("&Run").Visible = False
            .Controls("&Design View").Visible = False
            .Controls("SQL View").Visible = False
            .Controls("De&lete").Visible = False
            .Controls("&Export").Visible = False
            .Controls("&Copy").Visible = False
            .Controls("O&bject Properties").Visible = False
      End With

      'tables
      With CommandBars("Navigation Pane List Pop-up")
            .Controls("&Open").Visible = False
            .Controls("&Design View").Visible = False
            .Controls("I&mport").Visible = False
            .Controls("De&lete").Visible = False
            .Controls("&Export").Visible = False
            .Controls("&Copy").Visible = False
            .Controls("Ta&ble Properties").Visible = False
      End With

      'modules
      With CommandBars("Navigation Pane Object Pop-up")
            .Controls("&Design View").Visible = False
            .Controls("&Export").Visible = False
            .Controls("&Copy").Visible = False
            .Controls("O&bject Properties").Visible = False
      End With

      'forms / reports
      With CommandBars("Navigation Pane View Pop-up")
            .Controls("&Design View").Visible = False
            .Controls("&Export").Visible = False
            .Controls("&Copy").Visible = False
            .Controls("V&iew Properties").Visible = False
      End With

      With CommandBars("Form View Popup")
            .Controls("La&yout View").Visible = False
            .Controls("&Design View").Visible = False
            .Controls("Cu&t").Visible = False
            .Controls("&Copy").Visible = False
            .Controls("&Paste").Visible = False
      End With

      'form /report title bar - not named so use reference number
      'number may vary depending on Access version / workstation
      With CommandBars(32)
            'this line errors at startup - bypassed using error handling
            .Controls("&Design View").Visible = False
      End With

      With CommandBars(226)
            'this line errors at startup - bypassed using error handling
            .Controls("&Design View").Visible = False
      End With

End Function

This code runs from an Autoexec macro at start up. Similar code UnlockNavPane runs from the Form_Unload event of hidden form frmHide and is used to reverse all these changes immediately before the app is closed

IMPORTANT:
There are several significant issues to working with command bars to disable or remove menu items:

1.   Although most command bars are named, a few do not have a name e.g. the form /report title bar.
      In such cases, I reference the command bar index number supplied by the Advanced Shortcut Tool add-in

2.   The command bar index number may vary depending on the workstation and Access version. Check this carefully.

3.   Command bar names are independent of the Office language used. However, command bar menu item names vary depending on the Office language used.
      The menu item names in the above code will need to be amended for other Office languages such as Spanish.

Overall, there are too many issues for this to be a reliable approach for restricting context menus.

However, there is a much simpler approach which will work reliably. In Access Options, untick Allow Default Shortcut Menus.
Also untick Allow Full Menus to prevent anyone creating new database objects.


NOTE:
If you want to create an ACCDE file, make sure you do this immediately after applying these changes.
If you close the database first, you will no longer have access to the File...Save As ... ACCDE menu in Backstage view

Many experienced Access users (including hackers) will know how to reverse such changes externally. I do not intend to explain how to do so in this article.

For the purpose of this example app, I apply several of these restrictions at startup using a function ModifyStartupProps run from the Autoexec macro.
Amongst other things, this also disables the Shift bypass

Function ModifyStartUpProps()

'Call this from an autoexec macro or startup form

On Error GoTo Err_Handler

      'Delete existing start up properties
      DeleteStartupProps "AllowFullMenus"
      DeleteStartupProps "StartUpShowStatusBar"
      DeleteStartupProps "AllowBuiltInToolbars"
      DeleteStartupProps "AllowShortcutMenus"
      DeleteStartupProps "AllowToolbarChanges"
      DeleteStartupProps "AllowSpecialKeys"
      DeleteStartupProps "StartUpShowDBWindow"
      DeleteStartupProps "AllowBypassKey"

      'set various properties to False in ACCDB and/or ACCDE files
      StartUpProps "AllowBypassKey", False, True
      StartUpProps "StartUpShowStatusBar", False, True
      StartUpProps "AllowSpecialKeys", False, True
      'next line hides navigation pane
      'StartUpProps "StartUpShowDBWindow", False, True

      If IsACCDE Then
            StartUpProps "AllowFullMenus", False, True
            StartUpProps "AllowBuiltInToolbars", False, True
            StartUpProps "AllowShortcutMenus", False, True
            StartUpProps "AllowToolbarChanges", False, True
      End If

Exit_Handler:
      Exit Function

Err_Handler:
      MsgBox "Error " & Err.Number & " in ModifyStartUpProps procedure : " & Err.Description, vbOKOnly + vbCritical
      Resume Exit_Handler

End Function

'================================
Function IsACCDE() As Boolean

      ' Initiate
      IsACCDE = False

      ' The boolean MDE property exists only in compiled databases (.mde, .accde)
      ' Ignore error (and stay "False") if not present
      On Error Resume Next
      IsACCDE = CurrentDb.Properties("MDE") = "T"

End Function

I have deliberately left sufficient items alone to allow you to unlock the database and examine the code.

You will need to restart the applcation TWICE for any changes to startup restrictions to be implemented.
The first restart will modify the property and this will then take effect when the app is next opened.

Of course, whilst the database is locked, you cannot get access to the VBE to view or modify the code!
However I have provided a 'back-door' using a hidden Autokeys macro


To unlock the database, click Ctrl+Shift+U. This runs a function UnlockApp
To lock the database again, click Ctrl+Shift+L. This runs another function LockApp



Update:   Version 2.5     9 Apr 2024

Following some feedback, I have decided to release an updated version which removes the context menus completely in both ACCDB & ACCDE files.

Click to download:       LockDownDatabaseObjects_v2.5       ACCDB file - approx 3.5 MB (zipped)

Changes made:
1.   Removed functions LockNavPane and UnlockNavPane from modSecurity
2.   Removed LockNavPane from the LockApp function in modSecurity and the Autoexec macro.
3.   Removed UnlockNavPane from the UnlockApp function in modSecurity and the Form_Unload event of frmHide
4.   Added the line DoCmd.LockNavigationPane True to the LockApp function in modSecurity.
      Added DoCmd.LockNavigationPane False to the UnLockApp function in modSecurity

Public Function UnlockApp()
      DoCmd.LockNavigationPane False
      DoCmd.Close acForm, "frmHide"
End Function

'=====================================

Public Function LockApp()
      DoCmd.LockNavigationPane True
      DoCmd.OpenForm "frmHide", , , , , acHidden
End Function

5.   Disabled the If IsACCDE Then and End If lines in ModifyStartUpProps in modSecurity

' to unlock the ACCDB file, enable the If/End If lines below and restart the app TWICE
' to lock the app again, disable these two lines and again restart the app TWICE
If IsACCDE Then ' enable to unlock
      StartUpProps "AllowFullMenus", False, True
      StartUpProps "AllowBuiltInToolbars", False, True
      StartUpProps "AllowShortcutMenus", False, True
      StartUpProps "AllowToolbarChanges", False, True
End If ' enable to unlock

This overcomes all issues related to Office version differences and languages but locking/unlocking the app takes a bit more effort for the developer!

To unlock the app:
a)   Press Ctrl+Shift+U
b)   Open the VBE and navigate to ModifyStartUpProps in modSecurity
      ENABLE the If / End If lines shown above and Save. Restart the app TWICE for the changes to take effect.

      You will then have full access to all context menus for all objects

To lock the app again:
a)   Press Ctrl+Shift+U
b)   Open the VBE and navigate to ModifyStartUpProps in modSecurity
      DISABLE the If / End If lines shown above and Save. Restart the app TWICE for the changes to take effect.

NOTE:
The two keyboard shortcuts will continue to work in ACCDE files and allow access to the VBE
However, as the code will be inaccessible in an ACCDE, end users will not be able to unlock the app

If preferred, you can delete or rename the Autokeys macro before creating an ACCDE file



Video

UPDATE 28 Apr 2024
I have just released a YouTube video (8:20) with a detailed explanation of how this app works.
You can watch the Lock Down Database Objects video on my Isladogs YouTube channel or you can click below:



If you liked the video, please subscribe to my Isladogs on Access channel on YouTube. Thanks.



Summary

This approach is intended to deter most users from unwanted modification of Access objects and data. It is NOT designed to block knowledgeable and determined hackers.

This example app goes well beyond the built-in restrictions provided with ACCDE files.

However, if you want to lock down your application even more, you can apply other more stringent security measures.
See my 3-part article Improve Security in Access Databases



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 28 Apr 2024

Return to Example Databases Page

Return to Top