Results 1 to 6 of 6
  1. #1
    xtyle86 is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2012
    Posts
    4

    Relinking multiple linked tables that are linked to different databases

    Hello,

    I have a database with a number of linked tables that are linked to tables in different databases (not a back-end)

    for example,
    I have table1 that is linked to table1 in K:\database\db1.mdb.
    table2 linked to table2 in S:\data\data.mdb.
    and so on...



    However, recently we have moved all our databases to a new location.
    K:\database\db1.mdb is now residing in O:\master\database
    and S:\data\data.mdb is now residing in O:\master\data
    and so on...

    I'm now in charge of relinking all those tables to point to the new location.
    I would do this in linked table manager one by one but we have 100s of tables linked to multiple different databases in different location.

    My question is,

    is there a way to create a VBA code that will automatically do this re-linking process?

    so,
    1. find unlinkable tables
    2. search its new location under O:\master
    3. re-link it to the new location

    Database names and tables names have not been changed. Just the location of databases.

    Thank you in advance,

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,045
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    xtyle86 is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2012
    Posts
    4

    Awesome

    This is exactly what I was looking for.

    Thank you very much!

  4. #4
    xtyle86 is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2012
    Posts
    4

    hm..

    Hi June7,

    I have used the code successfully.
    However, there is one problem.

    I've noticed that some tables are linked to databases that no longer exists. and when the code finds any of them it stops and will not apply the change.

    Ideally, I will have to manually select those and either relink or delete, but I have too many tables needing to be re-linked right now, so I want to worry about that problem later.

    How can I prevent the code from trying to refresh the tables and just change the name of the path?

    Thank you,

    Public Sub NormalizeTableLinks()
    'Relink tables with UNC pathing
    'Use the variables provided to do the manipulations desired
    Dim td As TableDef
    Dim db As DAO.Database
    Dim strOld As String
    Dim strNew As String
    'replace the following strings as needed
    strOld = "J:\aaa"
    strNew = "O:\aaa"
    Set db = CurrentDb
    For Each td In db.TableDefs
    If InStr(td.Connect, strOld) > 0 Then
    td.Connect = Replace(td.Connect, strOld, strNew)
    td.RefreshLink
    End If
    Next td
    db.TableDefs.Refresh
    End Sub

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,045
    What is being refreshed is a Link and a TableDef. I don't think can change the path without those actions. This code was given to me by a paid consultant early in project development. If I encountered missing table, which is rare that I delete a table before deleting the link, I had to manually delete the link. I can see how this would be a burden to you. You need code that can first check that table in the backend actually exists. I don't have that but this might be what you need http://www.pcreview.co.uk/forums/che...-t2762570.html

    Could probably go further and have the link removed if table doesn't exist.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    xtyle86 is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2012
    Posts
    4
    I am no VBA user, so probably will just manually delete those links.
    Thank you very much for your help tho!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 12-19-2011, 02:53 PM
  2. Split Multiple Linked Databases
    By sifar786 in forum Database Design
    Replies: 0
    Last Post: 12-11-2011, 03:26 AM
  3. avoid relinking linked tables
    By Hobbes29 in forum Import/Export Data
    Replies: 1
    Last Post: 10-01-2010, 11:15 PM
  4. Replies: 3
    Last Post: 06-14-2010, 06:48 PM
  5. Replies: 1
    Last Post: 08-19-2009, 01:14 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums