ODBC connection string password

Question

We are required to change our ODBC connection password on a periodic basis.  How do I modify the password that is stored in the MSysObjects table so I don't have to drop and reconnect to my tables in all of my Access 2010 databases everytime my password changes?

Answer 1

It is not recommended to modify data in the MSysObjects table, instead use the TableDef object.

Try something like this:

Public Function UpdatePassword(sOldPasswood As String, sNewPassword As String)

On Error GoTo PROC_ERR

    If Len(sOldPasswood) <> 0 And Len(sNewPassword) <> 0 Then

        Dim tdf As DAO.TableDef

        Dim qdf As DAO.QueryDef

        Dim sConnect As String

        ' Refresh Access Linked Tables

        For Each tdf In CurrentDb.TableDefs

            If InStr(tdf.Connect, sOldPasswood) Then

                tdf.Connect = Replace(tdf.Connect, "PWD=" & sOldPasswood, "PWD=" & sNewPassword)

                tdf.RefreshLink

            End If

        Next

        ' Refresh Access PassThrough Queries

        For Each qdf In CurrentDb.QueryDefs

            If InStr(qdf.Connect, sOldPasswood) Then

                Call Replace(qdf.Connect, "PWD=" & sOldPasswood, "PWD=" & sNewPassword)

            End If

        Next

    Else

        MsgBox "An Old Password and New Password are required!", vbOKOnly + vbCritical, "Error"

    End If

PROC_EXIT:

    Exit Function

PROC_ERR:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure UpdatePassword"

    Resume PROC_EXIT

    Resume

End Function

'Updated code

Answer 2

Are you connecting to SQL Server? If so, I would use Doug Steele's code to set the connection property http://www.accessmvp.com/DJSteele/DSNLessLinks.html.