Link to home
Create AccountLog in
Avatar of chestera
chestera

asked on

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
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

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
Avatar of chestera
chestera

ASKER

Rey Obrero

Thank you I will run it now

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

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

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

    next
end with
oops, use this


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

     next
 end with
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
@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
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
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Rey Obrero

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

Alan