<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

A more efficient Linked Table Manager

Published on
6,298 Points
798 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
12 Comments
LVL 67

Expert Comment

by:Jim Horn
Well presented, and heck yes.  Upvoted.
0
LVL 75
I've been beta testing Dale's LTM since the inception. And as I did the original Access LTM, I use it almost daily at work.  You can think of the LTM as the original Access LTM on steroids and then some.

From the screenshots above, you can easily see that the Datasheet format is a lot easier to deal with than the fixed column format of the original LTM. And being able to separate out multiple linked data sources is a real bonus,  something completely lacking in the original LTM.

I highly recommend that you download this LTM and check out the many amazing features, which make it superior to and much more functional than the original Access LTM.
0
LVL 24

Expert Comment

by:Andrew Leniart
Dale,

I have an Office 365 subscription which gives me access to Microsoft Access (excuse the pun). Do you have any resources that give examples for noobs to Access and how it can be helpful to them? I find your writing style to be easy to follow, but have never really found a use for Access - perhaps there are uses for it that I might find useful and helpful, yet don't know about?
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

LVL 75
@Andrew ... millions of people and 100,000s of Access databases cannot be wrong :-)
0
LVL 24

Expert Comment

by:Andrew Leniart
@Joe - I don't disagree, but I'm not sure how something like Access could be helpful to me when I do a lot of my stuff in Excel for example :)
0
LVL 24

Expert Comment

by:Andrew Leniart
I also find Access to be difficult to use, given that I don't know a lot about databases. I'm sure it could be helpful to me, just would like to see a noob type article with examples of how things that are often done in Excel (for example) could better be done in Access.
0
LVL 75
Well ... contrary to popular belief ... Excel is not .... a database.
Meanwhile ... Google is your friend :-)
0
LVL 24

Expert Comment

by:Andrew Leniart
Meanwhile ... Google is your friend :-)
Thanks Joe, I know google is my friend and I use it daily, however, for the purposes of learning, I find articles such as this one far more useful than millions of results to sift through which often have nothing to do with what I'm searching for.
0

Expert Comment

by:CharlotteF
Andrew, my biggest pet peeve about Access training and "how-to" articles is that they address the tools in Access but rarely the underlying design principles that are essential in building a database.   Most of the user-built (and a lot of the developer-built) databases are really just spreadsheets dumped into Access.  These are not databases in the relational sense at all.  Look for a book by Michael J Hernandez, "Database Design For Mere Mortals".  This gives you the necessary background to designing any relational database.
1
LVL 75
The Hernandez book is certainly a classic.
And yes ... many Access databases start from Excel spreadsheets - once Excel self-destructs ... and ultimately become real databases :-)
0
LVL 24

Expert Comment

by:Andrew Leniart
Hi Charlotte.

Andrew, my biggest pet peeve about Access training and "how-to" articles is that they address the tools in Access but rarely the underlying design principles that are essential in building a database.

Indeed. You make an excellent point, one that I hope Dale (author of this article) and other readers will take note of for future article submissions. Perhaps an article explaining just that? :) Yes, there are already many explanations to be found with Google.

The difficulty I see here is that the term "Database" is so broad that to a layman, can also sound such technical a term as to discourage the excitement of learning about it. A title like "Learn the underlying design principles involved in building a database" wouldn't likely attract my attention, nor dare I say it, most others who don't have a vested interest in learning Access or other related software.

Yet if I saw something I could relate to, like "Learn to organize your favorites so that all browsers can use them" and do that in Access, well, that might be something I'd click on and actually read. The trick is in finding a way to educate using a hook and subject matter that will entice people to want to read and want to learn more. That's why I think different ways of educating on the same topic is so important.

Take the question: What is a database?

If I "Google" that as kindly suggested to me earlier by Joe, then the first thing I get is that its "a structured set of data held in a computer, especially one that is accessible in various ways."

Sounds too much like programming to me and doesn't really entice me to continue digging in order to learn more. I think that's a core problem of why many don't turn to something like Access and use Excel instead - it's too much like programming.

It's only when the penny drops (and for me, it's by reading articles like this one out of need while editing) that you start to get more interested in digging further. Editing articles such as this one prompt me to ask the question, how can I relate what I do in Excel, to do it even better in something like Access?

I confess I have a vested interest in promoting for these types of articles, both as someone who wants to read them, and someone who gets to edit article submission here in a volunteer Page Editing capacity. We already have a ton of technical "how to" articles as you say, but I think that's a good thing and keep them coming I say!

Yet I think interestingly written articles that will attract the "Joe Averages" of the world, who use Excel to store information in mostly because it seems easier to come to terms with using, yet will never know until enticed to dig deeper just how much easier life could be by delving into the cryptic world of databases.

That's the type of "How to" article I'm trying to promote be written here. Is there a single way of doing that? I don't think so. I'm just trying to encourage readers who have the necessary knowledge to give it a go. Hope that makes sense? :)

Look for a book by Michael J Hernandez, "Database Design For Mere Mortals"

Thanks for the heads up. I'll most certainly do that.

Regards, Andrew
0
LVL 52

Author Comment

by:Dale Fye
for those of you using the Linked Table Manager, the latest version (posted in the original article) is 1.14.07, which fixes several bugs, including one which caused the linking operation to fail if the path to the backend is > about 100 characters (has to do with the Title property of the FileDialog).

enjoy.
0

Featured Post

Powerful Yet Easy-to-Use Network Monitoring

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

Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Make it easier to see the current record on your Microsoft Access forms! To highlight the current record with a yellow background color, use Conditional Formatting, a control to keep track of the primary key value, a control to change color, and a l…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month