Version 1.7                     First Published 26 Sept 2020                     Last Updated 8 June 2023                     Difficulty Level : Moderate

Section Links:
        Use Runtime
        Hide Tables
        Hide Navigation Pane
        Disable Access Options
        Remove Ribbon Items
        Disable Shift Bypass
        Use Strong Password
        Split Database
        Use ACCDE FE
        Security using Code
        Encrypt Your Data
        Block Imported Autoexec Macro
        Download
        Updates / Acknowledgements
        Feedback



The first part of this article outlined different ways of making your databases as secure as possible.

In this second part, I will explain how many of these methods are done using an example application to illustrate the approaches that can be used

The third part of this article discusses a wish list of changes I would like to see added to ACCDE databases to further improve security.



IMPORTANT:
Many changes will only take effect after closing and reopening your database.
This is important as it will allow you to apply several security features at the same time.

If you follow all the steps described, you will also be unable to modify your own application
ALWAYS ensure you have at least ONE copy of your file that is NOT locked down (preferably several copies)

The example application supplied with this article is initially UNLOCKED with full functionality available.

UNLOCKED – Ribbon / Nav pane visible


As you begin to lock this down, some functionality will be removed (either due to that change or using code).

For example, you will no longer be able to view tables & module code from the main form when the application is partly or fully LOCKED whether this is done
by hiding the navigation pane/ hiding the ribbon/converting to ACCDE etc.

LOCKED - with form visible ONLY


All code required to make these changes is available in the supplied example application

NOTE:
All screenshots are from Access 365 unless stated otherwise. Some items may look different in other versions



1.   Use a Runtime version                                                                                                 Return To Top

      If your users have a runtime version of Access, much of the functionality is disabled


      In runtime mode, the navigation pane is removed and right clicking is disabled
      Users have no means of viewing/editing/deleting database objects.
      All interaction is therefore done using forms which is exactly what you want to achieve.

      Furthermore, the entire ribbon is removed apart from the File menu and that only has limited options


      In this setup, Privacy Options does not open up any method by which users can obtain additional 'privileges'


      It is also possible to emulate runtime mode by changing the file suffix from ACCDB to ACCDR.
      Doing so should provide sufficient security to prevent tampering by most users.

      However, users just need to rename the file with the original ACCDB suffix to regain full functionality
      For that reason, runtime mode is NOT a reliable security measure on its own.



2.   Hide all tables                                                                                                             Return To Top

      In the navigation pane, right click on a table then click Table Properties. Tick Hidden in the properties dialog


      This can be done for each table in turn or you can use the HideAllTables procedure in the modShowHide module.
      Hidden tables will no longer appear in the navigation pane
      However, they can be made visible at any time if users click Show Hidden Objects in Navigation Options.

      It is also possible to make tables 'deep hidden' so they will NEVER appear in the navigation pane.
      Access also uses that feature for selected system tables.

      However, for security reasons, I am deliberately NOT going to explain how to do that in this article.

      In the example application, there is normally only 1 table (tblDummy) visible in the navigation pane.
      However, in fact, there are a total of 30 tables including 3 hidden, 1 deep hidden and 25 system tables of different types in this application.

      System tables can be made visible by ticking Show System Objects in Navigation Options.
      However, deep hidden tables are still not shown.

      The UNLOCKED version of the example app supplied includes a feature allowing you to view the different types of table


      Click the Tables tab on the form then select a table type from the option group on the left.
      A list of tables appears. Click the Print button to view the table list as a report


      Alternatively, double click a table in the list to view it (READ ONLY)

      IMPORTANT:
      System tables are used by Access to make databases function correctly.

      Some system tables can be viewed & a few can be edited
      But that doesn't mean you should do so . . . UNLESS YOU ARE ABSOLUTELY SURE WHAT YOU ARE DOING
      Altering one system table may have 'knock on' effects on other system tables

      Incorrectly editing system tables may corrupt your database or prevent you opening it

      If you click on any of the deep hidden tables, you will see a message similar to this:


      'Deep hidden' tables cannot be viewed by any standard method

      The UNLOCKED version of the example app supplied also allows you to view the module code


      Click the Print button to view the module list as a report




