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.
