repointing linked files 1

[CFMSACCESS]

I have an Access project with lots of linked files.

My problem is that the names and the locations of the linked files is different for different customers.

Is there a way to easily repoint the files to the correct names and locations without using the Link Manager?


Santa Rosa Web Development

 

[PHV]

Take a look here;

http://www.peterssoftware.com/tlk.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CFMSACCESS]

any other ideas?

 

[CFMSACCESS]

My Environment would have a var, "FILELOC" that would be the path unique to that site of the files for my app. On Form intialization, can I grab the var and translate, giving the path. Open all files relative to that path....



Santa Rosa Web Development

 

[Golom]

I use the following in VB but it should work in an Access Module.

Public Type ExistingTableLinks
    TableName As String
    Connect   As String
End Type


'-----------------------------------------------------------
' Procedure : ReLinkTables
' Purpose   : Reset the linked table paths
' Arguments : db    - The DAO Database for which paths are to be reset.
'-----------------------------------------------------------
'
Public Sub ReLinkTables(db As DAO.Database)

    Dim Tbl                 As DAO.TableDef
    Dim n                   As Integer
    Dim LinkedTables()      As ExistingTableLinks

    On Error Resume Next
    
    ' Build a list of the linked tables in the database
    For Each Tbl In db.TableDefs
        If (Tbl.Attributes And dbAttachedTable) Then
            Err.Clear
            ReDim Preserve LinkedTables(UBound(LinkedTables) + 1)
            If Err.Number <> 0 Then ReDim Preserve LinkedTables(0)
            LinkedTables(UBound(LinkedTables)).TableName = Tbl.Name
            LinkedTables(UBound(LinkedTables)).Connect = Tbl.Connect
        End If
    Next Tbl
    
    ' Drop and relink each of the linked tables
    If UBound(LinkedTables) >= 0 Then
        For n = 0 To UBound(LinkedTables)
            db.TableDefs.Delete LinkedTables(n).TableName
            Set Tbl = New DAO.TableDef
            Tbl.Name = LinkedTables(n).TableName
            
            If InStr(1, UCase$(LinkedTables(n).Connect), _
               UCase$("ChapsCore")) > 0 Then
                Tbl.Connect = ";DATABASE=" & Core_Path & "ChapsCore.Mdb"
            ElseIf InStr(1, UCase$(LinkedTables(n).Connect), _
                   UCase$("ChapsMast")) > 0 Then
                Tbl.Connect = ";DATABASE=" & Data_Path & "ChapsMast.Mdb"
            ElseIf InStr(1, UCase$(LinkedTables(n).Connect), _
                   UCase$("Trans")) > 0 Then
                Tbl.Connect = ";DATABASE=" & Data_Path & "Trans.Mdb"
            ElseIf InStr(1, UCase$(LinkedTables(n).Connect), _
                   UCase$("ChapsCtrl")) > 0 Then
                Tbl.Connect = ";DATABASE=" & Data_Path & "ChapsCtrl.Mdb"
            End If
            
            Tbl.SourceTableName = LinkedTables(n).TableName
            db.TableDefs.Append Tbl
            Set Tbl = Nothing
        Next n
    End If
    On Error GoTo 0

    Set Tbl = Nothing
End Sub

The variables "Data_Path" and "Core_Path" are just globals in my application that contain the path to the relevant databases. Because I have several databases and several potential locations for linked tables, I need to go through a process to figure out not only where the database is but which database it is as well. If you are linking to tables in only one external database it should simplify considerably.