As part of the Microsoft Access training used to teach VBA programmers, the “AllowByPassKey” became handy and a good example why you would consider using VBA code for your database. For a distributed application, you will invariably have what is known as ‘Startup‘ switched on and enabled which means that when users start an Access database file it will take you to some sort of starting screen (an Access form) which would be a welcome or main-menu screen.
In a previous blog posting way back in 2011, I mentioned in a quick article on how to ‘ByPass’ Microsoft Access database startup (if applied) that referenced versions up to and including version 2010. Of course, this still exists and the latest version (at the time being Access 365/2016) still utilises this effective switch.
This is used to add a simple but effective way to take end-users to a starting point and not be confronted with either the Database Window (Access 2003 or earlier) or Navigation Pane (Access 2007 and 2010) and allow access to most if not all objects – causing mayhem!
In the article, I mentioned“if you wish to stop this bypass from firing up that impressive navigation form welcoming you to run a report, enter a new record or search for a list then using the following technique of holding down the SHIFT key when double-clicking the icon (the Access database file) will stop the ‘startup‘ and take you straight into the back-end system.” However, it I also said that Access VBA code would be required to fully control this switch as it can be locked-down from even using the SHIFT key too. — You need a backdoor approach!
The following code can be copied into a Module (and saved). There are three procedures (two subs and one function):
The three procedures are:
1. Sub StopStartup – which disables the ability to use the SHIFT key when starting your database.
2. Sub AllowStartup – which enables the ability to use the SHIFT key when starting your database and enter the ‘backstage’ of the database application.
3. Function ChangeProperty – which returns true or false (integer value) for a string property known to Microsoft Access (in this case “AllowByPassKey”).
You attach one of the sub procedures to where you call and invoke the routine. For example, a command button on a form can be attached to AllowStartup which switches on the ability to use the SHIFT key action. Perhaps adding the StopStartup procedure can be added to the Form_Load event for a form so that it switches off the ability to use the SHIFT key until manually switched back on.
When using the AllowStartup procedure, you then close the database file and then start it again using the SHIFT key action.
You may want to add code to automatically shut the application with a prompt (MsgBox) telling the user this will happen.
There are other keys too to help expand and build a more flexible set of tools for your Microsoft Access database:
The above keywords will need more research which I covered in my Microsoft Access training days before you can apply them (and may vary based on which version your are currently using) but in essence, using VBA code for your database will give a true professional lift making it stand out from the rest.