Programming the Startup Options | Real World Microsoft Access Database Protection and Security

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 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 StartupShowDBWindow Startup 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 frmStartupProperties form. 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 subroutine 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:

Secure your database by using workgroup security (discussed in Chapter 8) or run the User-Level Security wizard.
Users must not have Administrator database permission for their workgroup 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 Administrator 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 frmStartupProperties form 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:

Open the frmStartupProperties form.
Select or clear the startup options as appropriate.
Select the Administer Only check box and click Yes to confirm that you want to protect the options.
To remove DDL protection, clear the Administer Only check box and click Yes to confirm once again.
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.