Real World Microsoft Access Database Protection and Security

 

This article is an excerpt from Real World Microsoft Access Database Protection and Security from APress (ISBN 1-59059-126-7*, copyright APress 2003; all rights reserved). The author, Garry Robinson, established a successful software consultancy business called GR-FX Pty Limited that has been in business for eight years. During that time, he has helped to develop and supported over 100 Access applications for over 30 organizations. His clients include mining, insurance, and transport businesses. He has assisted quite a number of Access programmers working for their clients. Garry is a contributing editor to Pinnacle Publishing's Smart Access magazine and is an editor of the popular programming Web site* http://vb123.com. He develops and sells shareware software for data mining and Access application libraries, and a programmer's knowledge base. When Garry's not sitting at the keyboard, he can be found playing golf or swimming with the sharks in Sydney, Australia.

No part of these chapters may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.

Chapter 2: Protecting Your Database with Startup Options

Contents

Chapter 2: Protecting Your Database with Startup Options
Do I Need to Read this Chapter?
Manually Changing Startup Options
Defining Your Startup Display Form
Displaying the Database Window
Options that Require Custom Menu and Toolbar Development
Allowing Toolbar and Menu Changes
Using Access Special Keys
The Startup Options Demonstration Form
Manipulating the Startup Properties by Using VBA Code
Preventing End Users from Changing the Startup Properties
Applying DDL Protection
The Bypass Key Wrap Up
Further Reading
Reflecting on this Chapter
What's Next?

IF THERE'S ONE CHAPTER in this book that will help you protect your database, this is it. The startup options are simple and important safeguards that will hide the Database Window and Access programming commands and disable special keys used to access the developer's environment. These options apply to the current database and apply the next time the database is opened.

The low-risk steps required to modify the database startup options with menus are quick and easy. Once you've turned off the options, the data and application objects are less accessible to the average user. For the user, some of these changes provide a less-cluttered interface that should make your application easier to use.

The demonstration material for this chapter includes the following:

• An Access form that allows you to change the startup options. This form is very useful when the menu item to display the Startup dialog is disabled.
• Visual Basic for Applications (VBA) code within that Access form that will show you how to change the startup options.
• VBA code within that Access form that will disable the AllowBypassKey property, an option that is not available from the menus. This code ensures that the user cannot change the startup options.
• Accelerator keys that you can use when the developer menus and toolbars are disabled.

NOTE   To find the demonstration material, open the download database for your version of Microsoft Access — for example, grMAP97.mdb — and select Chapter 2.

I'll begin the chapter with an overview of the startup options.

Do I Need to Read this Chapter?

In this and the following chapters, I'm going to give three separate overviews — one for developers, one for database administrators (DBAs), and one for IT managers because the people in each of these categories will approach the chapter in a different way. Developers will want to find ways to use the startup options to protect the database, DBAs need to understand how startup options will affect the database, and IT managers should be aware of how the startup options might affect their companies' business.

Overview for the Developer

It's difficult enough for a programmer to provide a system that works according to users' requirements. When the end users take it on themselves to alter the database, however, certain issues arise:

• Who has the latest version of the database?
• What has changed?
• How do we integrate those changes with our latest modifications?

Turning off the startup options manually or with VBA code will assist in reducing these development issues. If you also set the AllowBypassKey property to False with VBA code, users will find it difficult to view the database container, change toolbars, or skip the startup display form and the AutoExec macro. Because the impact of changing these settings will change the way that you work with your database, you should become conversant with how to turn these options on and off manually and with VBA code.

For those programmers who are looking for something simple that will add value to their databases, you can import a demonstration form into your database that will immediately allow you to view and switch all the startup options. This form is very helpful, as some startup options will remove the menu options that allow you to undo your changes.

Overview for the Database Administrator

