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 with an example application to illustrate the possible methods available
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.
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.
Colin Riddington Mendip Data Systems Last updated 21/03/2019
To provide feedback on this article, please enter your name, e-mail address and company (optional) below, add your comment and press the submit button. Thanks
Click to download :
a) Example application with code: SYD_v1.3.accdb approx 0.8 MB (zipped)
b) This article as a PDF file: Secure Your Database v1.3 approx 0.8 MB (zipped)
Difficulty level : Moderate
1. Use a Runtime version
LOCKED - with form visible ONLY
Click any image to view a larger version
UNLOCKED – Ribbon / Nav pane visible
All code required make these changes is available in the supplied example application
All screenshots are from Access 2010 unless stated otherwise. Some items may look different in other versions
If your users have a runtime version of Access, much of the functionality is disabled
Click any image to view
a larger version
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 will provide sufficient security to prevent tampering by many 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
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
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)
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 table may have 'knock on' effects on other 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
3. Hide the navigation pane
A more useful approach is to hide the navigation pane completely
Untick Display Navigation Pane in Access Options (see screenshot below)
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:
• 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
• 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
4. Disable various Access options
From the File menu, click Options then Current Database
UNTICK all the following items:
• allow full menus – 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
• allow default shortcut menus – this disables right click context menus
• 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
After changing any of these options, you will see a message like this:
However, all these changes can still be reversed using Access Options/Privacy Options (see above)
5. Remove ribbon menu items
Do ONE of the following ….. BUT MAKE another backup first!
• Remove Privacy Options from the File menu (otherwise users can undo all the above changes)
To do so, you 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:
<button idMso="ApplicationOptionsDialog" visible="false"/>
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 be reduced to Print and Exit
• Remove all items from the File menu
Add a new record to USysRibbons and enter SimpleFile as the RibbonName
In the RibbonXML field, enter:
<tab idMso="TabHomeAccess" visible="false" />
<tab idMso ="TabPrint" visible="false"/>
<button idMso="ApplicationOptionsDialog" visible="false"/>
<button idMso="FileExit" visible="false"/>
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 doing this using a file with PrivacyOptions 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?
If you are using Access for Office 365, the Feedback menu item will still be visible:
I believe the Feedback item can be disabled but not removed completely
However, I’ve not investigated this as it isn’t a security risk,
• 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
Do not use the SimpleFile ribbon approach as well as HideRibbon.
The USysRibbons table actions will take priority
7. Encrypt using a strong password
• Open the database in Exclusive Mode.
Click File … Info then Encrypt with Password.
Enter a strong password that cannot easily be guessed (14 characters maximum) using a mixture of upper/lower case letters and numbers
e.g. DLt3Bgydi2tD is good but letmein or 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 it exclusively, enter the existing password then 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!
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…!
• 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 the article Compare Access Security in ACCDB/MDB files
• 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
VBA project passwords are MUCH less secure and a method exists which allow them to be bypassed.
It is STRONGLY RECOMMENDED you do NOT rely on this approach alone
8. Split your database
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
• 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)
- RECOMMEND you do NOT use a WIDE area network (WAN) as it is likely to be slow and cause issues
- NEVER use a cloud based location such as OneDrive or Dropbox as this increases the risk of corruption significantly
• 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
In the File menu, click Save & Publish then Make ACCDE
Access will only allow you to create an ACCDE file is 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
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 if it only contains tables
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
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:
• StartUpProps / DeleteStartUpProps – these contain the required functionality used in the procedure below
• 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
For further information on this approach, see the article Encrypted Split No Strings Database
These articles may also be useful:
I hope this article has been useful to all Access developers
I am extremely grateful 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 has now been fixed and additional information provided in both the article and example application
I would be grateful for any further feedback on the article including details of any other errors or omissions.
If you do wish to respond, please send me an email or complete the feedback form below
6. Disable the Shift Bypass
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 (and therefore have access to most/all 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)
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
If you still have access to the VBE, you can reverse this by running the EnableShiftBypass function
10. Apply security using database properties in code
It is still possible for authorised users with advanced Access knowledge to gain access to your data from outside the application provided 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:
11. Encrypt your data