[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 634
  • Last Modified:

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

I am looking for code that will update the individual filepaths within a database that can be used for 100's of databases.  We recently changed servers and now I need to update the filepath for all the linked tables within all my databases.

Does anyone have some code that will simplify my task.  Right now I have been using the Linked table manager and opening each mdb individually

Keep in mind that 1 database can have numerous linked tables from various sources.

Any suggestions would be greatly appreciated.

Karen
0
Karen Schaefer
Asked:
Karen Schaefer
  • 12
  • 10
1 Solution
 
Nick67Commented:
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?
0
 
Karen SchaeferAuthor Commented:
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
0
 
Karen SchaeferAuthor Commented:
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
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
Nick67Commented:
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

Open in new window

0
 
Nick67Commented:
?
<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?
0
 
Karen SchaeferAuthor Commented:
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

Open in new window

0
 
Nick67Commented:
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
0
 
Nick67Commented:
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
0
 
Karen SchaeferAuthor Commented:
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

Open in new window

0
 
Nick67Commented:
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
0
 
Karen SchaeferAuthor Commented:
common folder - z: drive

Thanks,

Karen
0
 
Nick67Commented:
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?
0
 
Karen SchaeferAuthor Commented:
which ever is easiest  - I think copy code into each mdb,  because not all mdb are affected.
0
 
Nick67Commented:
Ok,
So are we done?
0
 
Karen SchaeferAuthor Commented:
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

Open in new window

0
 
Nick67Commented:
I always like a good Select Case True better than ElseIf
Try this


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\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
        'MsgBox linkstring
        If TD.Connect <> linkstring Then
            TD.Connect = linkstring
        End If
        TD.RefreshLink
           
    End If
Skip:
Next

Exit Function
myerr:
MsgBox TD.Connect
Resume Next

End Function
0
 
Karen SchaeferAuthor Commented:
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

Open in new window

0
 
Nick67Commented:
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"))
0
 
Karen SchaeferAuthor Commented:
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

Open in new window

0
 
Karen SchaeferAuthor Commented:
thanks
0
 
Nick67Commented:
No Problem!
<YES I understand the pitfalls of using drive letters instead of the full path>
What pitfalls do you know about?
0
 
Nick67Commented:
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
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 12
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now