Understanding the startup options is vital if you're to administer Microsoft Access databases. At some stage you, the developer, or your users are going to change the startup options. Once that happens, you'll need to work out how to navigate to all the objects in the database with those new options in place. More importantly, you can easily change the options manually or by using the startup options demonstration form. This form will help you make appropriate changes to the startup options so that users will be hard-pressed to use the customization features that are available to the developer.

Overview for the IT Manager

If you have any concerns about users modifying your database objects and data or avoiding the user interface that you intended them to use, then this chapter is important. You should discuss the startup options with your developer and even adopt a standard group of startup options that you apply to all your databases. When you are considering if there is any risk with your developers providing you with software that has unwanted and undocumented security, the only risky startup option is if the developer has set the AllowBypassKey property to False by using VBA code. In this case, you may have difficulty opening and editing the database objects. This problem may arise if the relationship between your developer and your company goes sour.

Manually Changing Startup Options

To change the startup options in your database manually, choose Tools | Startup, as shown in Figure 2-1, and you will see the options form, shown in Figure 2-2.

Figure 2-1. Opening the startup options on the Tools menu

Figure 2-2 shows all the startup options in Access 97 (with the Advanced button selected). In Access 2000 and later, this Advanced button does not appear. If you want to experiment with these options for the first time, then I suggest you experiment with a copy of the demonstration database for this chapter.

CAUTION   If you're going to experiment on your own database, please be sure to make a full backup first, as options like the AllowBypassKey require a little practice to undo.

Figure 2-2. The startup options available for Access 97

Now I will tell you to add a startup form to your database, something that you more than likely did on the first day that you started developing your database.

Defining Your Startup Display Form

When users open your database, they should be greeted with a form that helps them to navigate to your database. If the database doesn't have a startup form, the first thing users will see is the database window. Clearly, this window could over-whelm some users, as it is similar to driving in a foreign city with no road map. Subsequently, if you are intent on hiding the database window from users, the users will be in a desert with no map. Therefore, let us add a startup form to the database. One form that you will probably recognize is the startup form that has welcomed users to the Northwind sample database ever since Access 97 (see Figure 2-3).

Figure 2-3. The Main Switchboard startup form in the Northwind sample database

When looking at the design of the Northwind startup form, it's clear what options the user can use to interact with the database. One thing that we aim to achieve with the protection and security discussed in this book is to put in place a framework that ensures that users will interact with the database in the way that we intended.

Displaying the Database Window

If you select the Display Database Window check box in the startup options, the database window (shown in Figure 2-4) will be available to all users. If the users are meant to use only the user interface, exposing the Database window will probably confuse them or, worse, may allow them to modify or delete any object in the database without authority. It is good to know that if you disable this option, you can display the Database window by pressing the F11 key.

Figure 2-4. The Database window makes the data and objects easily accessible to everyone

User Story   I personally have sent at least 50 databases to clients with just the Show Database Window option cleared. In all that time, I have yet to run into any users who have maliciously tampered with the database. Occasionally, some users have modified objects while I was developing, but that is rarer than you think. That said, in a number of the database applications that I have delivered, if it was important that the users didn't change anything, we modified a number of the startup options as the first line of protection. The main person these changes seemed to affect was the developer (me). I quickly learned how to change the startup properties in code so that I wasn't inconvenienced too much.

Options that Require Custom Menu and Toolbar Development

In Chapter 7, you can read in detail about how menus are an important part of protecting your Access interface. In this chapter, however, I will give a summary of the following menu-related startup options:

• Menu Bar: Display your own menu when Access starts.
• Shortcut Menu Bar: Display your own default shortcut (right-click) menu on all the forms and reports.
• Allow Full Menus: When you select this option, users will see all the developer-related menu items. When it's disabled, users will see a simpler menu.
• Allow Built-In Menus: When disabled, the user will not see the Microsoft Access built-in toolbars. You will generally use this option for custom toolbars that you need to allocate to each form and report in your database.
• Allow Default Shortcut Menus: When disabled, the user will not see the built-in Access shortcut menus. You will use this option sometimes for custom shortcut menus that you need to allocate to each form and report in your database.

