Navigation:  Articles > Mar-1998 >

Tip: Controlling Access to the Database Window

Previous pageReturn to chapter overviewNext page

Richard A. Hunt          
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 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.
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 prp As Property

Const conPropNotFoundError = 3270


Set dbs = CurrentDb

dbs.Properties(strPropName) = varPropValue

ChangeProperty = True



   Exit Function



If Err = conPropNotFoundError Then

 Set prp = dbs.CreateProperty(strPropName, _

             varPropType, varPropValue)

 dbs.Properties.Append prp

 Resume Next


 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



   Exit Sub



   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



  Exit Sub



   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.