3.   Hide the navigation pane                                                                                             Return To Top

      A more useful approach is to hide the navigation pane completely
      Untick Display Navigation Pane in Access Options


      Alternatively, use the HideNavigationPane function in module modNavPaneTaskbar.
      A similar ShowNavigationPane function is also provided.
      Both functions are accessible from the Navigation Pane or the Modules tab on the form.

      If you wish to remove the navigation pane ‘permanently’, do ONE of the following:

a)   Add the line HideNavigationPane to the Form_Load event of the startup form in your application
      This code exists but has been disabled in my example app

b)   Create a startup macro called Autoexec and add the function to it using the RunCode command
      To see this in action, you can run or rename the inactive Autoexex macro that I have created as Autoexec

      NOTE: If you use the Autoexec macro, you should remove the startup form frmStart from Access Options as this runs BEFORE the Autoexec macro.



4.   Disable various Access options                                                                                   Return To Top

      From the File menu, click Options then Current Database


      UNTICK all the items highlighted above:

a)   allow full menus – doing this removes all menu items except File and Home


      The File menu will look like the Runtime version with Print / Privacy Options / Exit items only
      However, clicking the Privacy Options item displays the full Access Options screen as shown above

b)   allow default shortcut menus – this disables right click context menus

c)   use Access special keys - this disables the following keyboard combinations that can be used in Access.
      F11              - shows & hides the navigation pane
      Ctrl+G         - shows the Immediate window in the Visual Basic Editor
      Alt+F11       - starts the Visual Basic Editor
      Ctrl+Break  - prevents Access retrieving records from the server

      NOTE:  After changing any of these options, you will see a message like this:


      However, be aware that all these changes can still be reversed using Access Options/Privacy Options (see above)



5.   Remove ribbon menu items                                                                                       Return To Top

      Do ONE of the following ….. BUT MAKE another backup first!

a)   Remove Privacy Options from the File menu (otherwise users can undo all the above changes)

      To do so, you first need to create a new table called USysRibbons with 3 fields:
          ID (autonumber PK field), RibbonName (Text - 255), RibbonXML (Memo/LongText)

      In the RibbonName field enter NoPrivacy (or similar)

      In the Ribbon XML field enter:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
 <ribbon startFromScratch="true">
 </ribbon>
 <backstage>
   <button idMso="ApplicationOptionsDialog" visible="false"/>
 </backstage>
</customUI>

      NOTE:
      A USysRibbons table with this code has already been created to save you time.

      Access will treat this as a system table so it should not normally appear in the navigation pane
      Close and reopen the database. No change will yet be applied as the ribbon hasn’t been selected.
      Go to File . . . Current Database and select NoPrivacy as the ribbon. Close and reopen again.


      The menu now only has the File menu and Privacy Options have been removed


      If you have already applied the previous changes, the File menu will now be reduced to Info, Print, Feedback and Exit



b)   Remove all items from the File menu
      Add a new record to USysRibbons and enter SimpleFile as the RibbonName
      In the RibbonXML field, enter:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
  <ribbon startFromScratch="true">
  <tabs>
        <tab idMso="TabHomeAccess" visible="false"/>
    </tabs>
    </ribbon>
  <backstage>
      <tab idMso ="TabPrint" visible="false"/>
      <button idMso ="ApplicationOptionsDialog" visible="false"/>
       <button idMso="FileExit" visible ="false"/>
      <tab idMso ="TabOfficeFeedback" visible ="false"/>
      <tab idMso ="TabInfo" visible ="false"/>
  </backstage>