As a part of this discussion, you will learn that custom menus are an important protection and user interface topic because the alternative, standard menus, provide the user with access to the same tools to change the database that are available to the developer.

Allowing Toolbar and Menu Changes

Selecting this startup option will allow the users to make changes to all the toolbars, menu bars, and shortcut menus. As a consequence of selecting this startup option, users will have easy access to the full repertoire of Access menu options, as shown in Figure 2-5.

Figure 2-5. Selecting the AllowToolbarChanges option will give your users access to more options

Clearing this option anchors your toolbars, disables the right-click modifications to toolbars, stops the closing of toolbars, and removes the menu option (View | Toolbars). In summary, turning this feature off should not affect users, and it will make it easier for you to maintain a standard interface for all users.

Using Access Special Keys

The Use Access Special Keys option is important because it allows users to use all the special key sequences that enable them to get to your normal programming environment. By clearing this option, you and your users will not be able to use the following special key sequences:

• F11 To bring the database window to the front (see Figure 2-4).
• ALT+F11 To open the Visual Basic Editor (VBE) (Access 2000 or later).
• CTRL+F11 To toggle between custom and normal toolbars.
• CTRL+BREAK To show the current point of execution of the VBA software or stop retrieving records from a slow query.
• CTRL+G To view the debugger window. In fact, clearing this option will stop anyone from using the Debugger, a point discussed further in Chapter 4.

CAUTION   If you are thinking of clearing this option, remember that this option has significance only if you have also cleared the Display Database Window option.

Now we will find out how we can program the startup options by using VBA code.

Programming the Startup Options

Selecting and clearing the startup options manually will suffice for a good percentage of Access databases. However, there are occasions when you will want to check and change the options by using a customized form or with VBA code, such as:

• When you want secret and faster ways to select the startup options again.
• When the Startup dialog is not available from the menus.
• When you have disabled the bypass key (discussed later in this chapter) and you need to enable it again.

NOTE   The Access help guide switches terminology from referring to "startup options" as "startup properties" in the VBE help guide. The Access guide makes this change because when you deal with a startup option through VBA, you are actually dealing with a property of the database. This chapter adopts the same convention.

To manipulate and interrogate the startup properties of the database by using VBA code, I've prepared two easy-to-use functions called StartUpProps and DeleteStartupProps. These functions are stored within a simple wizard-like form called frmStartupProperties. You will find this form in the download database.

User Story   Since I put this form together, I have found it quite useful to include in databases in which I am constantly changing the startup options. I like it because it provides a safe and simple way to return the options back to normal. After including the form, I have also found it useful to retrieve this form by using accelerator keys. You can read about this in detail at the end of the chapter.

The Startup Options Demonstration Form

The frmStartupProperties demonstration form is shown in Figure 2-6. When this form opens, it first checks the status of the database startup options and displays the results in a series of check boxes. If a check box is not available (appears gray) like the Allow Bypass Key check box in Figure 2-6, then you or your users have not set the property, and Access will use its default value for the property. If you are interested in protecting your database, you will be disappointed because the startup options are enabled by default.

Figure 2-6. The frmStartupProperties enables you to change the startup settings in code

This full list of startup properties are relevant to Access protection:

• Display Form
• Display Database Window
• Menu Bar
• Shortcut Menu Bar
• Allow Full Menus
• Allow Built-In Toolbars
• Allow Default Shortcut Menus
• Allow Toolbar/Menu Changes
• Allow Viewing Code after Error
• Use Access Special Keys
• Allow Bypass Key

These properties are not illustrated in the demonstration form:

• Application Title
• Application Icon
• Display Status Bar
• Menu Bar
• Shortcut Menu Bar

