First Published 4 Nov 2023                         Last Updated 29 Apr 2024


The first two articles in this series provided automation code to open databases using the shift key to bypass startup code.

However, the code will not work if the AllowBypassKey property of the database has been disabled.
This is often done as a security measure to ensure required startup code is always run.

The AllowBypassKey property does not exist by default. Before it can be disabled (or enabled), the property must first be created.

The code to do this for the current database is shown below. It should be placed in a standard module

CODE:

Option Compare Database
Option Explicit

Dim db As DAO.Database
Dim prop As DAO.Property
Const conPropNotFound = 3270

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

Function DisableShiftBypass()

'This function disables the shift at startup. This action causes
'the Autoexec macro and Startup properties to always be executed.

On Error GoTo Err_Handler

      Set db = CurrentDb()

      'This next line disables the shift key on startup.
      db.Properties("AllowByPassKey") = False

Exit_Handler:
      Exit Function

Err_Handler:
      'The first part of this error routine creates the "AllowByPassKey property if it does not exist.
      If Err = conPropNotFound Then
            Set prop = db.CreateProperty("AllowByPassKey", dbBoolean, False)
            db.Properties.Append prop
            Resume Next
      Else
            MsgBox "Error " & Err & " " & Err.Description, vbExclamation, "DisableShiftBpass did not complete successfully."
            Resume Exit_Handler
      End If

End Function

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

Function EnableShiftBypass()

'This function enables the SHIFT key at startup. This action causes
'the Autoexec macro and the Startup properties to be bypassed
'if the user holds down the SHIFT key when the user opens the database.

On Error GoTo Err_Handler

      Set db = CurrentDb()

      'This next line of code enables the use of the SHIFT key on startup.
      db.Properties("AllowByPassKey") = True

Exit_Handler:
      Exit Function

Err_Handler:
      'The first part of this error routine creates the "AllowByPassKey property if it does not exist.
      If Err = conPropNotFound Then
            Set prop = db.CreateProperty("AllowByPassKey", dbBoolean, True)
            db.Properties.Append prop
            Resume Next
      Else
            MsgBox "Error " & Err & " " & Err.Description, vbExclamation, "EnableShiftBpass did not complete successfully."
            Resume Exit_Handler
      End If

End Function

Unfortunately, it is also possible to do each of these from an external database using automation code.
This does make your databases more vulnerable to hacking.

As the code to do this is already widely available on the internet, I am providing it below.
However, later in this article I will also show how you can mitigate the effects of this in your own databases

The following code allows you to check the AllowByPassKey value and to enable or disable it from an external database. Place it in a standard module.

CODE:

Option Compare Database
Option Explicit

Dim db As DAO.Database
Dim prop As DAO.Property
Const conPropNotFound = 3270

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

Function CheckBypassValue(strPath As String, Optional strPwd As String) As Boolean

On Error GoTo Err_Handler

      If strPwd <> "" Then
           Set db = DBEngine.OpenDatabase(strPath, True, False, "MS Access;PWD=" & strPwd)
      Else
           Set db = DBEngine.OpenDatabase(strPath, True, False)
      End If

      'Get the value of the specified property
      CheckBypassValue = db.Properties("AllowByPassKey")

     If CheckBypassValue Then
           MsgBox "Shift bypass enabled", vbInformation, strPath
     Else
            MsgBox "Shift bypass disabled", vbInformation, strPath
      End If

Exit_Handler:
      Exit Function

Err_Handler:
      'err 3270 'The property was not found
      If Err.Number = 3270 Then
            MsgBox "Shift bypass property does not exist", vbInformation, strPath
      Else
            MsgBox "Error " & Err.Number & " in CheckBypassValue procedure: " & vbCrLf & "Description: " & Err.Description
      End If

      Resume Exit_Handler

End Function

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

Sub DisableShiftBypassExtDB(strPath As String, Optional strPwd As String)

'This function disables the shift at startup.
'This action causes the Autoexec macro and Startup properties to always be executed.

On Error GoTo Err_Handler

      If strPwd <> "" Then
            Set db = DBEngine.OpenDatabase(strPath, True, False, "MS Access;PWD=" & strPwd)
      Else
            Set db = DBEngine.OpenDatabase(strPath, True, False)
      End If

      db.Properties("AllowByPassKey") = False
      db.Close
      Set db = Nothing

Exit_Handler:
      Exit Sub

