Detech Table Link Path then re-link accordingly 2

[tobymom]

Hello, I always appreciate expert helps in this forum.

Here's what I'd like to do with VBA coding:

1. Upon mde file open and switch board opens, check if a table is link to R:\tables.mdb

2. If not, un-link all current tables then re-link to R:\tables.mdb

Thank you in advance.

 

[bubba100]

Does faq700-1584 help.

 

[Golom]

Public Sub RelinkToR()
   Dim rs As DAO.Recordset
   [COLOR=black cyan]' Find out if there are any tables linked from 'R:\tables.mdb[/color]
   Set rs = CurrentDB.OpenRecordset( _
            "SELECT S.Name, S.Database " & _
            "FROM MSysObjects AS S " & _
            "WHERE S.Type=6 AND S.Database = 'R:\tables.mdb'"

   If NOT rs.EOF then
      RelinkTables "R:\tables.mdb"
   End If
   Set rs = Nothing
End Sub

'---------------------------------------------------------------------------------------
' Procedure : ReLinkTables
' Purpose   : Reset the linked table paths
' Arguments : NewConnect - The new connect path & file
'---------------------------------------------------------------------------------------
'
Public Sub ReLinkTables(NewConnect As String)

    Dim tbl                         As DAO.TableDef

    ' Find the linked tables in the database
    For Each tbl In CurrentDB.TableDefs
        If (tbl.Attributes And dbAttachedTable) And _
            tbl.Connect <> NewConnect Then
            tbl.Connect = NewConnect
            tbl.RefreshLink
        End If
    Next tbl

End Sub

Note that this will fail if a linked table does not exist in "R:\Tables.mdb

 

[Golom]

Just for the sake of being precise
This

tbl.Connect = NewConnect

should be

tbl.Connect = [red]";DATABASE=" &[/red] NewConnect

 

[tobymom]

Bubba100: Thank you for the pointer.

Golem: Thank you for your code. I haven't tested it yet but it looks very promising.

 

[Remou]

I am inclined to keep a 'table of tables', that is, a list of tables that should be linked to the database. This list can then be used to check the linked tables and to re-link any missing or incorrectly linked tables.