Using the Access OM to Set the Database Password

The Access 2007 Object Model also provides the capability to open the Set Database Password dialog box through VBA code. You could call the RunCommand object with the acCmdSetDatabasePassword parameter, which opens the Set Database Password dialog box in the instance of Access, so that the user can simply enter the password and click OK. This will not work for a database that is currently open in the instance of Access calling the RunCommand function, though, because applying a password requires that the database be opened in exclusive mode. However, it is plausible that a user wants to create another application that applies security to many different database files. The benefit to using RunCommand is that it allows the user to add the password when the procedure is invoked and that it employs the Access UI, so you do not have to create any UI for this operation. The following code creates a new instance of the Access application class, opens the specified database, and then opens the Set Database Password dialog box:

Sub

SetDatabasePasswordRunCmd(strSourcePath As String)

' Define Variables

Dim accApp As New Access.Application

' Open the Source database in Exclusive mode

accApp.OpenCurrentDatabase strSourcePath, True

' Invoke the "Encode Database As" dialog

accApp.DoCmd.RunCommand acCmdSetDatabasePassword

' Cleanup Variables

accApp.Quit acQuitSaveNone

Set accApp = Nothing

End

Sub

Also, it is worth noting that there are two possible errors that could occur from the last block of code. The first is that the user could click the Cancel button on the dialog, which would cause an error to be generated. The second is that the database could be in Read-Only mode or locked by another source. If you plan to use this code, or other code listed further throughout this chapter, please consider adding some basic error handling, as most of the examples in this chapter only provide a basic outline of the required code to complete the functionality, but not necessarily to handle any error that could occur.

There are two recommended procedures when adding shared-level security to an application:

□ Always make a backup copy of the database before modifying the password. If the database is corrupted or some other catastrophic error occurs, a backup copy will be useful to have on hand.

□ Store the new password in a safe location. If you lose the password to a database, the database is not recoverable.

The few previous examples illustrate how to set a database password either programmatically, using ADO or DAO, or through the Access UI. No matter which method you use, adding shared-level security to any database is quite easy and is a great way to improve data security.

Was this article helpful?

+1 -1

Responses

  • Yerusalem
    How to setup password access using vba?
    9 years ago

Post a comment