Protecting Your Application

There are several reasons why it may be important to protect the data and features in an application that you develop. For example, your application may use objects that took you a great deal of time to design. Perhaps you or the users of your application need to control access to sensitive information. Or, to reduce maintenance for your application, you may want to prevent users from inadvertently breaking your application by changing code or objects on which the application depends. Consider implementing some form of security to control what a user, or group of users, can do with the objects and data in your application.

The most powerful and flexible method of protecting your application is called user-level security. However, establishing user-level security is a complex process and it may exceed your situation’s requirements. As alternatives to user-level security, Microsoft Access provides the following methods you can use to protect your application:

Using startup options to restrict access to default menus and toolbars, the Database window, and special keys.
Establishing a password to control opening the database.
Saving your application as an MDE file to remove Visual Basic source code and prevent changes to the design of forms, reports, and modules.

Microsoft Access also provides database encryption that you can use to prevent unauthorized users from viewing the objects in your application with a disk editor or other utility program. You can use encryption in conjunction with all methods of protecting your application.

The strategy you use depends on the extent of security you need and how your application will be used. The following sections discuss these options.

Using Startup Options

In a casual environment where true security isn’t required, you can use startup options to restrict access to default menus and toolbars, the Database window, and special keys. To perform the following procedure, your application must have a startup form and a custom menu bar that contains only the commands you want available.

See Also For information on creating a startup form, see “Setting Startup Options” in Chapter 1, “Creating an Application.” For information on creating a custom menu bar, see “Creating New Menu Bars, Toolbars, and Shortcut Menus” in Chapter 1, “Creating an Application.”

Û To use startup options to protect your application

On the Tools menu, click Startup.
In the Display Form box, click the name of your startup form.
In the Menu Bar box, click the name of your menu bar.
Click Advanced to display the rest of the dialog box.
Clear the following check boxes: Allow Full Menus, Allow Default Shortcut Menus, Display Database Window, Allow Built-in Toolbars, Allow Toolbar/Menu Changes, and Use Access Special Keys.
In Visual Basic, set the AllowBypassKey property to False. This prevents users from using the SHIFT key to bypass the settings in the Startup dialog box.
See Also For information on how to set the AllowBypassKey property, search the Help index for “AllowBypassKey property.”

A user can bypass this method of security by setting the database’s AllowBypassKey property back to True, but that user would need to understand how to create the appropriate line of Visual Basic code to do so.

Setting a Database Password

Adding a database password is an easy way to prevent unauthorized users from opening your application. Use this approach when you need to control which users can open your application, but not what they do after providing the correct database password.

Û To set a database password

Close the database. If the database is being shared on a network, make sure no other users have it open.
Make a backup copy of your database and store it in a secure place.
On the File menu, click Open Database.
Select the Exclusive check box, and then open the database.
On the Tools menu, point to Security, and then click Set Database Password.
In the Password box, type the password. Passwords are case-sensitive.
In the Verify box, type the password again to confirm it, and then click OK.

The password is now set. Each time a user tries to open the database, a dialog box appears that requests the database password.

Caution Write down your database password and keep it in a safe place. If your database password is lost or forgotten, you won’t be able to open your database or retrieve the information it contains.

Do not use a database password if you plan to replicate the database. You can’t synchronize a replicated database if a database password has been set. Defining user-level security permissions doesn’t interfere with replica synchronization. For more information on database replication, see Chapter 20, “Using Replication in Your Application.”

When you set a database password, the Set Database Password command changes to Remove Database Password. To clear a database password, click Remove Database Password on the Security submenu (Tools menu), type the correct password in the Password box, then confirm it in the Verify box.

Important Considerations When Using a Database Password

Take the following items into consideration when using a database password:

Setting a database password doesn’t prevent someone from using a disk editor or other utility program to read your data without opening the database. To prevent this as well, encrypt your database.
See Also For more information on encrypting a database, see “Encrypting a Database” later in this chapter.
Any user who knows the database password and has access to the Remove Database Password command can change or clear the password, unless you remove the Administer permission on the database for all users and groups except the database administrator. Similarly, if you want to prevent users from setting a password on a database, remove the Administer permission on the database for those users or groups. By default, the Users group, the Admins group, and the creator (owner) of the database all have Administer permission on the database.
See Also For more information on permissions, see “Permissions” later in this chapter.
If you use a password to protect a database containing tables that are linked to another database, Microsoft Access stores the password when the link is established. This means that any user who can open the database that contains the link can open the linked table. Additionally, Microsoft Access stores the password in an unencrypted form. If this will compromise the security of the password-protected database, you should not use a database password to protect the database. Instead, you should establish user-level security to control access to sensitive data in that database.
See Also For more information on user-level security, see “Microsoft Access User-Level Security” later in this chapter.

