<

A more efficient Linked Table Manager

Published on
11,434 Points
1,934 Views
5 Endorsements
Last Modified:
Editors:
Are you ready for a Linked Table Manager (LTM):
- with a sizable form
- which identifies which connections are valid and how many tables are associated with the connection
- which doesn't ask you to select the BE multiple times when you've identified tables from multiple sources to refresh?

For years, the Access community has been forced to deal with a Linked Table Manager (LTM) which has none of the characteristics mentioned above.  Although Microsoft has announced that they will soon be releasing a new Linked Table Manager, this new version will only be available in Office 16 and Office 365.  In the mean time, I have developed a LTM for Access and SQL Server (will be expanded to other data sources) which can be used by all versions of Access starting at 2007.  This LTM is efficient, user-friendly, and makes it easy to either refresh links, relink tables from one or more data sources to another BE (either Access or SQL Server), or link new tables from either an Access or SQL Server backend (with DSN-less connections).  Additionally, when working with SQL Server, you can elect to drop or retain the schema name (usually "dbo_") when you link tables.


There isn't an installation package for this add-in yet (eliminates the need for a sysAdmin to install the application), but installing the add-in on your system is quite simple.


1.  Download the version (32 or 64 bit) of the file (below) which corresponds to your version of Office.

2.  Save this file to the Microsoft "Add-ins" folder, generally located in a folder which looks like:


%AppData%\Roaming\Microsoft\Addins\ 


3.  Double click on the file, this will set several of the default settings of the add-in which will allow it to function from your computer.  This is important, if you fail to do this, the application will return an error that indicates the add-in is an bad state.



4.  From the Access ribbon, select  "Database Tools" => "Add-in" => "Add-in Manager", select the LTM add-in, click "Install", and then click "Close".  

5.  You then run the application by selecting "Database Tools" => "Add-ins" => "LTM32" (or LTM64)


Note: Recent versions of Access have an "Add-Ins" tab on the ribbon, this is not where this application will appear.


The first time you run the application, you should view the Help menu and either browse the topics, or view the help report, 


When you run the application,  the LTM will identify all of the Access and SQL Server tables and pass-through queries defined in your application.  It groups these in individual data sources, assesses the validity of each source (identified by color coding), counts the number of tables associated with each data source, and presents that information in a subform as indicated below:



The FileName column displays either the name of the Access BE or the SQL Server database associated with the connection, while the Path column displays the path to the Access file or the Server Name and driver associated with a SQL Server connection.


When you select (check) one or more of the data sources, the form expands to display the tables or pass-through queries associated with the selected connection(s).  When the dropdown over the tables subform says "Linked", only the tables which are already linked will be displayed.  


However, if only one data source is selected, another option ("Available") can be selected to display all of the tables in the selected data source which are not already linked.  This makes it significantly easier to identify and link tables from an external data source than with the Microsoft Linked Table Manager.



Pressing the "Select All" button (bottom left corner of the form) will check the "Selected" checkbox of all of the tables displayed, and, depending on the value in the tables dropdown, will enable either the "Refresh Links" or "Link Table(s)" button. 


By default, the "Prompt for new location" checkbox is checked if the tables dropdown reads "Linked" and un-checked if the dropdown reads "Available".  However, this default behavior can be changed in the "Options" dialog box, visible by clicking the "Options" button.  


The default for the Data Source dropdown will be the same as the source listed in the data sources subform.  If more than one data source is selected, this dropdown will display "default". If "Prompt for new location" is checked when the Refresh Links button is pressed, the application will present a way for you to select a new backend (Access or SQL Server) based on the value in the "Data Source" dropdown.


I'm still encountering occasional bugs, so if you run into one, please send me an email or post back here with details so that I can fix them.


The latest version of the application is v1-14-07, dated 17 Oct, 2018

LTM_x32.accde

LTM_x64.accde

If you have an earlier version, you should first uninstall that version before installing this new version, which fixes several "issues" identified by users.  To uninstall the previous version:

1.  open Access (any database)

2.  From the ribbon: Database Tools => Add-ins => Add-in Manager

      a.  Select LTM

     b.  Click Uninstall

     c.  Close Access

3.  Delete the previous version from the folder:  %appdata%\microsoft\addins

4.  Follow the steps from earlier in the article to install this new version


If you want to register the application with me, drill down on Help => About and click on my email address.


Dale Fye - Private Message Me


5
Author:Dale Fye
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free