$Account.OrganizationName
The Access Wizard Newsletter Tips, Tricks, and Traps for Access Users and Developers
April 2012

The Need for Speed - Part Three: Find and Eliminate Broken Linked Tables

Geocaching is a great hobby. Usually you go for a walk in the woods, sometimes to places that are amazing and that you probably would not have discovered on your own. If you live in or around Central Massachusetts, check out Stoney Hill Loop; it will knock your socks off!

Typically, you get to the general location of the geocache and start poking around, maybe looking under logs or rocks. Then you finally turn over the right stone and, lo and behold, there the treasure is!

In Access databases, there are rocks that you can look under, places to look that you would never suspect would cause things to slow down. If you find this kind of problem, you have an opportunity to speed things up with just a bit of work.

In this Issue
  • Tip of the Month - Understanding the System Table
  • Search for Tables with Broken Links
  • Conclusion

  • Search for Tables with Broken Links

    If you find that there is an unexpected delay every time you try to open an existing query, even for relatively simple queries, then one of your tables may have a broken link.

    Most well-constructed databases have linked tables. You typically create the link to another database to either separate your code file from the data file, or to bring in data from a completely different source.

    The linking process is straight-forward. (You can get details here: Splitting a Database.)

    The first thing to do is create a new query, dismiss the opening "Show table" dialog box, and go to the SQL view. In the screen's upper left hand corner, you will see an icon labeled "SQL."

    Click on the SQL icon and you will see a place to type in a SQL statement, which is the guts behind any query. (See What's Really Happening with Queries for details.) The word "Select" will already be highlighted; delete that as well as any trailing punctuation, then cut and paste the following SQL Statement:

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

    As a side note, if you're interested in what's going on in the query, see the Tip of the Month.

    Once you have pasted the above SQL statement (or typed it if you have a bit of a masochistic streak), go ahead and run the Query.

    You will get a list of all your linked tables, and the name and path of the database in which they reside. If you have any non-Jet data sources that you're attached to, you will get information about the DSN (a techie term denoting the specifics about the connection) as well.

    Scan this table to see if there are any databases that have moved since you first linked that table. If so, you will want to repoint those tables or eliminate the links entirely.

    The next thing to do is to close the query and then open each table in the list from the tables tab. They will have a link symbol to the left of the name. If you find one for which the link is broken, you will get a message on the order of "The Microsoft jet database engine cannot find the input table or query 'Table Name'."

    If you get this message, then you will want to repair the link or eliminate the table entirely.

    This type of problem can affect any queries in your database, not just he one which is broken. It's one of those hidden problems that is difficult to see, but once found easy to repair.

    And once you've fixed this problem you might happily find that others processes involving queries run significantly faster.


    Conclusion

    Sometimes things seem slow for no reason at all. In fact, there is almost always a reason. As we've seen in this month's Wizard, the problem could be well-hidden and in a spot that you would never suspect.

    By eliminating drags on performance, you will spend less time waiting and more time being productive.


    Tip of the Month - Understanding the System Table

    In this month's Wizard, I showed you a query that went after a table called msysobjects. This particular table is one of the behind the scenes objects that Access uses to store information about your application.

    By default, this and other system objects are hidden. (You can see them by changing one of the options in the application. I'm being intentionally vague here because these tables are hidden for a good reason - they are not intended for users to fiddle with them.)

    The particular trait that we discussed this month was linked tables, but there are many other traits you can investigate by tweaking the criteria for the Type field.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services



    Join our mailing list!

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