</customUI>

      Change the selected ribbon to SimpleFile in Access Options


      After you have applied this, close and reopen your application.
      If you have also disabled items in point 3 above, you will now see an empty File menu.



      However, if you are trying to do this using a file with PrivacyOptions already removed, you can’t access this menu item!

      The work-round if you are in this position is a bit convoluted:
      -   Rename your USysRibbons table temporarily to e.g. XSysRibbons.
      -   Close and reopen the database then clear the ribbon name from Access Options
      -   Rename the table back to USysRibbons. Close & reopen again
      -   Select SimpleFile as your ribbon in Access Options. Close and reopen again.

      If you have already removed the navigation pane, revert to the backup you created earlier
      You did create that backup . . . didn’t you?

c)   Depending on your existing ribbon settings, users may be able to customise the Quick Access Toolbar (QAT)
      Doing so will reinstate some functionality that will negate much of the above security

      In older versions of Access, the QAT can not be hidden whilst any parts of the ribbon are visible.
      However, in recent versions of Access 365, the ribbon and QAT can be managed separately


      All the QAT menu items can be disabled (except for position above/below ribbon) OR the QAT can be completely hidden.



      You can also disable the QAT by adding a new record to USysRibbons and enter SimpleFileNoQAT as the RibbonName.
      In the RibbonXML field, enter:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
  <ribbon startFromScratch="true">
    <tabs>
          <tab idMso="TabHomeAccess" visible="false"/>
    </tabs>
    <qat>
    </qat>
    </ribbon>
    <backstage>
      <tab idMso ="TabPrint" visible="false"/>
      <button idMso="ApplicationOptionsDialog" visible="false"/>
      <button idMso="FileExit" visible="false"/>
      <tab idMso="TabOfficeFeedback" visible="false"/>
      <tab idMso ="TabInfo" visible ="false"/>
  </backstage>
</customUI>

      NOTE:
      The two extra lines shown in RED are used to ensure the QAT is empty & the menu is disabled.

      Change the selected ribbon to SimpleFileNoQAT in Access Options
      After you have applied this, close and reopen your application.

d)   Hide the ribbon completely using the HideRibbon procedure in module modRibbon
      NOTE: this also hides the quick access toolbar (QAT)


      A similar ShowRibbon procedure is also supplied

      NOTE:
      Do not use the SimpleFile ribbon approach as well as HideRibbon.
      The USysRibbons table actions will take priority



6.   Disable the Shift Bypass                                                                                               Return To Top

      Users holding down the Shift key whilst opening your database can bypass all start-up code (and all security measures you’ve added).
      This is commonly referred to as the Shift Bypass

      For example, using the Shift Bypass, users can display the navigation pane and ribbon even if previously hidden and use any Access special keys.
      Doing this means users will have full access to most database features.

      This is a serious security issue and none of the above security measures will prevent this.
      However, the shift bypass can be disabled using code.

      The module modSecurity contains a function called DisableShiftBypass.
      Before running this, you should make another BACKUP in case you lock yourself out!

      You can run this from the Form_Load event of your startup form (as for the HideNavigationPane function)
      This code exists but has been disabled in my example app

      Alternatively, run it from an Autoexec macro (rename the inactive Autoexex  macro in my example app)

      IMPORTANT:
      The function works by first creating a property called AllowByPassKey (if it doesn’t already exist) and then setting the property to False.
      You will need to close and reopen your database TWICE to ensure the shift bypass has been disabled

      NOTE:
      If you still have access to the VBE, you can reverse this by running the EnableShiftBypass function



7.   Encrypt using a strong password                                                                               Return To Top

a)   Open the database in Exclusive Mode.
      Click File . . . Info then Encrypt with Password.


      Enter a strong password that cannot easily be guessed (20 characters maximum) using a mixture of upper/lower case letters and numbers

      For example, DLt3Bgydi2tD is a strong password but letmein and 12345 are not.

      Enter the password again to confirm


      You will then see this message. Don’t panic! Your database will be perfectly safe!


      Close and reopen normally. You will need enter the password which is of course case sensitive

      If you need to edit or remove the password later you will need to open the database exclusively using the existing password.
      Next go to File . . . Info . . . Decrpyt Database and make your changes.


      Make sure the password is memorable but do NOT store it anywhere in the application (FE or BE)
      If you forget the password, you will be locked out!

      HINT:
      A strong password like that above can be based on a phrase that is memorable to you but not obvious to anyone else.
      See if you can work out the phrase on which DLt3Bgydi2tD is based . . .

