You may not need to implement full-blown measures to secure your Access data, but you can apply some simple tricks to protect it from careless or overly curious users. Hiding the Database window offers one good safeguard.

The simplest way to accomplish this is via the Access Startup options (Figure A). These options let you determine specific behaviors when the database opens. Two of these features lend a hand toward securing your database:

  • Display Database Window: Deselect this option, and the next time someone opens the database, Access will hide the Database window. Users won’t have immediate access to any objects.
  • Use Access Special Keys: Deselect this option to inhibit the use of F11 to unhide the Database Window.

Figure A

Set Startup options to hide the Database window.

Both settings work together. If you don’t deselect the Use Access Special Keys option, users can press F11 to unhide the Database window.

To access the Startup options, choose Startup from the Tools menu. In Access 2007, click the Office button and then click the Access Options button. Select Current Database in the left pane and you’ll find these options in the Application Options section. Access 2007 doesn’t have a Database window, but you can hide the Navigation Pane in a similar manor. That option is in the Navigation section just below the Application Options section.

Deselecting the Display Database Window option will also disable the Startup command. Users can bypass all these options by holding down the [Shift] key while opening the database. That trick’s handy for you, but it leaves the database vulnerable to anyone else who knows about it. A user can also import objects into a blank database to bypass startup settings. Luckily, there’s a bypass to the bypass.

Bypass the bypass

To close the bypass crack, set the AllowBypassKey property to False when the database closes. You can automate this process by calling the following code from a close task — just which task is up to you:

Public Sub SetStartupOptions(propname As String, _

 propdb As Variant, prop As Variant)

  'Set passed startup property.

  Dim dbs As Object

  Dim prp As Object

  Set dbs = CurrentDb

  On Error Resume Next

  dbs.Properties(propname) = prop

  If Err.Number = 3270 Then

    Set prp = dbs.CreateProperty(propname, _

     propdb, prop)

    dbs.Properties.Append prp

  End If

  Set dbs = Nothing

  Set prp = Nothing

End Sub

When you call the procedure, be sure to pass the appropriate startup option text, as follows:

Call SetStartupOptions("AllowBypassKey", dbBoolean, False)

After setting this property during the close process, the database will ignore the [Shift] key bypass if one of your users is wily enough to try it.

You can use this approach to set any of the startup properties. For instance, this call hides the Database window:

Call SetStartupOptions("StartupShowDBWindow", dbBoolean, False)

You can set options when you close or open the database with one exception. The AllowBypassKey property must be set when you close the database. Be sure to set a reference to the Data Access Objects library (DAO). Otherwise, this procedure will generate a reference error. (There’s bound to be an ADO alternative, but DAO is efficient in this area.)

It makes sense that anyone who knows about the [Shift] key bypass might also know how to enable the [Shift] bypass by resetting the AllowBypassKey property to True. If this is the case, you’ll have to apply workgroup security to restrict access to this property to the administrator. Someone can try to reset the property, but the effort will fail unless that person is working through the administrator login.

Miss an Access tip?

Check out the Microsoft Access archive and catch up on other Access tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.