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
Microsoft Access

Avatar of undefined
Last Comment
chestera

8/22/2022 - Mon
Rey Obrero (Capricorn1)

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
chestera

ASKER
Rey Obrero

Thank you I will run it now

Alan
chestera

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

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

alan
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Rey Obrero (Capricorn1)

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

    next
end with
Rey Obrero (Capricorn1)

oops, use this


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

     next
 end with
chestera

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rey Obrero (Capricorn1)

@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
chestera

ASKER
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
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
chestera

ASKER
Rey Obrero

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

Alan
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes