Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
When looking at all of the ways to secure your database and database-driven applications, database administrators might feel a bit discouraged or overwhelmed. Fortunately for Microsoft Access database admins, Access enables you to encrypt a database fairly easy. 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, it becomes very difficult to decipher any of the data contained within the database.
A Microsoft Office 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:
Step 1:
Choose Tool | Security | Encrypt/Decrypt Database from the menu options
Step 2:
The Encrypt/Decrypt Database dialog will appear. Select the file you want to encrypt and click OK.
Step 3:
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.
Read:Â Best Database Software for Developers
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"
End Sub
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.
Read: Access Office 365 REST APIs with .NET Libraries
Conclusion to Database Encryption
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.
Read more database programming and database administration tutorials.
