Changing linked table location programatically (1 Viewer)

J

Jaye7

Registered User.
Local time
Today, 13:37
Joined
Aug 19, 2014
Messages
205
I am after a script to change the linked table paths like the following.

It will only ever be the path that is changing not the file name and only ever linked tables

Code: 
for each table in tabledefs
if table.path = c:\Testing* then table.path = w:\Testing\filename
if table.path = c:\Jobs* then table.path = w:\Jobs\filename
if table.path = c:\Quotes* then table.path = w:\QuotesNew\filename
next table
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 04:37
Joined
Sep 12, 2006
Messages
15,634
a linked table stores within it's structure the path to the linked table. so changing the path means changing the linked tables

you can either change the stored path and refresh the link (I think that works), or what I do, which is for each linked table to delete the link, and rebuild the link.

this sort of think.

for each linked table
update link
next


(sorry, I haven't got time to post full code)
 
S

smig

Registered User.
Local time
Today, 06:37
Joined
Nov 25, 2009
Messages
2,209
Code: 
Dim tdf As DAO.TableDef

For Each tdf In CurrDB.TableDefs
    ' If the table has a connect string, it's a linked table.
    If Len(tdf.Connect) > 0 Then
        tdf.Connect = "MS Access;PWD=" & dbFilePass & " ;DATABASE=" & dbFileFullPathAndName& " "
        tdf.RefreshLink         ' Relink the table.
    End If
Next tdf
dbFilePass - your BE db password
dbFileFullPathAndName - Full path + BE db full name
 
J

Jaye7

Registered User.
Local time
Today, 13:37
Joined
Aug 19, 2014
Messages
205
The problem I have is that there are actually around 6 back end databases that the various tables are linked to, that's why I wanted the if statements, if c:\Jobs, c:\Quotes etc...

I did not set the back end databases up this way, just working with pre-existing databases.

Maybe if I had a script to list the Original linked path's in a table and then in the 2nd column I could copy and paste the table names and adjust the paths and then run an update script to change original link path to NewPath(field) names.

Are you able to help with that.
 
essaytee

essaytee

Need a good one-liner.
Local time
Today, 13:37
Joined
Oct 20, 2008
Messages
512
Jaye7 said:
The problem I have is that there are actually around 6 back end databases that the various tables are linked to, that's why I wanted the if statements, if c:\Jobs, c:\Quotes etc...

I did not set the back end databases up this way, just working with pre-existing databases.

Maybe if I had a script to list the Original linked path's in a table and then in the 2nd column I could copy and paste the table names and adjust the paths and then run an update script to change original link path to NewPath(field) names.

Are you able to help with that.
Click to expand...

All that is required is passing in the full path and filename of your backend databases. In your case the function would be run six times.

Here is a function I use in one of my applications (written many years ago):

Code: 
Function LinkTables(pStr_File As String) As Boolean
On Error GoTo Err_LinkTables

    ' following two Dim assignments were assigned at the Module level
    ' included in function for demo purposes 
    Dim gWs As Workspace       ' was Global gWs as Workspace
    Dim gDb As Database         ' was Global gDb as Database


    Dim dbBackEnd As Database
    Dim tdfExtTables As TableDefs
    Dim tdfLocal As TableDef
        
    Dim intTableCount As Integer
    Dim intK As Integer
    Dim intX As Integer
    Dim strPrefix As String
    Dim strLinkTableName As String
                
    Dim booSuccess As Boolean
        
    Set dbBackEnd = gWs.OpenDatabase(pStr_File)
    Set tdfExtTables = dbBackEnd.TableDefs
        
    intTableCount = tdfExtTables.Count - 1
    strPrefix = "MSys"
    
 
    For intK = 0 To intTableCount
      
        strLinkTableName = tdfExtTables(intK).Name
        
        If Not Left$(strLinkTableName, 4) = strPrefix Then
            Set tdfLocal = gDb.CreateTableDef(strLinkTableName)
            With tdfLocal
                .Connect = ";DATABASE=" & pStr_File
                .SourceTableName = strLinkTableName
            End With
                        
            gDb.TableDefs.Append tdfLocal
                        
            intX = intX + 1
                        
         End If
                 
    Next intK
    
    gDb.TableDefs.Refresh
        
    booSuccess = True
     
Exit_LinkTables:
    LinkTables = booSuccess
    Exit Function
    
Err_LinkTables:
    
    booSuccess = False
     Resume Exit_LinkTables

End Function

I've removed some unnecessary lines (necessary for my application) only to make it more to the point. Also added the first two Dim statements gWs & gDb. I do have error routines but removed them for the time being as I'd have to fiddle with them as well (time). You must first unlink/delete existing linked tables otherwise errors will be generated.


Notes:
pStr_File = Is your full path and file name of your backend data file.

Hope this helps.

Steve.
 
J

Jaye7

Registered User.
Local time
Today, 13:37
Joined
Aug 19, 2014
Messages
205
Thanks for your help everyone,
I modified a few scripts and came up with the following which I will add my other 4 database scripts to, just used 2 for testing at this stage

Code: 
Sub test()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
    
Dim NewLink As String, NewLink1 As String
NewLink = "w:\Price List\Supply_2002_be.mdb"
NewLink1 = "w:\Price List\Jobs_2002_be.mdb"

On Error GoTo Err1
For Each tdf In dbs.TableDefs
    ' If the table has a connect string, it's a linked table.
    If Len(tdf.Connect) > 0 Then
    If Left(tdf.Connect, 19) = ";DATABASE=x:\Supply" Then
        Debug.Print tdf.Connect
            tdf.Connect = "MS Access; PWD=" & dbFilePass & " ;DATABASE=" & NewLink 'dbFileFullPathAndName
            tdf.RefreshLink         ' Relink the table.
    
    ElseIf Left(tdf.Connect, 17) = ";DATABASE=x:\Jobs" Then
        Debug.Print tdf.Connect
            tdf.Connect = "MS Access; PWD=" & dbFilePass & " ;DATABASE=" & NewLink1 'dbFileFullPathAndName
            tdf.RefreshLink         ' Relink the table.
    
    End If
    End If
    
Next tdf
MsgBox "Tables Linked - Any Errors Follow" & vbLf & s1
Exit Sub
Err1:
Debug.Print "This table path could not be changed " & tdf.Name & " " & tdf.Connect
s1 = s1 & vbLf & vbLf & "This table path could not be changed " & tdf.Name & " " & tdf.Connect
Resume Next
End Sub
 
D

DBQueen

New member
Local time
Today, 13:37
Joined
Jul 13, 2017
Messages
6
Hi Everyone,

I know this post was ages ago but useful to me now. Thanks!
 
G

Galaxiom

Super Moderator
Staff member
Local time
Today, 13:37
Joined
Jan 20, 2009
Messages
12,851
I usually just do a Replace() on the Connect property. Then RefreshLink.

Finally a RefreshDatabaseWindow to update the location in the tooltip.
 
Minty

Minty

AWF VIP
Local time
Today, 04:37
Joined
Jul 26, 2013
Messages
10,366
Very old post but I never thought of simply doing this.

Thanks!
 
You must log in or register to reply here.

Similar threads

J
Help with using ADOX to Set TableDef connect property in foreign DB w/out actually connecting
Replies
4
Views
207
Pat Hartman
P
N
Solved Mass Importing Txt Files
Replies
14
Views
380
ebs17
E
S
No of Files Count
Replies
14
Views
432
The_Doc_Man
The_Doc_Man
R
Updating Excel spreadsheet from within the Access
Replies
8
Views
323
CJ_London
C
T
Using multiple criteria in DCOUNT function using a comparison
Replies
1
Views
129
June7
June7

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom