Is it possible to change the backend database password from the front end in VBA

Clive Beaton
Clive Beaton used Ask the Experts™
on
I had this question after viewing Problem word extracting data from an Access database.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
The best is to call a stored procedure from UI code and then pass the password to the sp and sp  will take care of it.
Database Architect / Application Developer
Top Expert 2007
Commented:
Yes it is possible. Here is the VBA code to change the Database Password in an Access database (ie in a back end)

   Public Function SSF_PwdCtl() As Boolean
    'CurrentDb.NewPassword "MyNewPassword", "MyOldPassword"

' all code from experts exchange
    'Dim WS As DAO.Workspace
    Dim sDbName As String
    Dim sPwdOld As String
    Dim sPwdNew As String
    Dim db As DAO.Database

    sDbName = "C:\SomeFolder\SomeDatabase.mdb"
    sPwdOld = InputBox("Old Pwd: ")
    sPwdNew = InputBox("New Pwd:")
    'sPwdOld = "***"
    'sPwdNew = "SomeNewPassword"   ' or ""
   
    Set db = DBEngine.Workspaces(0).OpenDatabase(sDbName, options:=True, readonly:=False, Connect:=";PWD=" & sPwdOld & ";")
   
    db.NewPassword sPwdOld, sPwdNew
   
    MsgBox "Pwd Accepted"
    Set db = Nothing
    SSF_PwdCtl = True

End Function

Note that using the Input Box is of course optional. You can certainly hard code the Old and New
Clive BeatonAccess Developer

Author

Commented:
Thanks, Joe.  Looks like just what I need.

Clive
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
You are welcome.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial