MS-Access / Getting Started

---

Database Encryption with DAO

Access 2007 now supports standard encryption algorithms that are included with Windows. To maintain backward compatibility with previous versions of Access, this feature is only available for the new file formats. Database encryption combines two features that have existed in Jet for some time: database passwords and encoding. You can decrypt the database by removing the database password.

You can set the database password using DAO in several ways:

• Call the NewPassword method of the Database object.
• Compact an existing database and include a password using the CompactDatabase method.
• Create a new database using the CreateDatabase method and specify the password.

Using ActiveX Data Objects (ADO), you can run a query that calls ALTER DATABASE PASSWORD, but this does not work from DAO.

Setting the Database Password

Let's look at the three ways you can set a database password using DAO:

• Using the NewPassword method
• Compacting a database
• Creating a new database

Setting the database password requires that the database is opened exclusively.

Using the NewPassword Method

To change the password for the current database without creating a new database, you can use the NewPassword method on the Database object like this:

Sub ChangePassword(strOldPassword As String, strNewPassword As String)
    'Possible errors
    Const ERR_DB_OPENED_SHARED As Long = 3621
    Const ERR_INVALID_PASSWORD As Long = 3031

    On Error GoTo ChangePasswordErrors

    Dim dbs As DAO.Database

    'Get the database object
    Set dbs = CurrentDb

    'Change the password
    dbs.NewPassword strOldPassword, strNewPassword

Cleanup:
    Set dbs = Nothing
    Exit Sub

ChangePasswordErrors:
    Dim strMsg As String

    'Handle errors: invalid password and not opened exclusively
    Select Case Err.Number
	Case ERR_DB_OPENED_SHARED:
	    strMsg = "The current database is not opened exclusively"
	Case ERR_INVALID_PASSWORD:
    	    strMsg = "The specified password is invalid"
	Case Else
	    strMsg = "Unhandled Error: " & Err.Description
    End Select

    'Display the message
    MsgBox strMsg, vbCritical, "Cannot Change Password"
    Goto Cleanup
End Sub

Compacting a Database

Let's say that you want to compact a database using DAO and add a database password to the newly compacted database. The following code shows you how to include the database password for the CompactDatabase method on the DBEngine object:

Sub CompactAndEncrypt( _
	strOldDatabase As String, _
	strNewDatabase As String, _
	strPassword As String)

    'Make sure the old database exists
    If Dir(strOldDatabase) = "" Then
	MsgBox "Cannot find database: " & strOldDatabase, vbExclamation
	Exit Sub
    End If

    'Make sure the old database is not
    'the current database
    If strOldDatabase = CurrentDb.Name Then
	MsgBox "Cannot compact the currently opened database", vbExclamation
	Exit Sub
    End If

    'Make sure the new password is between 1-20 characters
    If Len(strPassword) < 1 Or Len(strPassword) > 20 Then
	MsgBox "Password must be between 1 and 20 characters", vbExclamation
	Exit Sub
    End If

    'Ok, now compact the database and set the new password
    DBEngine.CompactDatabase _
	strOldDatabase, _
	strNewDatabase, _
	dbLangGeneral & ";PWD=" & strPassword
End Sub

Creating a New Database

Finally, you can create a new database that is encrypted to begin with:

Sub CreateAndEncrypt(strDatabase As String, strPassword As String)

    'Make sure the database does not exist
    If Dir(strDatabase) <> "" Then
	MsgBox "The specified database already exists: " & strDatabase, vbExclamation
	Exit Sub
    End If

    'Make sure the new password is between 1-20 characters
    If Len(strPassword) < 1 Or Len(strPassword) > 20 Then
	MsgBox "Password must be between 1 and 20 characters", vbExclamation
	Exit Sub
    End If

    'Ok, now create the database and set the new password
    DBEngine.CreateDatabase _
	strDatabase, _
	dbLangGeneral & ";PWD=" & strPassword
End Sub