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.
A new and improved Linked Table Manager is available to O365 subscribers starting version 1808 (Build 10730.20088).
Please continue to provide feedback and help us improve it further.
Hey Neil Ginsberg,
I just loved the idea that you have shared to use Access Database Linked Table Manager. I also a newbie to this feature of Access database. Recently when I have also searched tutorial regarding how to use Access Database Linked Table Manager then I have found this informative post. Hopefully it will help other too just like the way it helped me.
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.
Christopher Daley commented
About time. Hey it’s only been about 20 years.
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
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
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
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
You could automate this process. I only link to one SQL server, but no limitation on retrofitting the below hack function:
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
MsgBox "SQL Relink Completed"
Ben Sacheri commented
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. :)