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.
We are designing on a new Linked Table Manager dialog, incorporating suggestions and requests made in UserVoice and other forums.
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. :)