Hack 80. Prevent Users from Disabling Your Startup Options
Stop users from being able to hold down the Shift key to get to the database window.
After spending all that time developing your database application and then setting the startup options, the last thing you want is for someone to be able to simply hold down the Shift key during startup and then mess around with your application. This hack explores two different ways to prevent this: disabling the Shift key code for Access databases (MDBs) and disabling the Shift key code for Access Data Projects (ADPs).
8.10.1. Access MDB
With MDB files, the hack works by adding a property called AllowBypassKey to the database object. Setting the property to False disables the Shift key, and changing it back to TRue enables it again. You need to decide on an event to trigger the value change. It could be when a specific user logs into the database or, as in this code example, when a file named AllowByPass.txt is in the same directory as the database:
Public Function DetermineByPass( ) If Len(Dir(CurrentProject.Path & "\AllowByPass.txt")) = 0 Then ChangeProperty "AllowBypassKey", DB_BOOLEAN, False, True Else ChangeProperty "AllowBypassKey", DB_BOOLEAN, True, True End If End Function Public Function ChangeProperty(strPrpName As String,_ varPrpType As Variant, _ varPrpValue As Variant, _ bolDDL as Boolean) As Boolean Dim prp As Variant Const conPrpNotFoundError = 3270 On Error GoTo ChangePrp_Err CurrentDb.Properties(strPrpName) = varPrpValue ChangeProperty = True ChangePrp_Bye: Exit Function ChangePrp_Err: If Err = conPrpNotFoundError Then 'Property not found Set prp = CurrentDb.CreateProperty(strPrpName, _ varPrpType, varPrpValue, bolDDL) CurrentDb.Properties.Append prp Resume Next Else 'Unknown Error MsgBox Err.Description ChangeProperty = False Resume ChangePrp_Bye End If End Function
After the code checks for the AllowByPass.txt file, it calls the ChangeProperty function and sets it to False if the file isn't found or to TRue if it is found.
First the ChangeProperty function attempts to set the value passed to it in varPrpValue to the strPrpName property, which in this case is AllowBypassKey. An error number (3270) occurs the first time this function is called with a new property because that property has not yet been appended to the data-base's properties collection. The function traps for this error number and creates the property with the error-handling routine.
This example uses CurrentDB so that you don't need to manually set a reference to the DAO library for Access 2000 and 2002 (Access 2003 has the reference set by default). It is worth noting that the CurrentDB method establishes a hidden reference to the Microsoft DAO 3.6 object library when used in a 2000 or 2002 MDB file.
The last parameter to the ChangeProperty function (bolDDL) sets the DDL parameter of the AllowBypassKey property. The DDL parameter determines if the property can be altered via automation. By setting this parameter to true, you prevent someone with VBA coding experience from resetting the parameter by automation.
Once the code is in place in an Access module, you need to make sure it gets run when the database starts up. The best way to do this is to use the AutoExec macro. The AutoExec macro is nothing more than a regular macro with a special name. Access automatically runs it based on its name. You should note that the AutoExec macro gets executed after any form is opened from the Startup properties. This is something to consider if you are using the Startup properties and you place any events in the macro other than a call to the DetermineByPass function.
Once you've set up the AutoExec macro to call your function and the startup form is in place (called from either the AutoExec macro or the Startup properties), open the database without holding down the Shift key. This allows the AllowBypassKey property to be added to the database for the first time.
If you open the Access database with the AllowByPass.txt file in the same directory as the Access MDB, it sets the AllowBypassKey property to true but doesn't allow the bypass the first time the database is opened. The Access database must be opened a second time while holding down the Shift key to bypass the Startup properties, including the AutoExec macro, because now the AllowBypassKey internal property is set to TRue. Removing the AllowByPass.txt file from the database's directory resets the property to False the next time the database is started up without holding down the Shift key, thereby preventing the next user from bypassing the Startup properties and the AutoExec macro.
If you use Access 2003, and you don't have your macro security setting set to Low, you need to hold down the Shift key when clicking the Open command button of the Access Security Warning screen shown in Figure 8-15.
Figure 8-15. Access 2003 security warning
8.10.2. Access ADP
Because most ADPs use ADO only, the developers at Microsoft provided a way to disable the Shift key without referencing a DAO library directly or indirectly. This method of adding the AllowBypassKey is much simpler, but unfortunately, it works only with ADP projects. The following sample code works identically to the code for an Access MDB, without the need for a function such as ChangeProperty:
Public Function DetermineByPass( ) If Len(Dir(CurrentProject.Path & "\AllowByPass.txt")) = 0 Then CurrentProject.Properties.Add "AllowBypassKey", False Else CurrentProject.Properties.Add "AllowBypassKey", True End If End Function
Although this method for setting the AllowBypassKey property is much shorter and arguably easier to use with an ADP, it does have a drawback. You have no way of setting the DDL parameter to prevent someone from changing your AllowBypassKey property with automation.
8.10.3. Be Careful
This technique of disabling the Shift key is powerful; make sure you don't lock yourself out of your database. You might want to consider changing the DDL parameter in the sample code for the Access MDB to False so that you can use remote automation to reset it if you need to. If you do lock yourself out, you can always create a new database and import all the objects over to the new database, then reset your Startup properties and replace the database you got locked out of with the new copy you just made.