<

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

x

A more efficient Linked Table Manager

Published on
6,078 Points
578 Views
5 Endorsements
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.


The latest version of the application is v1-14-05, dated 23 Aug, 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
Comment
Author:Dale Fye
11 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 21

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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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

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 21

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 21

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 21

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

Featured Post

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Join & Write a Comment

In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month