Looking for code to change filepath for all linked tables from various sources for multiple databases

Fun!
Do the tables and fields in these hundreds of databases have some commonality in their names?
It shouldn't be that hard to:

Automate Access

start Loop 'for the db
    open a db
     Start another loop 'for the table
          Start a third loop 'for the field
              Execute an update query against a table and field
          Loop 'next field
     Loop 'next table
     close the open db
Loop ' next db

BUT

The individual filenames, tablenames and fieldnames would need to be known.
They could be reamed out by code too, if needful

That would involve walking down each field in every table, check if it's text, if it's text check for your old \\ServerName\Pathname, if it's found, run the update.
It's doable
It could be sent to walk down all the files in all the folders on a drive if needful too.

The more you know upfront, the faster the resulting code.
How much do you know upfront?

Don't need to be concerned about the table fieldnames - just need to check if the filepath of the linked table = a certain filepath and if so change it to the new filepath.

ie.  table1 - filepath= \\fil-nw-03\apdata.... etc and change it to s:\apdata\

Linktbl.png

the attached gives example of tables that need to be updated - any table filepath = \\fil-nw03-03... for example will need to be updated to a drive letter - YES I understand the pitfalls of using drive letters instead of the full path.  this is how it is done in my department and what I have to work with.

Depending on the filepath will determine the proper drive letter.

ie.  AirplaneInfo should be on the S: drive.

Any suggestions.

Karen

Ok, I get it.
That's easier.
Yes the linked table manager can be automated
Here's code that updates my SQL Server linked tables.
You should be able to alter it to acces linked tables by changing the strings.
Turn on viewing system objects and you'll see the strings in MSysObjects table in the Connect field
The following code would do a single database

To do multiple db's a loop to walk thorough them would need to be constructed and CurrentDb would need to be replaced by an object reflecting the serially-opened db's.
Doable!

Function Refresh_Table_Link()
On Error GoTo myerr
Dim TD As TableDef
Dim linkstring As String
Dim intSubStringLoc As Integer
For Each TD In CurrentDb.TableDefs
    If Len(TD.Connect) > 0 Then
        intSubStringLoc = InStr(TD.Connect, "WhateverBadNameYouHaveOnTheGo")
        If intSubStringLoc > 0 Then
                linkstring = SomeCodeThatIsYourNewConnectionString
                If TD.Connect <> linkstring Then
                    TD.Connect = linkstring
                End If
            TD.RefreshLink
        End If
    End If
Next

Exit Function
myerr:
MsgBox TD.Name
Resume Next

End Function

Select allOpen in new window

?
<YES I understand the pitfalls of using drive letters instead of the full path>
I used drive letters.  That way when your scenario crops up--and it has twice for me--I just change the drive letter mapping in the logon script.
What pitfalls have you been warned against?

Ok this is what I have so far, and I get the popup dialog - how do I make it actual change the filepath?

Function Refresh_Table_Link()
On Error GoTo myerr
Dim TD As TableDef
Dim linkstring As String
Dim intSubStringLoc As Integer
For Each TD In CurrentDb.TableDefs
    If Len(TD.Connect) > 0 Then
        intSubStringLoc = InStr(TD.Connect, "\\Fil-nw03-03\db-data\")
        If intSubStringLoc > 0 Then
                linkstring = "S:\" '=DB-Data on 'nw\data\Nwi&ds_fti'
                If TD.Connect <> linkstring Then
                    TD.Connect = linkstring
                End If
            TD.RefreshLink
        End If
    End If
Next

Exit Function
myerr:
MsgBox TD.Name
Resume Next

End Function

Select allOpen in new window

I don't think this is going to cut it
linkstring = "S:\" '=DB-Data on 'nw\data\Nwi&ds_fti'

It wants the full path to a db file

linkstring ="S:\ACCESS\LinkedTables.mdb"

So, you are getting rid of \\Fil-nw03-03\db-data, which is 21 characters (if I counted right)
So
Linkstring = "s:" & Right(TD.Connect, Len(TD.Connect)-21)
might do it

Msgbox TD.Connect and msgbox linkstring  to make sure the syntax is right

This code worked in testing

Function Refresh_Table_Link()
On Error GoTo myerr
Dim TD As TableDef
Dim linkstring As String
Dim intSubStringLoc As Integer
For Each TD In CurrentDb.TableDefs
    If Len(TD.Connect) > 0 Then
        intSubStringLoc = InStr(TD.Connect, "\\Fil-nw03-03\db-data")
        'MsgBox TD.Connect
        If intSubStringLoc > 0 Then
                linkstring = ";Database=S:" & Right(TD.Connect, Len(TD.Connect) - Len(";Database=\\Fil-nw03-03\db-data"))
                'MsgBox linkstring
                If TD.Connect <> linkstring Then
                    TD.Connect = linkstring
                End If
            TD.RefreshLink
        End If
    End If
Next

Exit Function
myerr:
MsgBox TD.Name
Resume Next

End Function

that works great, however, I have a few stragglers that I need to handle - that do not use the same exact filepath.

see my code and feel free to modify.

K

Function Refresh_Table_Link()
On Error GoTo myerr
Dim TD As TableDef
Dim linkstring As String
Dim intSubStringLoc As Integer
For Each TD In CurrentDb.TableDefs
    If Len(TD.Connect) > 0 Then
        intSubStringLoc = InStr(TD.Connect, "\\Fil-nw03-03\db-data")
        'MsgBox TD.Connect
        If intSubStringLoc > 0 Then
                linkstring = ";Database=S:" & Right(TD.Connect, Len(TD.Connect) _
                    - Len(";Database=\\Fil-nw03-03\db-data"))
                'MsgBox linkstring
                If TD.Connect <> linkstring Then
                    TD.Connect = linkstring
                End If
            TD.RefreshLink
       elseif intSubStringLoc = InStr(TD.Connect, "\\Fil-nw03-03\masters\RegDiags")
                       linkstring = ";Database=S:" & Right(TD.Connect, Len(TD.Connect) Len(";Database=\\Fil-nw03-03\db-data"))
                'MsgBox linkstring
                If TD.Connect <> linkstring Then
                    TD.Connect = linkstring
                End If
            TD.RefreshLink
' X:\RefDiags\RefDiags_Diagrams.mdb
        End If
    End If
Next

Exit Function
myerr:
MsgBox TD.Name
Resume Next

End Function

Select allOpen in new window

Once you get it working for one, then you want to do many
Where are these located? A common folder or spread around?

To do many, you need to replace CurrentDb with one you open

Function Refresh_Table_Link()
On Error GoTo myerr
Dim TD As TableDef
Dim linkstring As String
Dim intSubStringLoc As Integer
Dim obj As New Access.Application

'for each db you need to do
'you need to build some code to work through them

obj.OpenCurrentDatabase (PathToTheDbYouWantToOpen)
For Each TD In obj.TableDefs
    If Len(TD.Connect) > 0 Then
        intSubStringLoc = InStr(TD.Connect, "\\Fil-nw03-03\db-data")
        'MsgBox TD.Connect
        If intSubStringLoc > 0 Then
                linkstring = ";Database=S:" & Right(TD.Connect, Len(TD.Connect) - Len(";Database=\\Fil-nw03-03\db-data"))
                'MsgBox linkstring
                If TD.Connect <> linkstring Then
                    TD.Connect = linkstring
                End If
            TD.RefreshLink
        End If
    End If
Next

obj.CloseCurrentDatabase
Set obj = Nothing

'and then loop up to the top and do the next one

 

Exit Function
myerr:
MsgBox TD.Name
Resume Next

End Function

common folder - z: drive

Thanks,

Karen

Do you want to do them in one routine?
Or do you want to just paste your working code into a module in each one after you open it and execute it?

which ever is easiest  - I think copy code into each mdb,  because not all mdb are affected.

Ok,
So are we done?

sorry not quite - I am a little confused - I have a few tables that are not on the same filepath - how do I check them.

this is what I have so far.

Tables in question are
ISISUpdateStatusLog - current filepath is  C:\Development\ApDbms_Conversion_ISIS_Data.mdb and should be on O:\Development\ApDbms_Conversion_ISIS_Data.mdb

and my current elseif statement does not seem to activate at the correct time for the RefDiags files - for the table names "TV_DiagProp"

K

Function Refresh_Table_Link()
On Error GoTo myerr
Dim TD As TableDef
Dim linkstring As String
Dim intSubStringLoc As Integer
For Each TD In CurrentDb.TableDefs
    
    If Len(TD.Connect) > 0 Then
        intSubStringLoc = InStr(TD.Connect, "\\Fil-nw03-03\db-data")
        'MsgBox TD.Connect
        If intSubStringLoc > 0 Then
                linkstring = ";Database=S:" & Right(TD.Connect, Len(TD.Connect) _
                    - Len(";Database=\\Fil-nw03-03\db-data"))
                'MsgBox linkstring
                If TD.Connect <> linkstring Then
                    TD.Connect = linkstring
                End If
            TD.RefreshLink
        ElseIf intSubStringLoc = InStr(TD.Connect, "\\Fil-nw03-03\Masters\RefDiags") Then
                linkstring = ";Database=X:" & Right(TD.Connect, Len(TD.Connect) _
                    - Len(";Database=\\Fil-nw03-03\Master\RefDiags"))
                'MsgBox linkstring
                If TD.Connect <> linkstring Then
                    TD.Connect = linkstring
                End If
            TD.RefreshLink
        End If
    End If
Next

Exit Function
myerr:
MsgBox TD.Connect
Resume Next

End Function

Select allOpen in new window

almost there for some reason the code for the Masters directory is returning an extra value

ie.  ;Database=X:s\RefDiags_Diagrams.mdb

the value should be x:\RefDiags/RefDiags_Diagram.mdb

Function Refresh_Table_Link()
On Error GoTo myerr
Dim TD As TableDef
Dim linkstring As String
Dim intSubStringLoc As Integer
For Each TD In CurrentDb.TableDefs
    If Len(TD.Connect) > 0 Then
        Select Case True
            Case InStr(TD.Connect, "\\Fil-nw03-03\db-data") > 0
                linkstring = ";Database=S:" & Right(TD.Connect, Len(TD.Connect) _
                    - Len(";Database=\\Fil-nw03-03\db-data"))
            Case InStr(TD.Connect, "\\Fil-nw03-03\ap-data\") > 0
                linkstring = ";Database=S:" & Right(TD.Connect, Len(TD.Connect) _
                    - Len(";Database=\\Fil-nw03-03\ap-data"))

            Case InStr(TD.Connect, "\\Fil-nw03-03\Masters\RefDiags") > 0
                linkstring = ";Database=X:" & Right(TD.Connect, Len(TD.Connect) _
                    - Len(";Database=\\Fil-nw03-03\Master\RefDiags"))

            Case InStr(TD.Connect, "C:\Development") > 0
                linkstring = ";Database=O:\Development" & Right(TD.Connect, Len(TD.Connect) _
                    - Len(";Database=C:\Development"))
            Case Else
                GoTo Skip
        End Select
        Debug.Print linkstring
        If TD.Connect <> linkstring Then
            TD.Connect = linkstring
        End If
        TD.RefreshLink
           
    End If
Skip:
Next

Exit Function
myerr:
'MsgBox TD.Connect
Debug.Print TD.Connect
Resume Next

Select allOpen in new window

Typo in Master

            Case InStr(TD.Connect, "\\Fil-nw03-03\Masters\RefDiags") > 0
                linkstring = ";Database=X:" & Right(TD.Connect, Len(TD.Connect) _
                    - Len(";Database=\\Fil-nw03-03\Master\RefDiags"))

Thanks for your assistance here is my final code:

You have been a great help

Function Refresh_Table_Link()
On Error GoTo myerr
Dim TD As TableDef
Dim linkstring As String
Dim intSubStringLoc As Integer
For Each TD In CurrentDb.TableDefs
    If Len(TD.Connect) > 0 Then
        Select Case True
            Case InStr(TD.Connect, "\\Fil-nw03-03\db-data") > 0
                linkstring = ";Database=S:" & Right(TD.Connect, Len(TD.Connect) _
                    - Len(";Database=\\Fil-nw03-03\db-data"))
            Case InStr(TD.Connect, "\\Fil-nw03-03\ap-data\") > 0
                linkstring = ";Database=Z:" & Right(TD.Connect, Len(TD.Connect) _
                    - Len(";Database=\\Fil-nw03-03\ap-data"))
            Case InStr(TD.Connect, "\\Fil-nw03-03\Masters\RefDiags") > 0
                linkstring = ";Database=X:" & Right(TD.Connect, Len(TD.Connect) _
                    - Len(";Database=\\Fil-nw03-03\Masters\"))

            Case InStr(TD.Connect, "C:\Development") > 0
                linkstring = ";Database=O:\Development" & Right(TD.Connect, Len(TD.Connect) _
                    - Len(";Database=C:\Development"))
            Case Else
                GoTo Skip
        End Select
        'Debug.Print linkstring
        If TD.Connect <> linkstring Then
            TD.Connect = linkstring
        End If
        TD.RefreshLink
           
    End If
Skip:
Next

Exit Function
myerr:
'MsgBox TD.Connect
'Debug.Print TD.Connect
Resume Next

End Function

Select allOpen in new window

thanks

No Problem!
<YES I understand the pitfalls of using drive letters instead of the full path>
What pitfalls do you know about?

We could have conceivably got a FileSystemObject on the go, pointed it at z:\, told it to look at all the files there and if they were mdb's run your working code.
On the other hand, debugging it might be more time-consuming than just copy-paste and run