b)   Encrypting your database does more than just requiring users to enter a password.
      It also encrypts the application file so it cannot be read using a text or hex editor.
      See my article: Compare Access Security in ACCDB/MDB files

c)   It is also possible to just protect your VBA code with a password
      To do so, open the Visual Basic Editor from the Database Tools menu
      Then click Tools . . . (Database name) Properties


      Now click the Protection tab
      Tick Lock project for viewing. Enter your chosen password twice and click OK


      NOTE:
      1.   VBA project passwords are MUCH less secure and a method exists which allow them to be bypassed.
            It is STRONGLY RECOMMENDED that you do NOT rely on this approach alone

      2.   Using a VBE password, the code cannot be viewed. However, it is still possible to get a list of VBA procedures and constants from the VBE Object Browser


          To prevent this, add the line Option Private Module at the top of each standard/class module as shown below:


          This code line isn't allowed in form/report modules. Place all 'mission critical' code in standard/class modules



8.   Split your database                                                                                                       Return To Top

      Databases in a multi-user environment should ALWAYS be split into a frontend and backend application.
      It is a good idea to also do this even if there will only ever be one user as it reduces the risk of data loss

a)   All tables should be moved to a shared backend database (BE) in a secure location on the server
      Users should connect to the backend files on a LOCAL area network (LAN)

      - Do NOT use a WIDE area network (WAN) as it is likely to be slow and cause corruption issues.
      - NEVER use a cloud based location such as OneDrive or Dropbox as this significantly increases the risk of corruption

b)   Each user needs their own copy of the front end (FE) on their own hard drive
      - NEVER allow multiple users to run an FE from a shared hard drive
      - Users should NEVER run a split database when connected to the server using a WIRELESS connection
        Corruption is almost inevitable due to dropped connections



9.   Convert the FE to an ACCDE file to make all code inaccessible                               Return To Top

      In the File menu, click Save As then Make ACCDE



      Access will only allow you to create an ACCDE file providing the project is fully compiled.

      If there are any compilation errors, these will need to be fixed first.
      To do so, open the VB Editor then click Debug…Compile
      Typically, compilation errors will be due to undeclared variables. Fix EACH error displayed in turn.
      When the project is fully compiled, the Compile menu item will be disabled

      NOTE:
      Unlike ACCDB files, ACCDE files created in 32-bit Access will only run in that 'bitness'.
      Similarly for ACCDE files created in 64-bit Access.
      If you have some users in 32-bit & others in 64-bit Access, you will need to create TWO ACCDE files

      Although users will still be able to open the VBE (unless prevented by other means), clicking on any database object will result in this message:


      There is no benefit in creating an ACCDE file for your backend database providing it only contains tables

      IMPORTANT:
      Make sure you keep a copy of the original ACCDB file to allow continued development work

      It is ALMOST impossible to convert an ACCDE back to the original ACCDB file.
      There are a few specialist firms that can reverse engineer ACCDE files but it will be very expensive
      Reputable firms will also require proof of ownership before proceeding



10.   Apply security using database properties in code                                                  Return To Top

      It is possible to update almost all the above database properties using VBA.

      The module modSecurity contains 3 other very useful procedures you can use:
a)   StartUpProps / DeleteStartUpProps – these contain the required functionality used in the procedure below

