Re Link data

chestera
chestera used Ask the Experts™
on
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
Comment
Watch Question

Do more with

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

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

Author

Commented:
Rey Obrero

Thank you I will run it now

Alan

Author

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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016

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

    next
end with
Top Expert 2016

Commented:
oops, use this


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

     next
 end with

Author

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
Top Expert 2016

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

Author

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
Top Expert 2016
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

Author

Commented:
Rey Obrero

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

Alan

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