Using Data Access Objects to Work with Database Passwords

As an alternate to using the Microsoft Access user interface to work with database passwords, you can use Visual Basic code to manipulate the Data Access Objects (DAO) that control them.

Important If you open a database with code and a user later sets a database password without you being aware of it, your code will no longer be able to open the database. You should always check to see if your attempt to open a database has succeeded. Your code should provide error handling in case it encounters a password-protected database.

See Also For information on preventing users from setting database passwords and other considerations when using a database password, see “Setting a Database Password” earlier in this chapter.

Setting a Database Password in Code

A database password can be set by using DAO code with the NewPassword method. The following code opens the Orders sample database and assigns a password to it:

 Sub SetDBPassword()
	' Declare database variable.
	Dim dbs As Database

	Const conFilePath = "C:\Program Files\Microsoft Office\Office\Samples\"

	' Open database for exclusive access.
	Set dbs = OpenDatabase(conFilePath & "Orders.mdb", True)

	' Set database password and close database.
	dbs.NewPassword "","topsecret"
	dbs.Close
End Sub

Opening a Password-Protected Database

After a database password has been set, all attempts to open that database fail unless the correct password is given. To open a password-protected database using DAO code, you specify the password as part of the source argument. For example, the following code opens the password-protected Orders sample database:

Sub OpenProtectedDB()
	' Declare database variable.
	Dim dbs As Database

	Const conFilePath = "C:\Program Files\Microsoft Office\Office\Samples\"

	' Open database for shared, read/write access, and specify database password.
	Set dbs = OpenDatabase(conFilePath & "Orders.mdb", False, False, ";pwd=topsecret")
End Sub

Changing a Database Password

To change a database password, you must supply both the existing and new passwords to the NewPassword method. The following example changes the password of the Orders sample database from “topsecret” to “ultrasecret”:

 Sub ChangeDBPassword()
	' Declare database variable.
	Dim dbs As Database

	Const conFilePath = "C:\Program Files\Microsoft Office\Office\Samples\"

	' Open database for exclusive, read/write access, and specify
	' current database password.
	Set dbs = OpenDatabase(conFilePath & "Orders.mdb", True, False, ";pwd=topsecret")

	' Change database password and close database.
	dbs.NewPassword "topsecret","ultrasecret"
	dbs.Close
End Sub

Saving Your Application as an MDE File

Saving your database as an MDE file creates a separate copy of your database that contains no Visual Basic source code. Your code is compiled and continues to run, but it can’t be viewed or edited. Additionally, users can’t view or modify the design of forms, reports, and modules in an MDE database. However, users can view and modify the design of tables, queries, macros, and relationships in an MDE database. Saving your application as an MDE file also reduces the size of your database and makes your application’s use of memory more efficient.

Saving your database as an MDE file prevents the following actions:

Viewing or modifying the design of forms, reports, or modules.
Creating forms, reports, or modules.
Adding or deleting references to object libraries or databases by using the References command on the Tools menu.
Using the Object Browser.
Changing code by using the properties or methods of the Microsoft Access or Visual Basic object models (because an MDE file contains no source code).
Importing or exporting forms, reports, or modules. However, tables, queries, and macros can be imported from or exported to non-MDE databases. Any tables, queries, or macros in an MDE database can be imported into another database, but all forms, reports, and modules can’t be imported into another database.

If you need to modify the design of forms, reports, or modules in a database saved as an MDE file, you must open the original database, modify it, and then save it as an MDE file again. Saving a database that contains tables as an MDE file creates complications reconciling different versions of the data if you need to modify the design of the application later. For this reason, saving a database as an MDE file is most appropriate for the front-end of an application that has been split into a back-end database that contains only tables and a front-end database that contains the remaining objects.

See Also For more information on splitting a database, see “Separating Your Application’s Tables from Its Other Objects” in Chapter 15 “Delivering Your Application.”

