Richard A. Hunt Bronze Collection
The startup options you set for your database can be bypassed by pressing the shift key while opening the database. Unfortunately, this can give your users unwelcome access to your database window. While the Microsoft Developers Kit allows you to hide the database window, this tip will give you more control over who accesses the database window without having to pay for the kit.
This process was written for MS Access 95 (v7.0). The ChangeProperty function and SetStartUpProperty sub below aren't original; they can be found in the Microsoft Access Help file (search for the AllowByPassKey property).
Timing is very important in implementing this process. Setting the AllowByPassKey database property to false prematurely or out of sequence can make the database window forever unavailable! Be careful! Please consider making a backup before implementing this process.
This is also not a substitute for setting database security. Using this process without proper database security will still allow access to your objects by importing them to a new database. Security should be set after implementing this process.
1. Create the ChangeProperty function using the following code:
Function ChangeProperty(strPropName As String, _
varPropType As Variant, _
varPropValue As Variant) As Integer
On Error GoTo ChangeErr
Dim dbs As DATABASE
Dim prp As Property
Const conPropNotFoundError = 3270
Set dbs = CurrentDb
dbs.Properties(strPropName) = varPropValue
ChangeProperty = True
If Err = conPropNotFoundError Then
Set prp = dbs.CreateProperty(strPropName, _
ChangeProperty = False
2. Create the SetStartUpProperties sub and call it using the OnOpen event in the startup form using the following code:
Public Sub SetStartUpProperties()
On Error GoTo SetStartUpPropertiesErr
Forms![Switchboard Main]![Command1].Enabled = False
Forms![Switchboard Main]![Command24].Enabled = False
Forms![Switchboard Main]![Command28].Enabled = False
Forms![Switchboard Main]![Command41].Enabled = False
Forms![Switchboard Main]![Command52].Enabled = False
ChangeProperty "StartupForm", dbText, _
ChangeProperty "StartupShowDBWindow", dbBoolean, True
ChangeProperty "StartupShowStatusBar", dbBoolean, True
ChangeProperty "AllowBuiltinToolbars", dbBoolean, True
ChangeProperty "AllowFullMenus", dbBoolean, True
ChangeProperty "AllowBreakIntoCode", dbBoolean, True
ChangeProperty "AllowSpecialKeys", dbBoolean, True
ChangeProperty "AllowBypassKey", dbBoolean, True
I used the first five lines to deactivate command buttons on my startup form that I didn't want users to have access to. These lines are, of course, optional. The remainer of the sub uses the ChangeProperty function to set the database startup properties. Initially, the AllowBypassKey, AllowBuiltInToolBars, and StartupShowDBWindow properties should be set to true. All of the properties can be toggled later as desired. This procedure should be called in the OnOpen event in your startup form. Opening the form sets the startup properties, but they won't take effect until the next time you open the database.
3. Create the ShowWindow sub and call it in OnClick property of a command button on your startup form using the following code:
Public Sub ShowWindow()
On Error GoTo ShowWindowErr
Dim Msg, Prompt, Title, Response As String
Prompt = _
"Please enter password to view the database window:"
Title = "Password"
Msg = "The password was invalid. Please try again."
Response = InputBox(Prompt, Title)
Select Case Response
DoCmd.DoMenuItem 4, 0, 3, 0, acMenuVer70
Forms![Switchboard Main]![Command1].Enabled = True
Forms![Switchboard Main]![Command24].Enabled = True
Forms![Switchboard Main]![Command28].Enabled = True
Forms![Switchboard Main]![Command41].Enabled = True
Forms![Switchboard Main]![Command52].Enabled = True
MsgBox Msg, vbOKOnly + vbInformation, _
You can set the password to anything you want. Supplying the correct password allows you to unhide the database window and enables the previously disabled command buttons on the startup form.
4. Set the AllowBypassKey, AllowBuiltInToolBars, and StartupShowDBWindow properties to false. These properties will take effect the next time the startup form opens. The AllowBuiltInToolBars property should be set to false because several tool bars have an unhide database window button on them.
5. Build a custom menu based on the Startup menu and remove the Unhide command and the spacer between Exit and Send. Set the startup menubar to your new menu bar. The unhide command should be deleted from your custom menu for obvious reasons (an alternative is to call the ShowWindow sub instead). The spacer between Exit and Send allows previously opened databases to be reopened. With the bypass key disabled, the database window can still be accessed by opening the database twice (the second time reveals the database window).
6. Close and reopen the database.
The database window will only be available through the command button on your startup form.