Feedback by UserVoice

I suggest you ...

Linked Table Manager: select all tables from same back end

One of the biggest pains is when you have multiple back ends and Linked Table Manager (LTM) prompts you at every table. When you have lots of links and multiple back ends, it's a pain to go through and select all from only one back end.

Idea #1: Click on a single table in LTM, then click a button: "Select all tables from same back end." Voila! Now you can update that single back end, and then move on to the next one.

Idea #2: Sort the tables by back end. Not as convenient as Idea #1, but would still make life a heck of a lot easier when you're trying to update links from multiple back ends.

Ideally, what would be best would be to able to select all tables, and just have LTM prompt you once for each back end location. But that would require rewriting the code a bit, I get it. The above two ideas are both very simple to implement without having to rewrite the existing code.

40 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    Neil Ginsberg shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    8 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • JimmB commented  ·   ·  Flag as inappropriate

        One change that would eliminate a lot of the hassle is to allow RELATIVE PATHNAMES to linked tables. Often my linked tables are in the same folder as the DB, or a subfolder thereof.

        This also makes it much easier to move a database to a machine with a different data structure. For example, I usually keep my data on a D: partition, but some machines don't have a D:, so I have to use a folder on C:. Also, If I have the DB on a USB drive, it may set up as a different drive letter on different machines, forcing me to relink all the tables.

      • Dolly commented  ·   ·  Flag as inappropriate

        Instead of iterating table by table, it should be iterating BE by BE and only opening and closing the connection once. I think one of your MVPs has a sample that I can't locate now. You should speak with them. A lot of them have rolled out far superior solutions over the years that you should be seriously looking at.

      • Mark Phillipson commented  ·   ·  Flag as inappropriate

        As well as selecting all tables in a particular backend at a time. Can we have pass through queries also included so we can update their Connection Strings all in one go.

      • Jacqui Evanchik commented  ·   ·  Flag as inappropriate

        I like Niel's Idea #1 or #2, either would alleviate this common problem. I sometimes need to name my tables with different prefixes ('t', 't_', 'tbl', '_t') to indicate which back-end they are in, but this is awkward for development, too easy to use the wrong prefix, and a disaster when tables are moved to another back-end. Otherwise I find it's easier to skip the LTM and just delete all linked tables, and go through the process of linking tables from each db. Neither is a good solution. Lets fix the LTM!

      • Mark Jaskula commented  ·   ·  Flag as inappropriate

        One obvious way to improve the Linked Table Manager is to allow Shift key to work so that you can select a range of tables in one go instead of doing Control click on every table, very poor design. Also remove all the sql internal tables not relevant to an Access Database would be a bonus, but the shift select would be a boon.

      • Mark Jaskula commented  ·   ·  Flag as inappropriate

        You could automate this process. I only link to one SQL server, but no limitation on retrofitting the below hack function:

        Function ReLinkSQLDatabase()
        Dim i As Long
        Dim tableName As String
        Dim rec As Recordset
        Dim r As Long

        For i = 0 To CurrentDb.TableDefs.count - 1
        tableName = LCase(CurrentDb.TableDefs(i).name)

        If InStr(1, tableName, "msys") = False And InStr(1, tableName, "~") = False And InStr(1, tableName, "temp_") <> 1 And tableName <> "name autocorrect save failures" And tableName <> "paste errors" Then
        DoCmd.DeleteObject acTable, tableName
        DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DRIVER=ODBC Driver 13 for SQL Server;UID=administrator;DATABASE=tsfe;WSID=razorbladeAPP=2016 Microsoft Office system;Trusted_Connection=Yes;SERVER=razorblade;DESCRIPTION=MSSQL_TSFX", acTable, tableName, tableName, False, True
        End If
        Next

        MsgBox "SQL Relink Completed"
        End Function

      • Ben Sacheri commented  ·   ·  Flag as inappropriate

        I've written my own Linked Table Manager using a ListView so I can sort by back end and adjust the column widths. It should be low effort for Microsoft to update the antiquated Linked Table Manager, if they can still find the source code. :)

      Feedback and Knowledge Base