Tip: Controlling Access to the Database Window

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).

Warnings
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.

The 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

ChangeExit:

Exit Function

ChangeErr:

If Err = conPropNotFoundError Then

Set prp = dbs.CreateProperty(strPropName, _

varPropType, varPropValue)

dbs.Properties.Append prp

Resume Next

Else

ChangeProperty = False

Resume Change_Bye

End If

End Function

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, _

"Switchboard Main"

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

SetStartUpPropertiesExit:

Exit Sub

SetStartUpPropertiesErr:

MsgBox Error$

Resume SetStartUpPropertiesExit

End Sub

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

Case "Password"

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

Case Else

MsgBox Msg, vbOKOnly + vbInformation, _

"Password Invalid"

End Select

ShowWindowExit:

Exit Sub

ShowWindowErr:

MsgBox Error$

Resume ShowWindowExit

End Sub

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.