b)   ModifyStartupProps – this allows you to disable any or all of the following items using code
          - Allow Full Menus
          - Show Status bar at startup
          - Allow built in toolbars
          - Allow Shortcut menus
          - Allow toolbar changes
          - Allow special keys
          - Show database window at startup
          - Allow Shift bypass key

    The procedure works by first deleting existing start up properties. It then reapplies them with all properties set to false.

    To test the effects of each, it is RECOMMENDED that you try each item in turn disabling all other items.

    Once again, you may need to close and reopen your database TWICE to ensure the change is fully implemented.

    When you have decided which items to use, you can add ModifyStartupProps to the Form_Load event of the startup form.
    Alternatively, use this in an Autoexec macro

    As this procedure duplicates many of the other methods described above, you may need to remove any repeated code



11.   Block Imported Autoexec Macro                                                                              Return To Top

    This issue was raised by @ZEEq in a thread at Access World Forums on 7 June 2023: Autoexec export

    Whilst perhaps an unlikely screnario, it isn't difficult for someone to export an autoexec macro into your database.
    This would overwrite any existing autoexec macro and, potentially, could undo some of the scurity you have added when it runs at startup.

    Unfortunately, it isn't possible to prevent this happening but you can stop such an action having any effect.
    To do so, first make a backup copy of your own autoexec macro and name it e.g. autoexex or (or something non-obvious for obfuscation).

    Next add the following code to the Form_Load event of your startup form

Private Sub Form_Load()

      'check & delete autoexec macro if it exists (as it may have been imported overwriting the original)
      If DCount("*", "MSysObjects", "Name = 'Autoexec' AND Type = -32766") > 0 Then
            DoCmd.DeleteObject acMacro, "Autoexec"
      End If

      'Now copy the autoexex macro and rename as Autoexec to ensure the correct macro always runs
      DoCmd.CopyObject , "Autoexec", acMacro, "autoexex"

End Sub

    This will delete the existing Autoexec macro if it exists and replace with the correct copy!

    A bit tortuous but it will work because, contrary to popular belief, the startup form loads BEFORE the autoexec macro.
    See my article: Debunking Myths about the Autoexec Macro



12.   Encrypt your data                                                                                                       Return To Top

      Authorised users with advanced Access knowledge can still gain access to your data from outside the application if they know the encryption password.
      For security reasons, I am deliberately NOT going to explain how this is done

      However, it is also possible to encrypt the data itself.
      Doing so means that anyone who does manage to retrieve your data tables will see something like this:



      For further information on this approach, see my article Encrypted Split No Strings Database

      These articles may also be useful:
      Compare Security in ACCDB/ACCDE/MDB/MDE files
      Purpose of System Tables



13.   Download                                                                                                                 Return To Top

      Click to download:       Example application with code:       SYD_v1.7.accdb                       approx 0.8 MB (zipped)

      NOTE:
      The VBA project in the above file has been digitally signed with a code signing certificate.
      This means the file is trusted. You will not need to unblock the file to remove the 'mark of the web'



14.   Updates / Acknowledgements                                                                               Return To Top

      I hope this article has been useful to all Access developers

a)   Version 1.2 - 19 Mar 2019
      Thanks to Access World Forums member Scott Prince for informing me about an error in my original article related to the use of the shift bypass key.
      This was corrected and additional information provided in both the article and example application.

b)   Version 1.5 - 4 Sept 2022
      Many thanks to Peter Doering (MVP) for pointing out that there were unintended spaces in the code blocks above which prevented the code running correctly.
      Hopefully, these have now all been fixed in the article! The example app was already correct.

c)   Version 1.6 - 30 Apr 2023
      Updated ribbon XML code to handle changes to the File . . . Info menu in recent versions of Access 365. Updated example app with amended ribbon code.
      Updated article in line with changes to handling the QAT separately from the ribbon. Updated all screenshots for Access 365 (previously for A2010)

c)   Version 1.7 - 8 Jun 2023
      Added code to the startup form to prevent any imported autoexec macro from running (see section 11) above



14.   Feedback                                                                                                                 Return To Top

      I would be grateful for any further feedback on the article including details of any errors or omissions.

      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 8 June 2023



Return to Access Articles Page Page 2 of 3 1 2 3 Return to Top