Re Link data

Hi EE
I have the following code to re-link data
Dim strPathBE As String
Dim tdf As TableDef

With FileDialog(msoFileDialogFilePicker)
    If Not .Show Then Exit Sub
    strPathBE = .SelectedItems(1)
End With
With CurrentDb
    For Each tdf In .TableDefs
        If tdf.Attributes And dbSystemObject Then
        ElseIf Len(tdf.Connect) = 0 Then
        ElseIf Left(tdf.Connect, 1) <> ";" Then
        Else
            tdf.Connect = ";DATABASE=" & strPathBE
            tdf.RefreshLink
        End If
    Next tdf
    Me!txtLoc = strPathBE
   
End With

It works if data has no password. I assume it needs some extra code to handle password.
something at the end of this line perhaps
 tdf.Connect = ";DATABASE=" & strPathBE (Password)

Any help appreciated

chestera
chesteraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
first open the BE

Dim strPathBE As String
Dim tdf As TableDef
dim strPwd As String
dim dbBE as Dao.database

strPwd="yourPassword"

 With FileDialog(msoFileDialogFilePicker)
     If Not .Show Then Exit Sub
     strPathBE = .SelectedItems(1)
 End With
 
 'open the back end
 
 Set dbBE = DBEngine(0).OpenDatabase(strPathBE, False, True, ";pwd=" & strPwd)
 
 'Set Connect property
 
 tdf.Connect = ";Database=" & strPathBE & ";PWD=" & strPwd
chesteraAuthor Commented:
Rey Obrero

Thank you I will run it now

Alan
chesteraAuthor Commented:
Rey Obrero
getting error message "Object variable or with block variable not set. on the following line

tdf.Connect = ";Database=" & strPathBE & ";PWD=" & strPwd

alan
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Download eBook now!
Rey Obrero (Capricorn1)Commented:
with dbBE
    For Each tdf In .TableDefs
          tdf.Connect = ";Database=" & strPathBE & ";PWD=" & strPwd

    next
end with
Rey Obrero (Capricorn1)Commented:
oops, use this


with currentdb
     For Each tdf In .TableDefs
           tdf.Connect = ";Database=" & strPathBE & ";PWD=" & strPwd

     next
 end with
chesteraAuthor Commented:
Rey Obrero

Getting Invalid Operation tdf Connect line

Dim strPathBE As String
Dim tdf As TableDef
Dim strPwd As String
Dim dbBE As DAO.Database

strPwd = "ep2015ms"

 With FileDialog(msoFileDialogFilePicker)
     If Not .Show Then Exit Sub
     strPathBE = .SelectedItems(1)
 End With
 With CurrentDb
    For Each tdf In .TableDefs
        tdf.Connect = ";Database=" & strPathBE & ";PWD=" & strPwd
    Next
 End With

Alan
Rey Obrero (Capricorn1)Commented:
@alan, use the codes you were using above

With CurrentDb
     For Each tdf In .TableDefs
         If tdf.Attributes And dbSystemObject Then
         ElseIf Len(tdf.Connect) = 0 Then
         ElseIf Left(tdf.Connect, 1) <> ";" Then
         Else
             tdf.Connect = ";Database=" & strPathBE & ";PWD=" & strPwd
             tdf.RefreshLink
         End If
     Next tdf
     Me!txtLoc = strPathBE
     
 End With
chesteraAuthor Commented:
Rey Obrero

This is what I now have. Still getting error message last line Object variable etc. If I leave the last two lines out there is no error but it wont link to the data.
Dim strPathBE As String
Dim tdf As TableDef
Dim strPwd As String
Dim dbBE As DAO.Database

strPwd = "ep2015ms"

 With FileDialog(msoFileDialogFilePicker)
     If Not .Show Then Exit Sub
     strPathBE = .SelectedItems(1)
 End With
 With CurrentDb
    For Each tdf In .TableDefs
        If tdf.Attributes And dbSystemObject Then
            ElseIf Len(tdf.Connect) = 0 Then
            ElseIf Left(tdf.Connect, 1) <> ";" Then
        Else
            tdf.Connect = ";DATABASE=" & strPathBE & ";PWD=" & strPwd
            tdf.RefreshLink
        End If
    Next tdf
   
   
End With
 'open the back end
 
 
 Set dbBE = DBEngine(0).OpenDatabase(strPathBE, False, True, ";pwd=" & strPwd)
 
 'Set Connect property
 
 tdf.Connect = ";Database=" & strPathBE & ";PWD=" & strPwd

Alan
Rey Obrero (Capricorn1)Commented:
copy and paste these codes


Dim strPathBE As String
Dim tdf As DAO.TableDef
Dim strPwd As String
Dim dbBE As DAO.Database
Dim dbs as dao.database

 strPwd = "ep2015ms"

  With FileDialog(msoFileDialogFilePicker)
      If Not .Show Then Exit Sub
      strPathBE = .SelectedItems(1)
  End With
 
   'open the back end
   
  Set dbBE = DBEngine(0).OpenDatabase(strPathBE, False, True, ";pwd=" & strPwd)
 
  set dbs=currentdb

     For Each tdf In dbs.TableDefs
          If tdf.Connect <> "" Then
             tdf.Connect = ";DATABASE=" & strPathBE & ";PWD=" & strPwd
             tdf.RefreshLink
         End If
     Next tdf

 dbBE.Close
Set dbBE = Nothing

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chesteraAuthor Commented:
Rey Obrero

Got it, it works like a charm. Many thanks for your help

Alan
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.