<

A more efficient Linked Table Manager

Published on
3,237 Points
137 Views
1 Endorsement
Last Modified:
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.


LTM_x32_v1-14-04.accde

LTM_x64_v1-14-04.accde


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


1
Comment
Author:Dale Fye
1 Comment
LVL 67

Expert Comment

by:Jim Horn
Well presented, and heck yes.  Upvoted.
0

Featured Post

Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Join & Write a Comment

Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month