locked
Automate changing of back-end password and relinking front-ends? RRS feed

  • Question

  • 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?

    Wednesday, February 12, 2014 4:38 PM

Answers

  • 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

    Wednesday, February 12, 2014 5:28 PM

All replies

  • 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

    Wednesday, February 12, 2014 5:28 PM
  • Thanks, I'll give it a try. Please post an update if you come across any issues.
    Wednesday, February 12, 2014 5:38 PM