Working with Database Passwords in VBA Code

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

As an alternative to using the Access user interface to work with database passwords, you can use Data Access Objects (DAO) and ActiveX Data Objects (ADO) in VBA code.

****Important   ****If you use code to open a database and a user later sets a database password without your being aware of it, your code will no longer be able to open the database. Your code should always check to see if the attempt to open a database has succeeded. The code should also provide error handling in case it encounters a password-protected database.

Setting, Changing, or Clearing a Database Password in Code

Unfortunately there is no way to set, change, or clear a database password by using ADO code. It is possible to set, change, or clear a database password when you are compacting a database by using the CompactDatabase method of the Microsoft Jet and Replication Objects 2.1 object library; however the CompactDatabase method requires you to create a second copy of the database to do so.

A simpler method of setting, changing, or clearing a database password is to use the NewPassword method of the DAO Database object. To work with the NewPassword method, you must set a reference to the Microsoft DAO 3.6 object library. Before you can work with a database password, you must be able to open the database in exclusive mode. To open a database by using exclusive access with DAO, set the Options argument of the OpenDatabase method to True. The following procedure opens a database in exclusive mode, and sets, changes, or clears a database password depending on what values you pass to it for the strOldPwd and strNewPwd arguments:

  • To set a database password for a database that currently has no password, pass a zero-length string ("") for strOldPwd and the password you want to set for strNewPwd.

  • To change a database password, pass the current password for strOldPwd and the new password for strNewPwd.

  • To clear a database password, pass the current database password for strOldPwd and a zero-length string for strNewPwd.

 Function SetDBPassword(strDBPath As String, _
                       strOldPwd As String, _
                       strNewPwd As String)
   ' This procedure sets a new password or changes an existing
   ' password.

   Dim dbsDB      As DAO.Database
   Dim strOpenPwd As String

   ' Create connection string by using current password.
   strOpenPwd = ";pwd=" & strOldPwd

   ' Open database for exclusive access by using current password. To get
   ' exclusive access, you must set the Options argument to True.
   Set dbsDB = OpenDatabase(Name:=strDBPath, _
                            Options:=True, _
                            ReadOnly:=False, _
                            Connect:=strOpenPwd)

   ' Set or change password.
   With dbsDB
      .NewPassword strOldPwd, strNewPwd
      .Close
   End With

   Set dbsDB = Nothing
End Function

The SetDBPassword 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.

Opening a Password-Protected Database with ADO

To open an Access password-protected database by using ADO code, you specify the password by using the provider-specific Jet OLEDB:Database Password property. You can specify the Jet OLEDB:Database Password property by using the Properties collection of the Connection object, or as part of the connection string passed to the Open method of the Connection object. For example, the following procedure opens a password-protected database:

Function OpenProtectedDB(strDBPath As String, _
                         strPwd As String)
   Dim cnnDB As ADODB.Connection

   Set cnnDB = New ADODB.Connection

   ' Open database for shared (by default), read/write access, and
   ' specify database password.
   With cnnDB
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Properties("Jet OLEDB:Database Password") = strPwd
      .Mode = adModeReadWrite
      .Open strDBPath
   End With
   ' Code to work with database goes here.
   cnnDB.Close
   Set cnnDB = Nothing
End Function

The OpenProtectedDB 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.