If you want to work through the examples in the demonstration form, open the form and you will find that these buttons on the form execute the following actions. If you want fast access to the VBA code behind the button, select the View Code check box at the top of the form before you click the button. Remember that when you have finished testing the startup options, return to this form in the demonstration database, select all the check boxes, and click the Finish button.

The Refresh Button

The code in the Refresh button's onClick event looks at the status of the database startup properties and changes all the check boxes to match. To change the onClick event, we use the function called StartupProps that you will find in the demonstration form. The following code snippet illustrates the VBA code that retrieves the current value of the StartupShowDBWindow property and displays it in the appropriate check box:

Me!chkShowDBWindow = StartUpProps("StartupShowDBWindow") 

The Finish Button

The code in the Finish button's onClick event takes the values from the check boxes in the body of the form and modifies the database startup properties to these new values. When this process is complete, the form closes and the Startup dialog box appears, which allows you to see if your changes were successful.

The following code illustrates the VBA code that sets the StartupShowDBWindowStartup property to the value in the appropriate check box:

StartUpProps "StartupShowDBWindow", Me!chkShowDBWindow 

If you look carefully at the two ways that I have called the StartUpProps function, you will see that it allows us both to retrieve and to change the values of a startup property, depending on whether we include the new value in the second argument of the function call.

The Cancel Button

The Cancel button ignores any changes that you made and closes the form.

TIP   To display the VBA code under the button, open the form in Form view, select the View Code check box (shown in Figure 2-6), and click the button. To look at the contents of a subroutine or function (like StartUpProps), right-click its name in the VBE and choose Definition from the menu or press SHIFT+F2.

Now I will show you the VBA subroutines that make this form tick.

Manipulating the Startup Properties by Using VBA Code

The procedures that are required to view and change the database's startup properties are a little involved. Their relative complexity arises from the fact that you need to assemble some code that manipulates database properties. This involves code to

• Change the value.
• Add the property if it doesn't exist.
• Delete the property.
• Handle subtleties due to the different data types of different properties.

To satisfy these programming requirements, you are required to put together subroutines, something that I have already done for you. After you have them running to your satisfaction, you should not have to look at them again for a long time. So let's look at the code to find the startup properties. I simplified this code from a sample included in the Access 97 help file by making the arguments call the subroutine specific to the data types found in startup properties.

NOTE   Access 97 help is much more thorough on this topic than the later versions of help due to its emphasis on the Data Access Objects (DAO) library. Access 2003 help seems to redress this issue to some extent.

You will find the relevant code by exploring the VBA code from the Refresh and Finish buttons' onClick events on the frmStartupPropertiesform. This programming example uses the DAO library, which is the only way to modify these Access Jet engine-specific properties.

NOTE To add a reference to DAO while programming a form, open the form in Design view, choose View | Code, and you will now be in VBE. Now choose Tools | References and select the check box next to either Microsoft Access DAO 3.5 or 3.6 Object Library.

Function StartUpProps(strPropName As String, Optional varPropValue As Variant, _
  Optional ddlRequired As Boolean) As Variant 
' This function requires a reference to DAO library. 
' This function will both return and set the value of startup properties 
' in your database. It can also be used for other database properties 
' with some slight modification.
 
Dim dbs As DAO.Database, prp As DAO.Property, varPropType As Variant 
Const conPropNotFoundError = 3270
 
If IsMissing(ddlRequired) Then 
   ddlRequired = False 
End If
 
' Because this code is specific to the startup properties, we assume that the 
' data type of the property is Boolean unless stated otherwise. 
varPropType = dbBoolean
Select Case strPropName
   Case "StartupForm"  
      varPropType = dbText
End Select
Set dbs = CurrentDb
 
' This function will either set the value of the property or try to 
' return it. It knows which mode it is in by the existence of the 
' property value in the procedure that called the function.
 
If Not IsMissing(varPropValue) Then
 
' As we change the value of the startup property, we will first try to 
' assign that value. If the property does not exist, it will be 
' added to the database object by using the following error handling code. 
   On Error GoTo AddProps_Err
   dbs.Properties(strPropName) = varPropValue
   StartUpProps = True
Else
' If we find out the value of the startup property, we first see if
' that value exists. If the property does not exist, we will return a null string.
   On Error GoTo NotFound_Err
   StartUpProps = dbs.Properties(strPropName)
End If

StartupProps_End:
   On Error Resume Next
   Set dbs = Nothing
   Set prp = Nothing
Exit Function

When a property doesn't exist in the database, you must use the CreateProperty method to add the property to the database. The error handling section of the sub-routine handles this method as follows:

AddProps_Err:
 
   If Err = conPropNotFoundError Then  
      ' Property not found when adding a property value.  
      Set prp = dbs.CreateProperty(strPropName, varPropType, _
          varPropValue, ddlRequired)  dbs.Properties.Append prp  
      Resume Next
Else  
   ' Unknown error.  
   StartUpProps = False  
   Resume StartupProps_End
End If

The final section of error handling handles instances where the function is searching for an existing property value and none exists. In this case, the StartupProps function will return a null value as this makes it simpler to set the value of a check box.

NotFound_Err:
   If Err = conPropNotFoundError Then
      ' Property not found when returning a property value.
      StartUpProps = Null
      Resume Next
   Else    
      ' Unknown error.    
      StartUpProps = False    
      Resume StartupProps_End
   End If
 
End Function 

If you were looking at the detail of this subroutine, you would have seen a mysterious variable called ddlRequired. In the next section, I will explain why and how you can use this variable to make your startup properties extremely hard for your smart users to change.

Preventing End Users from Changing the Startup Properties

Access offers an additional safeguard for the startup options for those databases that are protected through workgroup (user-level) security. This protection is provided by an oddly named protection mechanism called Data Definition Language (DDL). In a nutshell, this DDL protection will stop any person who doesn't have Administrator permissions for the database itself from changing the startup options by the menu or by VBA code.

Therefore, if you are thinking that this sounds interesting and you might like a piece of this additional security, then you first must complete the following:

1. Secure your database by using workgroup security (discussed in Chapter 8) or run the User-Level Security wizard.

2. Users must not have Administrator database permission for their work-group user account or any group account of which they are a member. Figure 2-7 illustrates how the database permissions should look for the users group.

   

   Figure 2-7. The users group does not have Administrators permission for the database

NOTE   If you don't understand steps 1 and 2 or you haven't secured your database as yet, it is probably best that you skip through the chapter until you get to the section The AllowBypassKey Property. I will remind you in Chapter 8, when I have discussed workgroup security, that you should to return to this point.

Now I will discuss one last reason not to get too involved in DDL protection before I describe how the frmStartupPropertiesform will apply the DDL protection for you.

DDL Protection for Startup Options for Workgroup-Secured Databases

Before we head to the technical discussions on DDL protection, there is just one last thing to discuss before you make up your mind that you really need some of this DDL security. Surprisingly, DDL security is one of the few Access protection mechanisms that is turned on by default. So, before you even worry about DDL security for your database, you should test it by logging on to your database by using an end user's workgroup account. If you find that the user account can actually modify the startup options by using the menu Tools | Security, then you will need to use the DDL property protection. Conversely, you may actually want people who do not have administrator permission for the database to change the startup options. If that is the case, you need to remove the DDL protection.

To add or remove DDL protection for the startup options, I have setup an Administrator Only check box on the frmStartupProperties demonstration form. To use it, follow these steps:

1. Open the frmStartupProperties form.
2. Select or clear the startup options as appropriate.
3. Select the Administer Only check box and click Yes to confirm that you want to protect the options.
4. To remove DDL protection, clear the Administer Only check box and click Yes to confirm once again.
5. Click the Finish button to change the startup options. You only have to do this procedure once as long as you select or clear all the options.

