Changing linked table location (that protected with a password) programatically on acccess 2013

bill201
bill201 used Ask the Experts™
on
hi

how can i with vba relink a table to database that closed with password. for example i have this code  
 For Each td In TDS
        If td.Connect = ";DATABASE=" & OldPathName Then
            td.Connect = ";DATABASE=" & NewPathName, passowrd:mypassword'
            td.RefreshLink 'Refresh the link

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Try this

Public Function FixTableLink()
 
Dim db As Database
Dim strPath As String
Dim strConnect As String
 
strPath = "NewPathName"

 
strConnect = ";DATABASE=" & strPath & "; password:mypassword"
 
Set db = CurrentDb
 
For Each tbl In db.TableDefs
 
    If Nz(DLookup("Type", "MSysObjects", "Name = '" & tbl.name & "'"), 0) = 6 And tbl.Connect <> strConnect Then
         
        tbl.Connect = strConnect
        tbl.RefreshLink
         
    End If
 
Next tbl
 
End Function
Commented:
sorry connection string should be

strConnect = ";DATABASE=" & strPath & "; PWD=mypassword"

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