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.
