Security

Microsoft Jet databases can be secured in one of two ways: share-level security or user-level security. For share-level security, the database is secured with a password. Anyone attempting to open the database must specify the correct database password. For user-level security, each user is given a user name and password to open the database.

Changing a Password

The first step in securing a Microsoft Jet database is to change the password for the Admin user, if using user-level security, or changing the database password if using share-level security. When changing a password for a user or database, you must supply both the existing and new passwords. When changing the database or Admin user's password for the first time, use an empty string ("") as the existing password.

The following code shows how to enable user level security by setting the password for the Admin user to "password".

DAO

Sub DAOChangePassword()

   Dim wks As Workspace

   Dim usr As DAO.User

   ' Open the workspace, specifying the system database to use

   DBEngine.SystemDB = _

      "C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"

   Set wks = DBEngine.CreateWorkspace("", "Admin", "")

   ' Change the password for the user Admin

   wks.Users("Admin").NewPassword "", "password"

End Sub

ADOX

Sub ADOChangePassword()

   Dim cat As New ADOX.Catalog

   ' Open the catalog, specifying the system database to use

   cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

      "Data Source=.\NorthWind.mdb;Jet OLEDB:System database=" & _

      "C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"

   ' Change the password for the user Admin

   cat.Users("Admin").ChangePassword "", "password"

End Sub

DAO and ADOX both have a method on the User object to change the user's password. The method takes the user's current password and the new password as parameters. In DAO this method is called NewPassword while in ADOX it is called ChangePassword.

Note   The Microsoft Jet Provider will not error on the line of code that opens the catalog if the system database specified is incorrect. However, it will error when attempting to change the password or perform any other security related operations with the following error if the system database was not correctly specified: "The operation requested by the application is not supported by the provider."

The following code shows how to change the database password for enabling security at the share level.

DAO

Sub DAOChangeDatabasePassword()

   ' Make sure there isn't already a file with the

   ' name of the compacted database.

   If Dir(".\NewNorthWind.mdb") <> "" Then _

      Kill ".\NewNorthWind.mdb"

   ' Basic compact - creating new database named newnwind

   DBEngine.CompactDatabase ".\NorthWind.mdb", _

      ".\NewNorthWind.mdb", , , ";pwd=password;"

   ' Delete the original database

   Kill ".\NorthWind.mdb"

   ' Rename the file back to the original name

   Name ".\NewNorthWind.mdb" As ".\NorthWind.mdb"

End Sub

JRO

Sub JROChangeDatabasePassword()

   Dim je As New JRO.JetEngine

   ' Make sure there isn't already a file with the

   ' name of the compacted database.

   If Dir(".\NewNorthWind.mdb") <> "" Then _

      Kill ".\NewNorthWind.mdb"

   ' Compact the database specifying the new database password

   je.CompactDatabase "Data Source=.\NorthWind.mdb;", _

      "Data Source=.\NewNorthWind.mdb;" & _

      "Jet OLEDB:Database Password=password"

   ' Delete the original database

   Kill ".\NorthWind.mdb"

   ' Rename the file back to the original name

   Name ".\NewNorthWind.mdb" As ".\NorthWind.mdb"

End Sub

Note   JRO, not ADOX, is used to change a database password at share level.

Both DAO and JRO allow you to change the database password when compacting the database. The syntax is slightly different: in DAO, specify ";pwd=password;" in the Password parameter of CompactDatabase. In JRO, specify the provider-specific "Jet OLEDB:Database Password=password" in the destination connection parameter of CompactDatabase.

Alternatively, the DAO code could be rewritten to use the NewPassword method of the Database object.

Sub DAOChangeDatabasePassword2()

   Dim db As DAO.Database

   Set db = DBEngine.OpenDatabase(".\NorthWind.mdb", True)

   db.NewPassword "", "password"

   db.Close

End Sub

A similar mechanism is not currently available in JRO or ADOX. You must use the CompactDatabase method in order to change the database password.