Programmatically changing the password on a .accdb or .accde Database (1 Viewer)

[msowards]

Hi All,
I have a little Access app DB that has an option to let the user assign a new password to the database. In the .mbd or .mde format the option worked fine. But when I switched to the .accdb format the change password option failed with the error message the the file was already locked by another process.

The original code

 DBPath = [CurrentProject].[FullName]
                    Dim dbsDB      As DAO.Database
                    Dim strOpenPwd As String
                
                   ' Create connection string by using current password.
                    strOpenPwd = ";pwd=" & OldPswd
                
                    ' 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:=DBPath, _
                                             Options:=True, _
                                             ReadOnly:=False, _
                                             Connect:=strOpenPwd)
                
                    ' Set or change password.
                    With dbsDB
                      .NewPassword OldPswd, Pswd2
                      .Close
                    End With
                    Me.DB_Pswd = Pswd2
                
                    Set dbsDB = Nothing

allows the user to change the password on the current DB even though it is open. If the file was a .MDb or .MDE

I tried the below code for the ADO version for the .accdb or .accde format

                  Dim ADO_Cnnct As adodb.Connection, strAlterPassword As String
                     strAlterPassword = "ALTER DATABASE PASSWORD [" & Pswd2 & "] [" & OldPswd & "];"
                  
                    Set ADO_Cnnct = New adodb.Connection
                    With ADO_Cnnct
                         .Mode = adModeShareExclusive
                        
                        .Provider = "Microsoft.ACE.OLEDB.12.0"
                        '  Use old password to establish connection
                        .Properties("Jet OLEDB:Database Password") = "OldPswd"
                        
                        'name  current DB
                        
                        DBPath = [CurrentProject].[FullName]
                        
                        .Open "Data Source= " & DBPath & ";"
                        ' Execute the SQL statement to change the password.
                        .Execute (strAlterPassword)
                    End With
                    
                     'Clean up objects.
                    ADO_Cnnct.Close
                    Set ADO_Cnnct = Nothing

With this code the process still errors out this time however I keep getting file already in use error.

 

[Khalid_Afridi]

I don't understand WHY it is required to change Database password by any user? this is against the security of any database and leads to BIG mess

 

[msowards]

If it is the users Database they want to change and control their password. All tool bars are hidden there is no way for them to change it other than through coding.

 

[Khalid_Afridi]

If it is the users Database they want to change and control their password.

changing their passwords is other than to change the whole database password. Its beyond my knowledge why one should change the database password if others also will use the same database, this will prevent them to log in.

users password can be changed by other means.

 

[msowards]

Thanks for your concern about the over all design, but that's not what I'm asking about. This a a personal sized DB only one individual will use each edition. I feel a DB password is more secure that individual passwords. So I will re-iterate my original question does anyone know how to use VBA to change the DB password on a .accdb/e format Access Database? Thanks

 

[nyandukosi]

hello pple

 

[mdlueck]

Perhaps by the mere fact that the FE DB is accessing the BE DB is preventing the FE from changing the BE's password.

Try opening it directly and changing it there.

Perhaps you may not do what you are attempting via VBA code, but manual operations would succeed.

I use ADOX to update Linked Table Object credentials on the fly as my FE DB application loads. In my case, the BE DB is SQL, not Access.

Linked Table Saved Password ADOX code
http://www.access-programmers.co.uk/forums/showthread.php?t=226468#post1155700