I have recently implemented a password on the back end of a database.
I am aware that an Access password is not really secure.
There are about twenty front-ends that link to this back-end.
In addition, there are many archived back-end databases in a separate folder.
I need a quick way to change the passwords (on the back-end and all archived back-ends) and relink all the front-ends.
I do not want to do this manually as it will have to be done on a regular schedule.
Has anyone come up with a solution to doing this?
I've only done it in a test I made just now, but this seems to work:
Sub TestChgPassword()
Dim dbBE As DAO.Database
Dim dbFE As DAO.Database
Dim tdf As DAO.TableDef
Dim strBEPath As String
Dim strFEPath As String
Dim strOldPassword As String
Dim strNewPassword As String
strOldPassword = "pwd"
strNewPassword = "newpwd"
strBEPath = "C:\Users\Dirk\Documents\DBPwd.accdb"
strFEPath = "C:\Users\Dirk\Documents\LinkToDBPwd.accdb"
' Open the back-end database exclusively.
Set dbBE = Application.DBEngine.OpenDatabase(strBEPath, True, False, ";PWD=" & strOldPassword)
dbBE.NewPassword strOldPassword, strNewPassword
dbBE.Close
Set dbFE = Application.DBEngine.OpenDatabase(strFEPath, True, False)
For Each tdf In dbFE.TableDefs
With tdf
If InStr(.Connect, strBEPath) > 0 Then
.Connect = Replace(.Connect, "PWD=" & strOldPassword, "PWD=" & strNewPassword)
.RefreshLink
End If
End With
Next tdf
dbFE.Close
End Sub
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html