Now if you are a hard-nosed programmer, I have included for you in the next section the technical details on the code that will allow you to apply and remove DDL protection for the startup options.

The Software Used For DDL Protection for Startup Options

Whenever you change any of the startup (and other database) properties by using VBA code, you can prevent users from changing the database startup properties by themselves. This security restriction even applies when the Tools Startup menu is available to users. When you apply DDL protection, only workgroup accounts that have administrator permission for the database (obtained through workgroup security) will be able to change the value of the property.

In the following code, you will see how to stop workgroup users from making toolbar changes in the database. To accomplish this change, I have used the StartUpProps function that I discussed earlier in the chapter. In the example, you will notice that there is now an additional fourth argument (called ddlRequired) that is set to True.

StartUpProps "AllowToolbarChanges", False, True 

To demonstrate how this change works in the StartUpProps subroutine, I repeat the lines of code that use the CreateProperty method, as follows. Note that I have used the optional ddlRequired argument in this instance.

Set prp = dbs.CreateProperty(strPropName, varPropType, _ 
   varPropValue, ddlRequired)  
dbs.Properties.Append prp
 

NOTE   In Access 97 help, this optional Data Definition Language (DDL) argument is described as "A Variant (Boolean subtype) that indicates whether or not the Property is a DDL object. The default is False. If DDL is True, users can't change or delete this Property object unless they have dbSecWriteDef permission." This argument is not described in the any of the help files in Access 2000 and later because DAO information was removed from the help files.

There is a trick when using VBA code to add the DDL argument to the startup properties. First, you must delete the property before you add it with the new security setting, which you do in the demonstration form by calling the deleteStartupProps subroutine:

deleteStartupProps "StartupShowDBWindow" 

Now I will show you the deleteStartupProps subroutine so that you can see how a database property is deleted. This process is complicated by the fact that the property may not exist in the first place and therefore this needs to be handled by error-trapping code.

Function deleteStartupProps(strPropName As String) As Boolean 
' Function requires a reference to DAO library.
 
Dim dbs As DAO.Database, prp As DAO.Property 
Const conPropNotFoundError = 3270
 
deleteStartupProps = False 

On Error GoTo deleteStartupProps_Err 
CurrentDb.Properties.Delete (strPropName) 
deleteStartupProps = True 

deleteStartupProps_End: 
   On Error Resume Next 
   Set dbs = Nothing 
   Set prp = Nothing 
   Exit Function
 
deleteStartupProps_Err: 
   If Err = conPropNotFoundError Then   
      ' Property not found.  
      deleteStartupProps = False  
      Resume Next
   Else  
      ' Unknown error.  
      Resume deleteStartupProps_End
   End If

End Function
 

That ends the technical coverage of the DDL protection for startup options. To summarize, remember that workgroup security, when properly applied, will more than likely provide this protection. Once it is in place, test your workgroup user accounts to ensure that they cannot change the options. If they can change the options, select the Administrator Only check box in the frmStartupProperties form before modifying the options. Most users will never get this far anyway!

Now I will describe how to stop people from using the bypass key, commonly known as the SHIFT key, to sneak into your database. I can guarantee that a few users will be up to speed on this trick.

The AllowBypassKey Property

The bypass key (SHIFT key) sequence is a sneaky little Access trick that has been around since Noah kept a database of animals on his ark. Once you know the SHIFT key sequence, you will be able to use it for all versions of Access. Though it's disappointing that anyone can sidestep security so easily by using the SHIFT key, once I have demonstrated how it actually works, I will show you how to disable it.

The Access help guide describes the bypass key as follows: "You can use the AllowBypassKey property to specify whether or not the SHIFT key is enabled for bypassing the startup properties and the AutoExec macro. For example, you can set the AllowBypassKey property to False to prevent a user from bypassing the startup properties and the AutoExec macro."

