Encrypting an Access Database
You might feel discouraged and ask yourself, "Why bother with security?" Do not despair! Fortunately, Access enables you to encrypt a database. The encryption process renders the data in the database indecipherable from data in word processors, disk utilities, and other products capable of reading text. When a database is encrypted, no one can decipher any of its data.
A database can be encrypted using the standard Access menus or by writing a VBA subroutine. In either case, the database you are encrypting must not be open. To encrypt a database using Access's standard menus, follow these steps:
Choose Tool | Security | Encrypt/Decrypt Database from the menu options
The Encrypt/Decrypt Database dialog will appear. Select the file you want tot encrypt and click OK
You are prompted for the name of the encrypted database. If you selected the same name as the existing file, Access deletes the original file after it determines that the encryption process is successful. It is always a good idea to back up the original database before you begin the encryption process. This ensures that if something goes awry during the encryption process, you won't lose your data.
Encrypting with Code
If you have distributed your application with the runtime version of Access and you want to give your users the ability to encrypt the database, you must write ADO code to accomplish the encryption process. The code looks like this:
Sub Encrypt(strDBNotEncrypted As String, _
strDBEncrypted As String)
Dim je As Nwe JRO.JetEngine
Je.CompactDatabase SourceConnection:="Data Source=" _
& strDBNotEncrypted & ";", _
DestConnection:="Data Source=" & strDBEncrypted & _
"; Jet OLEDB:Encrypt Database=True"
This routine receives two parameters. The first is the name of the database that you want to encrypt. The second is the name you want to assign to the encrypted database. The CompactDatabase method is issued on the JetEngine object. This method receives two parameters: the name of the original database to encrypt and the name for the new encrypted database. Notice that the data source for the destination includes information indicating that you want to encrypt the database being compacted.
When you encrypt a database, the entire database (not just the data) is encrypted. As you access the data and the objects in the database, Access needs to decrypt the objects so that users can use them and then encrypt them again when users are finished accessing them. Regardless of the method of encryption you use, the encrypted database degrades performance by about 15 percent. Furthermore, encrypted databases usually cannot be compressed by most disk-compression software utilities because compression software usually relies on repeated patterns of data. The encryption process is so effective at removing any patterns that it renders most compression utilities ineffective. You need to decide whether this decrease in performance and the inability to compress the database file is worth the extra security that encryption provides.
About the Author
Alison Balter is the author of Mastering Microsoft Access 2000 Development (Click to buy) a book published by Sams Publishing. This article is based on information from her book. Alison is the president of InfoTechnology Partners, Inc., a Microsoft Solutions Partner. Her training videos for Keystone Learning Systems are well-known in the Access community. Alison is a regular speaker at conferences, is an author and speaker for Advisor Media, and has also written a number of Microsoft Access books.
© copyright 2001 by Sams Publishing. All Rights Reserved.