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