- 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
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
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (12)
Commented:
Commented:
Commented:
And yes ... many Access databases start from Excel spreadsheets - once Excel self-destructs ... and ultimately become real databases :-)
Commented:
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? :)
Thanks for the heads up. I'll most certainly do that.
Regards, Andrew
Author
Commented:enjoy.
View More