Note The process of saving a database as an MDE file compiles all modules and compacts the destination database, so there is no need to perform these steps before saving a database as an MDE file.

Û To save a database as an MDE file

Close the database. If the database is being shared on a network, make sure no other users have it open.
On the Tools menu, point to Database Utilities, and then click Make MDE File.
In the Save As MDE dialog box, select the database you want to save as an MDE file, and then click Make MDE.
In the Save MDE As dialog box, specify a file name for the database and the location where you want to save it, and then click Save.
Your original database is unchanged and a new copy is saved as an MDE file using the file name and location you specified in step 4.

Note Saving a database as an MDE file doesn’t create a run-time version of the database. To use an MDE database, users must have Microsoft Access 97 installed. Alternatively, if you have Microsoft Office 97, Developer Edition, you can save a database as an MDE file and then use the Setup Wizard to create a distributable run-time version of it.

Caution Be sure to save a backup copy of your original database in a safe place. You cannot modify the design of forms, reports, or modules in a database saved as an MDE file. If you need to modify the design of any of these objects, you must open the original database, modify the objects, and then save the database again as an MDE file. Also, databases saved as MDE files in Microsoft Access 97 cannot be opened or converted in later versions of Microsoft Access. To convert or open it in later versions of Microsoft Access, you must use the original database.

Using Other Forms of Security with a Database That Is Saved as an MDE File

Saving a database as an MDE file is a good way to protect the code and the design of forms and reports in your application, and it doesn’t require users to log on or require you to create and manage the user accounts and permissions required by user-level security. However, an MDE file doesn’t control how users access your application’s tables, queries, and macros. If you want more control over these database objects, you should establish user-level security before you save your database as an MDE file.

If the database you want to save as an MDE file is secured with user-level security, you must meet the following requirements before you can proceed:

Before starting Microsoft Access, you must use the Workgroup Administrator or the /wrkgrp command-line option to join the workgroup defined by the workgroup information file that was in use when the database was created. The workgroup information file defines the user accounts used to access the database.
See Also For more information on workgroup information files, see “Where Is Workgroup and Permission Information Stored?” later in this chapter.
Your user account must have Open/Run and Open Exclusive permissions for the database object.
Your user account must have Modify Design or Administer permission for any tables in the database, or you must be the owner of any tables in the database.
Your user account must have Read Design permission for all objects in the database.

You can also use a database password to control who can open an MDE database. To use a database password or establish user-level security for an MDE database, you must implement these features in the original database before you save it as an MDE file. The database password or user-level security will be preserved in the new MDE database.

Saving your database as an MDE file prevents all users (including database administrators) from modifying the design of forms, reports, and modules. If this is too restrictive and you require additional control and flexibility in these areas, you shouldn’t save your database as an MDE file—you should establish user-level security instead.

See Also For more information on database passwords, see “Setting a Database Password” earlier in this chapter. For more information on user-level security, see “Microsoft Access User-Level Security” later in this chapter.

Saving a Replicated Database as an MDE File

A replicated database (either a replica or Design Master) cannot be saved as an MDE file; however, once a database is saved as an MDE file, it can be replicated. To save a replicated database as an MDE file, you must first remove replication system fields, tables, and properties.

See Also For more information on removing replication fields, tables, and properties, search the Help index for “replicated databases, making regular.” For more information on database replication, see Chapter 20, “Using Replication in Your Application.”

Saving a Database That References Another Database as an MDE File

If you try to save a database (.mdb) or an add-in database (.mda) that references another database or add-in as an MDE file, Microsoft Access displays an error message and doesn’t let you complete the operation. To save a database that references another database as an MDE file, you must save all databases in the chain of references as MDE files, starting from the first database referenced. After saving the first database as an MDE file, you must then update the reference in the next database to point to the new MDE file before saving it as an MDE file, and so on.

For example, if Database1.mdb references Database2.mdb, which references Database3.mda, you would proceed as follows:

Save Database3.mda as Database3.mde.
Open Database 2.mdb and change its reference to point to the new Database3.mde.
Save Database2.mdb as Database2.mde.
Open Database1.mdb and change its reference to point to the new Database2.mde.
Save Database1.mdb as Database1.mde.

See Also For information on referencing another database, see “Referencing Library Databases” in Chapter 12, “Using Library Databases and Dynamic-Link Libraries.”