The Access Wizard Newsletter
Tips, Tricks, and Traps for Access Users and Developers.

Finding Information on All Linked Tables
– The Easy Way

Introduction




Take a look at the picture above. It's by the American artist Bev Doolittle.  In addition to being an interesting scene, there is also a series of hidden faces in the forest. How many do you see? When I look at this, I can easily find nine, many people find eleven, and some folks see thirteen.

One of the beauties of a database is that you can easily find, count, and inspect information about a variety of things. One set of information that is available, but not necessarily easy to get to, are the details behind the linked tables in your database. This month, I will show you a simple technique to get not only a list of all linked tables but also information about them, all in a single step.
 

Linked Tables: Background

Linked, or attached tables, live outside your database. Most typically, they are in another Access database, but their home can also range far beyond that. To get the full story of attaching tables take a look at the February 2013 issue of the Access Wizard.

If you need to learn the source of a single linked table, you can hover over the table in the Nav Pane and the path will show up. With older versions, you can right-click on the table and go to design view. For more details, once again see the February 2013 issue.

A Little Work, A Lot of Information

But let's say that you have a complicated database with many linked tables coming from a variety of sources. There is an easy way to get information about all linked tables in one fell swoop.

The following SQL statement is all you need:

SELECT DISTINCTROW msysobjects.Name, msysobjects.Database, msysobjects.Connect
FROM msysobjects WHERE (((msysobjects.Type)=6 Or (msysobjects.Type) Like "dsn*")) ORDER BY msysobjects.Database;


To use this, open a new query in design view, swap to SQL view (click view – choose SQL), and paste the SQL statement above into the window. Then run the query. You will get a listing of all the attached tables as well as the database they are coming from. The example below shows a set of tables with connections to multiple access databases.

  

What is Going on Behind the Scenes

This query that provides this information goes after a system table called msysobjects. This table contains information about objects within the database. The msysobjects table is hidden by default because typically there is no reason for a user to open it. It has all sorts of interesting information, but a format that makes it difficult to understand. The system tables can put quite a bit of power at your fingertips. In our case, all we have to know is that, if we need to get information about table links, we can use the SQL statement above to get what we need.

Conclusion

Getting information about objects in your database doesn't have to be as challenging as finding the faces in Bev Doolittle’s painting. You can use tools within Access to get information about objects such as linked tables quickly and easily.
 

 

Tip of the Month: 
Use the Nav Pane to Drag Tables into Queries

If you are a typical Access user, you probably use the Show Tables button to find tables when you are designing a new query. In that view, you are presented with a list of all the tables (or queries) in your database. If you have only a few tables, it is not too tough. But if you have a complicated database with loads of tables, this approach can be tedious.


If you are using Access 2007 or 2010, there is a much easier way. In the Nav Pane, find your table either visually or by using the search tool. (See the July 2011 Wizard for details on how to activate the search tool.) Once you find your table, you can drag it into the top part of the query by design window and then your table is ready for use.

 
Quick Links:

Custom Software Home

Access Wizard Archives

Our Services


© Copyright Custom Software
All Rights Reserved | Westford, MA 01886