Err_Handler:
      'The first part of this error routine creates the "AllowByPassKey property if it does not exist . . . and sets it false.
      If Err = conPropNotFound Then
            'err 3270
            Set prop = db.CreateProperty("AllowByPassKey", dbBoolean, False)
            db.Properties.Append prop
            Resume Next
      Else
            'err 3031 - not a valid password
            MsgBox "Error " & Err & " " & Err.Description, vbExclamation, "DisableShiftBpassExtDB did not complete successfully."
            Resume Exit_Handler
      End If

End Sub

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

Sub EnableShiftBypassExtDB(strPath As String, Optional strPwd As String)

'This function enables the shift bypass at startup.
'This action allows the Autoexec macro and Startup properties to be bypassed

On Error GoTo Err_Handler

      If strPwd <> "" Then
            Set db = DBEngine.OpenDatabase(strPath, True, False, "MS Access;PWD=" & strPwd)
      Else
            Set db = DBEngine.OpenDatabase(strPath, True, False)
      End If

      db.Properties("AllowByPassKey") = True
      db.Close
      Set db = Nothing

Exit_Handler:
      Exit Sub

Err_Handler:
      'The first part of this error routine creates the "AllowByPassKey property if it does not exist . . . and sets it true.
      If Err = conPropNotFound Then
            'err 3270
            Set prop = db.CreateProperty("AllowByPassKey", dbBoolean, True)
            db.Properties.Append prop
            Resume Next
      Else
            'err 3031 - not a valid password
            MsgBox "Error " & Err & " " & Err.Description, vbExclamation, "EnableShiftBpassExtDB did not complete successfully."
            Resume Exit_Handler
      End If

End Sub

The example app also includes an OpenWithShiftBypass procedure. This will only work if the shift bypass is first disabled e.g. using the code above

The code has been provided to assist users manage their own databases.
It should NOT be used to modify or hack any external database without permission.

Option Compare Database
Option Explicit
Option Private Module

#If VBA7 Then
      Declare PtrSafe Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
#Else
      Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
#End If

#If VBA7 Then
      Declare PtrSafe Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As LongPtr)
#Else
      Declare Sub keybd_event Lib "user32.dll" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
#End If

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

Sub OpenWithShiftBypass(strPath As String, Optional blnExcl As Boolean = False, Optional strPwd As String)

Dim appAccess As Access.Application

On Error GoTo Err_Handler

      'OPTIONAL - browse to your database, check shift bypass state and enable it if necessary
      'EnableShiftBypassExtDB strPath

      ' Create a new instance of Access
      Set appAccess = New Access.Application

      ' Simulate pressing the Shift key
      keybd_event vbKeyShift, 0, 0, 0

      'OPTIONAL - enable next line to add short delay of 0.1 second here if your results aren't reliable
      ' Sleep 100

      ' Open the target database
      If strPwd <> "" Then
            appAccess.OpenCurrentDatabase strPath, blnExcl, strPwd
      Else
            appAccess.OpenCurrentDatabase strPath, blnExcl
      End If

      'OPTIONAL - make external app visible
      appAccess.Visible = True

      ' Simulate releasing the Shift key
      keybd_event vbKeyShift, 0, 2, 0

      'OPTIONAL - Wait 5 seconds so external app can be viewed before closing it
      Sleep 5000

      'OPTIONAL - Close the target database
      appAccess.CloseCurrentDatabase

      ' Quit the new instance of Access
      appAccess.Quit

      ' Release the object
      Set appAccess = Nothing

Exit_Handler:
      Exit Sub

Err_Handler:
      'err 2467 = The expression you entered refers to an object that is closed or doesn't exist.
      'occurs if user closed external database manually before above code runs
      If Err = 2467 Then Resume Next

      MsgBox "Error " & Err.Number & " in line " & Erl & " of OpenWithShiftBypass procedure : " & _
            Err.Description, vbOKOnly + vbCritical, "Critical Error"

      keybd_event vbKeyShift, 0, 2, 0 'release the Shift key
      Resume Exit_Handler

End Sub

UPDATE 29 Apr 2024
I have added the OPTIONAL line Sleep 100 in the OpenWithShiftBypass procedure above following feedback from Greg Sevior.
He reported getting inconsistent results on a fast SSD drive running Access 2016. Adding a short delay of 0.1 second fixed the issue for him.

You should enable the line if necessary on your own test workstations.



Example usage:
a)   Database with no password
      CheckBypassValue "G:\MyFiles\ExampleDatabases\Northwind\2023\Northwind22Dev.accdb"

      DisableShiftBypassExtDB "G:\MyFiles\ExampleDatabases\Northwind\2023\Northwind22Dev.accdb"

      OpenWithShiftBypass "G:\MyFiles\ExampleDatabases\Northwind\2023\Northwind22Dev.accdb"

b)   Database encrypted with password: isladogs
      CheckBypassValue "G:\MyFiles\ExampleDatabases\ShiftBypass\TestDBPwd.accdb", "isladogs"

      EnableShiftBypassExtDB "G:\MyFiles\ExampleDatabases\ShiftBypass\TestDBPwd.accdb", "isladogs"

      OpenWithShiftBypass "G:\MyFiles\ExampleDatabases\ShiftBypass\TestDBPwd.accdb", "isladogs"





Click to download:

The zip file contains an example database BypassCode with all the above code in module modShiftBypass.
It also includes with two test databases TestDb and TestDBPwd. The password for the encrypted database is isladogs

      Bypass Test Databases      (ACCDB - zipped)

Download the zip file and unblock it.
For more details, see my article: Unblock downloaded files by removing the Mark of the Web

Unzip and save the ACCDB files to a trusted location.

The 2 test databases each have a startup form which is colour coded according to the AllowBypassKey value:

BypassKey does not exist

AllowBypassKey Enabled

AllowBypassKey Disabled

Further Information

The shift bypass is a very useful tool for developers to open databases without any startup code running.
For example, it is useful as a diagnostic tool if errors occur when an application is loaded.

Nevertheless when a database is deployed, most developers will want to ensure that startup code does run.
To do so, disable the shift bypass by setting the AllowByPassKey value to False.

However, the code above shows that it is also possible for other users to re-enable the shift bypass and undo some of the database security.

It is almost impossible to prevent being done to an Access database.

For that reason, you should not rely on the use of the shift bypass as the only method of securing your database.
For further information on other methods of adding security, see my article: Securing Your Database - A Tutorial

By following the suggestions in that article, you can make it much harder for users to bypass your security

The ShiftBypassQuit example database below uses several of those methods to limit the effects of bypassing security.
It is supplied as a 32-bit or 64-bit ACCDE with password isladogs

When the database is opened normally, the startup form frmStart appears 'floating on the desktop' with the Access application interface hidden.



Click the Enable Shift Bypass button and restart the application normally. Do NOT press the Shift key at this stage.
As the application loads, the Shift Bypass is automatically DISABLED again

Click the Enable Shift Bypass button and restart the application again. This time, press the Shift key as you enter the password.
The startup code is bypassed and the startup form does not load.

However, the navigation pane is empty as all database objects have been hidden.



Check Show Hidden Objects in Navigation Options to view the database objects



Click form frmAutomation. This opens but is hidden. It immediately disables the ShiftBypass again and form frmStart appears.
Once again the application interface is automatically hidden.

Repeat the previous steps but this time click frmStart in the navigation pane. The form does NOT open.
In fact, nothing seems to happen!
See my article Prevent Opening Objects From the Navigation Pane for an explanation of how this is done.

However, it is also possible to open forms from the Visual Basic Editor (VBE). As this is an ACCDE file, you cannot view the code.
Instead, type the following in the VBE Immediate pane:



When you press Enter, this message appears:



Click OK and the database closes.

Normally, I would recommend reapplying the shift bypass at startup but, for the purpose of this demo I have deliberately not done so.

Also try opening the app from a non trusted location and note what happens.



In summary, I have not prevented you re-enabling the shift bypass but if you do so, other code will (hopefully) prevent you getting anywhere 'useful'.

NOTE:
The example app is not completely secure. I know of at least one method of circumventing the security.
There are additional things I could do to improve it further. However, its good enough to deter all but the most determined hackers.

Obviously, using an ACCDE file provides added protection but the example app is also fairly secure even as an ACCDB file.

If you like a challenge and are able to bypass the built-in security, please email me using the Feedback form and let me know how you did so!



Click to download:

Choose the correct bitness for your version of Access:
          Shift Bypass Quit 32      (32-bit ACCDE - zipped)
          Shift Bypass Quit 64      (64-bit ACCDE - zipped)

Download the zip file and unblock it. Unzip and save the ACCDE file to a trusted location.



Further Reading

These related articles may also be of interest:

      Securing Your Database - A Tutorial

      Encrypted Split No Strings Database

      Prevent Opening Objects From the Navigation Pane

      Protect Data in Tables and Queries

      Lock Down Database Objects



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 29 Apr 2024



Return to Code Samples Page Page 3 of 3 1 2 3 Return To Top