Microsoft Office 2000/Visual Basic Programmer's Guide

Encrypting a Database

If you want to protect your secured database from unauthorized access by someone who is using a disk editor or other utility program, you can encrypt it. Encryption makes a database indecipherable, which protects it from unauthorized viewing or use, particularly during electronic transmission or when it's stored on floppy disk, tape, or compact disc. Encrypting an unsecured database will have no effect because anybody can open the database with Access or VBA and gain full access to all objects in the database.

Important   Before you can encrypt or decrypt an Access database, you must be either the owner of the database or, if the database has been secured, a member of the Admins group of the workgroup information file that contains the accounts used to secure the database. You must also be able to open the database in exclusive mode, which, if the database is secured by using User-Level security, requires you to have the Open/Run and Open Exclusive permissions.

The User-Level Security Wizard automatically encrypts your database. You can encrypt or decrypt a database by starting Access without opening a database, and then using the Encrypt/Decrypt Database command (Tools menu, Security submenu). When you encrypt a database by using the same file name as that of the original database, Access deletes the original unencrypted file if the encryption process is successful. If an error occurs, Access doesn't delete the original file.

Note   Encrypting a database slows its performance by up to 15 percent. Also, an encrypted database cannot be compressed by programs such as DriveSpace or PKZIP. If you try to compress an encrypted database, its size doesn't change.

You can also use the CompactDatabase method of the Microsoft Jet and Replication Objects (JRO) 2.1 JetEngine object to encrypt or decrypt a database. To use the JRO JetEngine object, you must set a reference to the Microsoft Jet and Replication Objects 2.1 object library. When you use the CompactDatabase method, you can't save the compacted (and optionally encrypted) database to the same name as the original database. The CompactDatabase method takes two arguments to specify the source database and the destination database: SourceConnection and DestConnection. Both the SourceConnection and DestConnection arguments take the form of connection strings. Within the connection strings, you specify various connection properties to determine how the source database is opened and how the destination database is compacted. At a minimum, you must use the Data Source property in each connection string to specify the path and name of the database. Additionally, to encrypt the database, you must include the Jet OLEDB:Encrypt Database property in the connection string for the DestConnection argument. The following procedure uses these connection properties to encrypt the database specified by the strSourceDB argument to the path and name specified by the strDestDB argument:

Function EncryptDb(strSourceDB As String, _
                     strDestDB As String) As String
   Dim jetEngine          As JRO.JetEngine
   Dim strSourceConnect   As String
   Dim strDestConnect     As String

   ' Build connection strings for SourceConnection and 
   ' DestConnection arguments.
   strSourceConnect = "Data Source=" & strSourceDB
   strDestConnect = "Data Source=" & strDestDB & ";" & _
                     "Jet OLEDB:Encrypt Database=True"

   Set jetEngine = New JRO.JetEngine

   ' Compact and encrypt the database specified by strSourceDB
   ' to the name and path specified by strDestDB.
   jetEngine.CompactDatabase strSourceConnect, strDestConnect

   Set jetEngine = Nothing
End Function

The EncryptDb procedure is available in the modSecurity module in AccessSecurity.mdb in the ODETools\V9\Samples\ODETools\V9\Samples\OPG\Samples\CH18 subfolder on the Office 2000 Developer CD-ROM.

If the database you are encrypting or compacting is secured with User-Level security or a database password, to open the source database you must specify additional connection properties in the connection string for the SourceConnection argument. When specifying additional properties in the connection string, separate each property with a semicolon (;). The following table lists these properties.

The connection string for both the SourceConnection and DestConnection arguments also accepts the Provider property, but the OLE DB provider for Microsoft Jet 4.0 (Microsoft.Jet.OLEDB.4.0) is assumed by default, so you don't have to specify it within the string. The connection strings can also include other properties to determine how the database is compacted. For more information about using the JRO CompactDatabase method, search Microsoft ADO Help (C:\Program Files\Common Files\System\Ado\Ado210.chm) or the Microsoft Access Visual Basic Reference Help index for "CompactDatabase method."