The best way to illustrate what effect the bypass key will have on your database is to walk through an example, using the Northwind sample database.

1. Open Microsoft Access.

2. Choose File | Open.

3. Navigate to Northwind.mdb in the samples subdirectory of your Access installation directory (see Figure 2-8).

4. Select the Northwind.mdb file, and click the Open button, and you will find yourself using the Main Switchboard form.

   

   Figure 2-8. Opening the Northwind database

5. Exit Access.

6. Open Access.

7. Choose File | Open. Now navigate back to Northwind.mdb.

8. Select the file name.

9. Press SHIFT and click the Open button.

This time the database will open a lot faster, the database window will be visible, and the Main Switchboard form will not open at all. In addition, the AutoExec macro will not run either, so any special startup sequences that you have built into your database will not be triggered. You can repeat this trick by pressing SHIFT when using Windows Explorer or even in the Most Recent Used List on the Access menus to open a database.

NOTE   Unless the AllowBypassKey property has been set to False, you can bypass the startup options in any Access database by pressing SHIFT in Windows Explorer and then double-clicking the database file. This is a good way to view the hidden database window, turning on the special keys and avoiding customized menus. Your users can also do this, so you may want to think about invoking this extra bypass key protection just before you deliver your software.

CAUTION   It's now time to back up your database, because it can be a bit tricky if you do not leave the VBA code somewhere accessible in your database so that you can change the AllowBypassKey property back to True.

Let's look at how we can use the frmStartupProperties form to set the AllowBypassKey property to False.

1. Open this book's demonstration database for your version of Access.
2. Select Chapter 2 and select frmStartupProperties.
3. Because we are testing the process, select the Display Database Window check box just in case we have to return to frmStartupProperties from the Database Window. This form provides a safety net because it will allow us to reset the AllowBypassKey property back to True.
4. Clear the Allow Bypass Key check box.
5. Click Finish.
6. Check the startup options, which won't show the AllowBypassKey property.
7. Close the database and then try to open it while pressing SHIFT. You will find that you won't be able to—you have turned it off.

Now there is one last step for those who just must have the best protection for their startup properties.

Applying DDL Protection

Finally, if you consider that one of your users may understand how to enable the AllowBypassKey option by using VBA code and you have used workgroup security, you can use the DDL protection to limit this modification to database administrators only. This limitation is possible by using the optional fourth argument (ddlRequired) of the StartUpProps subroutine. Remember that before we set the DDL property, we first must delete the current setting of the property, as demonstrated by the following code:

DeleteStartupProps "AllowBypassKey"
StartUpProps "AllowBypassKey", False, True

What this code means in real words is to test whether a workgroup user can use the frmStartupProperties form to turn off the AllowBypassKey property. If they can, then use the Administrator Only check box on the form to lock it.

The Bypass Key Wrap Up

If your database relies only on the startup properties to protect the data and the objects, then you probably want to make sure it is as safe as possible. A user who knows how to press SHIFT (the bypass key) when opening a database can easily modify or delete any object in the database, copy those hidden tables that the developer tried to hide, or simply enter the data directly into the tables and avoid all the form-based data rules. If you think that your users will never find out about the SHIFT key, try typing Startup into your Access help and the instructions for "Ignore startup options" will figure very prominently. So, do you want to trust your database protection to a SHIFT key?

The final word on this topic is that you may want to look to the end of Chapter 6, where I show you a tool that will locate people who have connected to your database without using your required startup form. Now I will show you how special keys can make using the startup options a little easier for the developer.

Using Homemade Accelerator Keys in Restricted Environments

Now that you've gone to all the trouble of disabling a combination of the database window, menus, toolbars, shortcut menus, and the special keys from the database, you'll probably find that it's difficult to develop and administer your database. To assist you with these tasks, I have deliberately set up the frmStartupProperties form in such a manner that you can easily use it to modify the startup properties in your database (and modify them back again). You can augment this form by allocating your own accelerator key sequences to open this form. Another accelerator key sequence that is described below will allow you to open any form or report in the database in design view without having a design view menu or toolbar option. To do this, build a macro that stores all your database accelerator keys:

