Relinking Tables *Problem

  • Thread starter Thread starter cbjames
  • Start date Start date
C

cbjames

I have my Db split; programming and data. When I go to change the link
location through the Link Table Manager it will not link all of the tables at
one time. I have to link them one at a time. What's up? I can link them all
at once when the backend locaton changes on some of the Dbs but not others.
Anyone know the solution?

Thanks!
 
Here is code I use to do batch change on table links. Set the strOld and
strNew variables for your situation. This example sets links to UNC pathing.

Option Compare Database
Option Explicit

Public Sub NormalizeTableLinks()
'Easier relinking of tables when cases are different in paths, etc.
'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

strOld = "P:\airports\Report Production\GIS\Airports.mdb"
strNew = "\\Fritz\admin\Airports.mdb"

Set db = CurrentDb

For Each td In db.TableDefs
If InStr(td.Connect, strOld) > 0 Then
Debug.Print td.Name
Debug.Print "Old Link: " & td.Connect
td.Connect = Replace(td.Connect, strOld, strNew)
td.RefreshLink
Debug.Print "New Link: " & td.Connect
End If
Next td

db.TableDefs.Refresh

End Sub
 
Hi James,

I have experienced this frustrating problem myself. I usually use re-linking
code, similar to the reply provided by "June7", but I have seen this issue
when attempting to manually re-link using the Linked Table Manager. The only
way I've found in the past to fix this problem is to delete all linked
tables, compact the FE database (hold the Shift key down to prevent startup
routines), and then re-establish the linked tables from scratch, using File |
Get External Data | Linked tables... (or the equivalent in Access 2007).
After doing this, the linked table manager seems to work again for re-linking
without stopping at each table.

Access MVP Armen Stein has some very good re-linking code available on his
web site. Check out "J Street Access Relinker" (currently the first download
shown):

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
Back
Top