Karen Schaefer
asked on
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
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
ASKER
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
ie. table1 - filepath= \\fil-nw-03\apdata.... etc and change it to s:\apdata\
Linktbl.png
ASKER
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
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!
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
?
<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?
<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?
ASKER
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
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.m db"
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
linkstring = "S:\" '=DB-Data on 'nw\data\Nwi&ds_fti'
It wants the full path to a db file
linkstring ="S:\ACCESS\LinkedTables.m
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
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-
'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
ASKER
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
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
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
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-
'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
ASKER
common folder - z: drive
Thanks,
Karen
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?
Or do you want to just paste your working code into a module in each one after you open it and execute it?
ASKER
which ever is easiest - I think copy code into each mdb, because not all mdb are affected.
Ok,
So are we done?
So are we done?
ASKER
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_Conv ersion_ISI S_Data.mdb and should be on O:\Development\ApDbms_Conv ersion_ISI S_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
this is what I have so far.
Tables in question are
ISISUpdateStatusLog - current filepath is C:\Development\ApDbms_Conv
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
almost there for some reason the code for the Masters directory is returning an extra value
ie. ;Database=X:s\RefDiags_Dia grams.mdb
the value should be x:\RefDiags/RefDiags_Diagr am.mdb
ie. ;Database=X:s\RefDiags_Dia
the value should be x:\RefDiags/RefDiags_Diagr
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
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"))
Case InStr(TD.Connect, "\\Fil-nw03-03\Masters\RefDiags") > 0
linkstring = ";Database=X:" & Right(TD.Connect, Len(TD.Connect) _
- Len(";Database=\\Fil-nw03-
ASKER
Thanks for your assistance here is my final code:
You have been a great help
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
ASKER
thanks
No Problem!
<YES I understand the pitfalls of using drive letters instead of the full path>
What pitfalls do you know about?
<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
On the other hand, debugging it might be more time-consuming than just copy-paste and run
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?