Override the Shift key in Access

Using the startup properties in Access, the database window can be hidden when a database is open. This is usually turned on for most production databases. When the developer needs to make changes, the shift key is used when opening the database to override all startup options. This is a handy feature, but also allows other not so savy users to do the same. The AllowByPassKey property can be used to "password" protect this feature by enabling or disabling the shift key. However, because of the potential of locking yourself out of a database, Microsoft has not created this property by default.

The AllowByPassKey property is explained in detail in the VBA help file that comes with Access. However, the sample code provided in the help files does not use the fourth DDL argument when making a call to CreateProperty. This means that anyone who can open the database can programmatically reset the AllowBypassKey value.

Therefore, in order to restrict the change capabilities to only the Admins, set the fourth argument to True when calling CreateProperty. And don't lock yourself out!

Below is the code that can be used to create this property and some comments on how to use it. Check out the help file for more detail.

'*********** Code Start ***********
'This code was originally written by Michael Kaplan.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Michael Kaplan
'
Function ChangePropertyDdl(stPropName As String, _
PropType As DAO.DataTypeEnum, vPropVal As Variant) _
As Boolean
' Uses the DDL argument to create a property
' that only Admins can change.
'
' Current CreateProperty listing in Access help
' is flawed in that anyone who can open the db
' can reset properties, such as AllowBypassKey
'
On Error GoTo ChangePropertyDdl_Err

Dim db As DAO.Database
Dim prp As DAO.Property

Const conPropNotFoundError = 3270

Set db = CurrentDb
' Assuming the current property was created without
' using the DDL argument. Delete it so we can
' recreate it properly
db.Properties.Delete stPropName
Set prp = db.CreateProperty(stPropName, _
PropType, vPropVal, True)
db.Properties.Append prp

' If we made it this far, it worked!
ChangePropertyDdl = True

ChangePropertyDdl_Exit:
Set prp = Nothing
Set db = Nothing
Exit Function

ChangePropertyDdl_Err:
If Err.Number = conPropNotFoundError Then
' We can ignore when the prop does not exist
Resume Next
End If
Resume ChangePropertyDdl_Exit
End Function

Function ChangeProperty(strPropName As String, _
varPropType As Variant, varPropValue As Variant) As Integer
' The current listing in Access help file which will
' let anyone who can open the db delete/reset any
' property created by using this function, since
' the call to CreateProperty doesn't use the DDL
' argument
'
Dim dbs As Database, prp As Property
Const conPropNotFoundError = 3270

Set dbs = CurrentDb
On Error GoTo Change_Err
dbs.Properties(strPropName) = varPropValue
ChangeProperty = True

Change_Bye:
Exit Function

Change_Err:
If Err = conPropNotFoundError Then ' Property not found.
Set prp = dbs.CreateProperty(strPropName, _
varPropType, varPropValue)
dbs.Properties.Append prp
Resume Next
Else
' Unknown error.
ChangeProperty = False
Resume Change_Bye
End If
End Function
' *********** Code End ***********