This chapter addresses the issue of database privacy, integrity, and security. The main purpose for database security is to prevent both inadvertent and intentional damage to the data and the database objects. Proper security measures prevent anyone who might view or edit the information from gaining unauthorized access. They also prevent anyone from making design changes without express permission to do so. You can protect both the data and the design elements with a variety of approaches. This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293).
In a multi-user environment, security becomes both more essential and more complicated. Not all users need to have access to all the data or all the design elements in the database. One way to create security is to organize users in groups, each of which has specific responsibilities. This information is stored in the Workgroup Information file (WIF).
Understand the User-Level Security Model
The user-level security model is based on the idea of workgroups whose members share the data and privileges. The group and user accounts list the members of the workgroup. A group account is a collection of user accounts. Each member of the group is permitted some degree of freedom in dealing with data and objects. A user account belongs to a single user and includes the user name and personal ID (PID).
The four pieces of a user-level security model are:
A user is a person who uses the database.
A group is a set of users, all of whom operate at the same security level and need access to the same parts of the database.
A permission gives a user or group the right to carry out a specific action. For example, Read Data permission allows opening a table or query for viewing but not for entering new data or editing existing data.
An object refers to any of the Access tables, queries, forms, reports, macros, or modules—as well as to the database itself.
QUICKSTEPS: Deciding Who Needs What Permisions
Depending on what the user needs to do with the database, you can assign her to any of the groups provided by the User-Level Security Wizard. To give a user more permissions than one group has, you can assign that user to more than one group.
Backup Operators can open the database exclusively for backup and compacting but are not permitted to see any of the database objects.
Full Data Users have full permission to edit data but are not allowed to make any design changes.
Full Permissions have full permissions on all database objects but are not allowed to assign permissions to others.
New Data Users can read and insert data but are not allowed to delete or update existing data. They are also not allowed to alter any object designs.
Project Designers have full permission to edit data and all objects but are not allowed to alter any tables or relationships.
Read-Only Users can read all the data but are not allowed to change data or any design object.
Update Data Users can read and update all data but are not allowed to insert or delete data. They are also not allowed to make any design changes.
This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293). Check it out at your favorite bookstore today.
The User-Level Security Wizard can secure any or all of the objects in your database. All the relationships and linked tables are kept intact when you secure the database with the Security Wizard. It also makes a backup copy of the original database in case you have forgotten to do so. The backup file has the same name with the .bak fi le extension.
With the Security Wizard, you can be specific about which users enjoy which permissions. You can also decide who belongs to which groups and edit user passwords and personal IDs (PIDs) as well. A PID is similar to a password and is used in combination with the user name to identify an account.
Start the User-Level Security Wizard The database must be open before you can work with the User-Level Security Wizard.
Open the database whose objects you want to secure.
In the first dialog box, as shown in Figure 9-1, either:
Select Create A New Workgroup Information File if one doesn’t exist.
–Or–
Select Modify My Current Workgroup Information File to make changes in an existing WIF
Figure 9-1: You can choose to create a new workgroup information fi le or edit an existing one, if available.
Click Next.
In the next dialog box, as shown in Figure 9-2, accept the workgroup ID (WID) the Security Wizard offers or enter a unique string in the WID box. The WID is a case sensitive string of between 4 and 20 alphabetic and numeric characters.
Figure 9-2: The Security Wizard lets you choose where to locate and what to name your Workgroup ID.
Select I Want To Create A Shortcut To Open My Security-Enhanced Database.
Click Next.
CAUTION If you choose to make the WIF identifi ed in Figure 9-2 the default, every Access database you open will use it unless you specify that it be opened with a different WIF.
Secure Individual Objects The third Security Wizard dialog box, shown in Figure 9-3, shows tabs for each of the objects in the current database. By default, all objects are secured by the wizard, but you can exclude some and keep the existing security measures in the others.
Figure 9-3: You can remove security from objects that don’t need it.
Deselect the check box next to the object you want to leave as it is now.
Click the tab (such as Queries, Forms, or Reports) for any other object whose security selections you want to change.
When finished, click Next.
If you have secured your Visual Basic code with a password, the Security Wizard asks for the password in the next dialog box. If not, you move on to setting up group accounts.
TIP Prior to using the Security Wizard, be sure to open the database in shared mode. If you open it in Exclusive mode, the Security Wizard will offer to reopen it in shared mode. Also, be sure to close any unsaved objects you have open.
NOTE The Other tab in the third dialog box of the Security Wizard contains a list of new objects. You can choose to keep or to remove all new tables/queries, forms, reports, and macros from the Security Wizard’s security program.
This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293). Check it out at your favorite bookstore today.
Set Up Group Accounts Setting up group accounts involves choosing the appropriate groups for your application based on the tasks assigned to the users. In the next dialog box, shown in Figure 9-4, you can view which permissions are assigned to specific security groups, and then decide how to assign the users to the different groups. A unique group ID is assigned to each group by the Security Wizard.
Figure 9-4: The security group accounts provide specific permissions to their members.
CAUTION The default Users group provides access to all objects in a database. Everyone who has a copy of Access is automatically a member of the Users group and will have the same permissions applying to all open databases.
Select a group in the group list to read the description of the permissions in the Group Permissions area. For example, Figure 9-4 shows the permissions granted to the New Data Users group.
Select the check boxes for the groups you want to include in the security model.
Click Next.
The next Security Wizard dialog box, shown in Figure 9-5, allows you to assign some permissions to the Users group but strongly recommends against that. Choose No, The Users Group Should Not Have Any Permissions.
Figure 9-5: The Security Wizard recommends not giving the Users group any permissions.
In the next Security Wizard dialog box, shown in Figure 9-6, you can get specific about which users to add to the workgroup. You can also delete a user from the workgroup and edit a user’s password or PID.
Figure 9-6: The Security Wizard allows you to add users to and delete users from the workgroup.
To add a new user:
Click Add New User in the left pane.
Type a name in the User Name text box.
Type a password in the Password text box.
The user’s PID is automatically entered, but you can change it if you want to.
Click Add This User To The List.
Repeat Steps 1 through 5 to add other users to the workgroup.
To remove a user from the workgroup, select the name and click Delete User From The List. To edit user information, select the name and change the password or PID. Click Next when fi nished with the users in the workgroup.
Assign Users to Groups The final major step in defining user-level security is to assign each user to one of the groups that you have selected. You have two ways to carry this out in the next Security Wizard dialog box, as shown in Figure 9-7. You may start with a user and add the user to groups, or start with a group and assign users to it.
Figure 9-7: You can assign users to groups or groups to users.
In the next dialog box, click Select A User And Assign The User To Groups. The user names you added to the workgroup appear in the Group Or User Name drop-down list. The group names you included appear in the pane below the list.
Click the Group Or User Name down arrow and select a user name from the list.
In the lower pane, select each group name that you want the user to be a member of.
Repeat Steps 2 and 3 to assign other users to groups. Then click Next.
In the final Security Wizard dialog box, enter a name and path for the unsecured backup database, and click Finish.
NOTE You can assign a user to more than one group. The user then has all the permissions associated with all the groups she belongs to.
NOTE When you assign users to groups in the Security Wizard, if you choose Select A Group And Assign Users To The Group, you select the group name in the Group Or User Name drop-down list and select the user names from the list below it.
This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293). Check it out at your favorite bookstore today.
It is extremely important to document and save all the security provisions you have set. When the Security Wizard is finished, a security report is displayed in Print Preview as shown in Figure 9-8. You will need all this information if you need to rebuild the WIF.
Figure 9-8: All the security provisions are documented in the security report.
You have three ways to save the document:
Click Print on the Print Preview toolbar to print a hardcopy of the report. Store the printout in a safe place.
Choose Tools | Offi ce Links | Publish With Microsoft Office Word to save it as a Word document. Print the document or store the fi le in a safe place.
Choose File | Export to open the Export Report Reportname As dialog box. In the Save As Type drop-down box, choose Snapshot Format (*.snp), and type a name for the file in the File Name box. Click Export.
QUICKSTEPS: Keeping Data Safe
Prevent User Access to Data in a Form If you don’t want the user to be able to get to a text box control in a form by pressing TAB:
Open the form in Design view.
Double-click the text box control you want to protect.
In the property sheet, click the Other tab.
Select the Tab Stop property, and choose No from the drop-down list. Then save the form design.
The user will still be able to click in the control and edit the contents.
Limit To Values in a Lookup Field A lookup field can appear in a form as a combo box or as a list box from which the user chooses a value. She may also enter a value not already on the list. If you don’t want other values in the field:
Open the table in Design view.
Select the lookup fi eld in the upper pane.
Click the Lookup tab in the lower pane, and change the Limit To List property to Yes.
Save the table design.
Require Valid Data To make sure newly entered data is correct, include data validation rules in a table or form design. To do this:
Open the table in Design view and select the field name in the upper pane.
Select the Validation Rule property in the Field Properties pane, and enter the expression that the field value must comply with.
Select the Validation Text property text box, and enter a message to display when the rule is broken.
Save the table design.
Text box controls in forms also have the Validation Rule and Text properties. See Chapter 3 for more information on using data validation.
Prevent Data Changes in a Form
To keep a user from entering, deleting, or editing data in a form:
Open the form in Design view, and double-click the form selector.
In the property sheet, click the Data tab.
Select the Allow Edits property text box, and choose No from the drop-down list.
Repeat Step 3 to set the Allow Deletions and Allow Additions properties.
Save the form design.
This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293). Check it out at your favorite bookstore today.
Access offers several less comprehensive methods of securing a database and its objects. For example, you can require a password to open the database, and you can hide specifi c objects from user view. In addition, there are methods to create backup copies of the database and to improve performance by optimizing disk space usage.
Use a Database Password
A database password only protects a database from being opened by someone who doesn’t know the password. Anyone who knows the password can open the database. Once the database is open, the user can do anything with it.
Assign a Password You must have exclusive use of the database to assign a password.
To add a password, make sure all users have closed the database, then:
Click File | Open. In the Open dialog box, select the database, click the Open down arrow, and choose Open Exclusive.
Click Tools | Security | Set Database Password.
Type the password in the Password text box.
Repeat the password in the Verify text box, and then click OK. (If the entries don’t match, Access will ask you to reenter the password in the Verify box. Do so, and click OK.)
The next time you try to open the database, you will be asked for the password.
Some basic guidelines for creating a password include:
Create a password that combines uppercase and lowercase letters with numbers and symbols. This builds a “strong” password. For example, “67TrCg!89sdJ” is a strong password, while “MyFavoriteCat” is not.
Never use a word that appears in the dictionary.
Do not use the special characters that have a special meaning in Access: “ \ [ ] : | < > + = ; , . ? *.
CAUTION Don’t forget your password. You won’t be able to open the database without it. To be safe, write it down and store it in a safe place.
An encoded database is compacted and unreadable by any word processor or other utility program. You still have access to an encoded database and can work with it as usual. When you decode an encoded database, it returns to its original form.
The database must be closed before you can encode it. You must be the owner of the Workgroup Information File and be able to open it in Exclusive mode. In addition, your disk must have enough free space for both the original database and the encoded copy.
TIP Always make a backup of your database before adding a password. Store the backup in a secure place.
To encode a database:
In the empty Database window, click Tools | Security | Encode/Decode Database.
Select the database in the Encode/Decode Database dialog box, as shown in Figure 9-9, and click OK.
Figure 9-9: You can encode a database to make it unreadable by programs other than Access.
In the Encode Database As dialog box, specify the location where you want to save the encoded database, and click Save.
To decode the encoded database, repeat steps 1 and 2 above, and then specify the name and location in the Decode Database As dialog box. Click OK.
CAUTION You can have problems if you link a table in a password protected database to an unprotected database. The password for the protected database is stored with the linking information passed to the second database. Any user who can open the unprotected database also has access to the linked table in the protected database.
This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293). Check it out at your favorite bookstore today.
Hiding database objects does not really tighten security; it just keeps certain objects from appearing in the Database window.
Hide Database Objects
To hide an object:
Select the object in the Database window, and click Properties on the Database toolbar.
Check Hidden at the bottom of the General tab in the Properties dialog box, as shown in Figure 9-10.
Click OK.
NOTE You can save the encoded version with the same name as the original, but you will be asked to confirm this. If the encode process fails, the original database is not deleted.
View Hidden Database Objects
Normally, the hidden object is no longer listed in the Database window; however, you can choose to see the names of the hidden objects.
Choose Tools | Options and click the View tab.
In the Show area, select Hidden Objects.
Click OK.
Although the hidden objects appear dimmed in the Database window, they can still be opened. To remove the Hidden property, open the Properties dialog box (see the previous section, “Hide Database Objects”) and deselect the Hidden option.
Figure 9-10:Use the object’s Properties dialog box to hide the object.
This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293). Check it out at your favorite bookstore today.
When working with an important database, it is a good idea to have a backup copy on hand. Creating a backup database on a regular basis can help reduce the risk of losing important data.
Before making a backup copy, make sure all users have closed their databases so all changes in the data have been saved.
You can make a backup copy from either outside or inside of Access. To backup without using Access, do one of the following:
Right-click the file name in the Windows Explorer window, and point to Send To in the context menu, as shown in Figure 9-11. Click the drive where you want to save the copy.
Figure 9-11: You can create a backup copy using Send To in Widows Explorer.
After locating the file name in the Windows Explorer window, drag it to another disk in the Folders pane.
Use Microsoft Windows Backup and Recovery tools. For example, in Windows XP you can use the Automated System Recovery (ASR) tool (Click Start | All Programs | Accessories | System Tools | Backup | Advanced Mode). You can also use the MSDOS Copy command or a third-party backup utility. (You may also be able to compress the file at the same time.)
Back Up a Database From Access
You can use Access to create a regular copy to keep as a backup copy. No compression or other reformatting takes place—you just create a regular database file.
TIP By default, the backup fi le keeps the same name with the current date added to it.
Click File | Backup Database.
Choose the location for the copy, and type a name for it in the Save Backup As dialog box, as shown in Figure 9-12.
Figure 9-12: You can also create a backup copy with Access.
Click Save.
TIP If yours is a multi-user environment, be sure to backup the Workgroup Information File, too.
This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293). Check it out at your favorite bookstore today.
If you want to back up only a few objects instead of the whole database:
Create a new empty database.
Click File | Get External Data | Import.
Locate and select the database in the Import dialog box. Click Import.
Click each tab in the Import Objects dialog box, as shown in Figure 9-13, and select the objects you want. See the section, “Import Data from Outside Sources,” in Chapter 4 for more information about importing.
Figure 9-13: You can import objects into the backup database.
Click OK to import the objects.
Save the new database.
NOTE By default, the new database assumes the Access 2000 format, unless you’ve changed the default settings. See Chapter 1 for more information about changing default settings.
TIP Some defi nitions can cover many pages. Be sure to check how many before you start to print.
Restore a Database
When you need to restore the database from the backup copy, use the recovery tool for the method you used to create the backup. For example, in Windows Explorer you may simply drag the fi le name back to the original folder on the hard drive.
NOTE You can save the Documenter’s report as a Report Snapshot; an Excel worksheet; or in another format, such as HTML or RTF. (See Chapter 7 for information on creating a Report Snapshot.)
This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293). Check it out at your favorite bookstore today.
Access provides tools to assist you in managing the size of your database, as well as to repair a database that may have become corrupted. You can also create a printout of your database relationships, database properties, and definitions of your database objects.
Document a Database
If you are working alone on your own database, you probably don’t need extensive documentation of the database objects. In a corporation where there is a large information management team, documentation is extremely important. With up-to-date object definitions, errors can be quickly isolated and fixed.
The documentation can include all or a select group of objects in the database.
Choose Tools | Analyze | Documenter, as shown in Figure 9-14.
Figure 9-14:You can select just those objects you want documented.
Either:
Select each object tab and select the objects you want documented, or click Select All.
–Or–
Select the All Objects Types tab, and click Select All. This includes relationships and the database properties as well as the definitions of all the database objects.
If you don’t need all the information about an object, you can click Options and choose how much you want to see. Figure 9-15 shows the choices you have with table documentation.
Figure 9-15:Choose the table defi nition items to include in the documentation.
Click OK twice when you have finished making your selections.
When the Documenter is finished, the results appear in Print Preview.
Click Print to print the entire document, or press CTRL+P and use the Print dialog box to print selected pages. See Chapter 8 for more information on printing database objects.
This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293). Check it out at your favorite bookstore today.
As you improve and modify your database, the file can become scattered about on your disk with empty blocks of space between. The Compact and Repair Database utility removes the empty spaces and rearranges the file more efficiently to improve performance. If there has been some damage, this utility can find the problems and offer to repair them at once.
You can start the compact and repair process with the database open or closed. With the database open, simply click Tools | Database Utilities | Compact And Repair Database.
TIP One of the options on the General tab of the Tools | Options dialog allows you to automatically compact a database when you close it if the reduction in size would be greater than 256K. To do this, select the Compact On Close option.
If the database is closed, you can compact and repair it to the same fi le or with a different name in another location.
Figure 9-16:Documenter shows you the documentation report before you print.
If the compact and repair utility doesn’t work, one of the following problems may exist:
The database may be open by another user. Wait for the other user to close the database and try again.
There is not enough free space for both the original and the repaired database on the disk. Go back and delete unnecessary files, and try again.
You may not have the required Open/Run and Open Exclusive permissions.
The name of the database from an earlier version of Access may include a character that is no longer permitted, such as the grave accent (`). Return to the earlier version of Access, change the name, and then try again.
The database file may be set to Read Only.
This chapter is from Microsoft Office Access 2003 QuickSteps, by Cronan, Anderson, and Anderson (McGraw-Hill/Osborne, 2004, ISBN: 0072232293). Check it out at your favorite bookstore today.
Microsoft Access 
/ 16 
ASP.NET Export to Excel and Word using VB.NET and C#