1. Open the database window.

2. Select Macros.

3. Create a new macro (unless you already have an Autokeys macro).

4. Choose View | Macro Names.

5. In the macro name column, enter ^ Q (see Figure 2-9).

   

   Figure 2-9. Setting up the Autokeys macro to make getting around Access a little easier

6. In the Action column, select OpenForm.

7. Select the frmStartupProperties form in the Action Arguments pane (near the bottom).

8. Now start a new line and enter ^ D into the Macro Name column.

9. Enter RunCommand in the Action column.

10. Select DesignView in the Action Arguments pane.

11. Now save the macro and call it Autokeys.

NOTE   Accelerator keys are stored in the database in a macro that is always called Autokeys. From now on, you can type the following two key sequences: CTRL+Q to open the frmStartupProperties demonstration form and CTRL+D to open any object in design view. You can even add triple key sequences like CTRL+SHIFT+X if you want extra security on these key sequences.

Now if you want to use accelerator keys to open the frmStartupProperties demonstration form in your database, all you need to do is import the form and the Autokeys macro into your database. If you set the AllowBypassKey property to False, you can still open the frmStartupProperties startup options form by using the CTRL+Q key sequence. Test the accelerator keys and demonstration form before setting the AllowBypassKey property to False.

Further Reading

As you might expect, there is never enough information when it comes to powerful products like Microsoft Access. To assist you with further investigations, I have put together a Web page with hyperlinks to Web sites and articles on the issues relating to the material in this chapter. This page includes

• A Microsoft page that shows how to use startup options.
• Discussion group comments on the AllowBypassKey property.
• Alternative approaches to disable the important special key sequences.
• Discussions on accelerator keys.

You can find the further reading Web page for this chapter in the Downloads section of the Apress Web site (http://www.apress.com) or at the following address: http://www.vb123.com/map/sta.htm.

Reflecting on this Chapter

First, you have seen how easy it is to change the startup options in your database by using Access menus. If you do use the menus, you will make it reasonably hard for the majority of users to get up to much mischief in your database. Unfortunately, every time you change a startup option, you make it harder for the developer to make changes to the software and objects in the database, which means that the additional security starts to add to the development costs. Therefore, I suggest that you experiment with each of the properties one at a time and see what effect it has on both your application and your development productivity.

Now that you know how to change the startup properties by using VBA code, you will find that you can enable the options for development and disable them for end users. In addition, another benefit of using VBA code is that you can disable that (not so clever) bypass key that allows people to use the database as they see fit. You can also combine workgroup security and VBA code to make sure that only users who have administration privileges in your database can change the startup options.

Finally, if you find that you would like the additional protection for your database and really haven't the time to research these issues in detail, you can import the demonstration form and Autokeys macro into your database. These tools give you a way to change the startup options all the time, regardless of the changes that you make. This approach provides secrecy, flexibility, and a good safeguard with little of the programming effort. That's my idea of low-cost security.

What's Next?

In the next chapter, I will take you through many of the database options that affect your databases' security and stability. Once again in the chapter, I will demonstrate how to make changes manually and then how to make the changes programmatically. In addition to these database options, I also expand on the manual and VBA code techniques that will allow you to hide objects from your users. Though these subtle vanishing tricks, coupled with the startup options described in this chapter, are not foolproof, they certainly can keep the casual software saboteur or data nomad from wandering into places in your database that they shouldn't.

And, as a smart computer systems manager told me once, you can achieve 80 percent of the gain with 20 percent of the effort. He always left the office on time and drove a good company car, so I guess he knew what he was talking about! The same principle applies to database startup properties and options because these two chapters show you techniques that take very little time and achieve quite a good deal of protection for your database.