linked table manager

[ProgramError]

Is there a way of calling the linked table manager using VBA?

Usually found under tools/database utilities/linked table manager.

I would like to call this to alter the back end links. Maybe you know a better way?




Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[JerryKlmns]

Many posts here, using ADOX or DAO, trying to link or relink.

 

[randy700]

You will need to make some minor modifications, but this is the code I use.

Public Function Link2BE()
[COLOR=green]'Recreate the links to tables in the back end database. Links are removed first to prevent the possibility of duplicating tables. It may be necessary to reset the name of the back end database in the 5th line below...[/color]
    Dim tdf As TableDef
    Dim i As Integer, j As Integer
    Dim strBE As String
    If MsgBox("Relink all tables?", vbYesNo + vbQuestion, strTitle) = vbNo Then Exit Function
    strBE = Application.CurrentProject.Path & "\[i][COLOR=red]DatabaseName.mdb[/color][/i]"
    Set db = CurrentDb
    On Error Resume Next
    DoCmd.Hourglass True
    For i = 0 To db.TableDefs.Count - 1
        Set tdf = db.TableDefs(i)
        If tdf.Properties(4) <> "" Then
            If Left(tdf.Name, 4) <> "msys" Then
                DoCmd.DeleteObject acTable, tdf.Name
            End If
        End If
    Next i
    Set tdf = Nothing
    Set db = Nothing
    Set db = DBEngine.Workspaces(0).OpenDatabase(strBE)
    j = 0
    For i = 0 To db.TableDefs.Count - 1
        Set tdf = db.TableDefs(i)
        If Left(tdf.Name, 4) <> "msys" Then
            DoCmd.TransferDatabase acLink, "Microsoft Access", _
                  strBE, acTable, tdf.Name, tdf.Name
        Else
            j = j + 1
        End If
    Next i
    DoCmd.Hourglass False
    MsgBox db.TableDefs.Count - j & " tables relinked.", vbOKOnly + vbInformation, strTitle
    Set tdf = Nothing
    Set db = Nothing
End Function

Randy

 

[PHV]

Is there a way of calling the linked table manager using VBA?
DoCmd.RunCommand acCmdLinkedTableManager

You may have a look here:

http://www.mvps.org/access/tables/tbl0009.htm

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