Very slow query with linked tables

[amilgate]

I have two databses, A and B, that contain the same linked tables.
Last week I was running a query on A on another machine, and had to
close it down as the query had stopped responding. After that any
query in A that contains a certain table runs extremely slow. The same
query may have taken 30 seconds before but is now taking ages, and
apparently slowing down the network. If I take out said table, it
seems to run fine.

For B, everything works as the same as before, including anything with
the offending table.

I've refreshed the links and re-linked the table which makes no
difference. If I put a local table with the same data it runs but
still extremely slow. I presume it's some sort of issue with the
linking of the table in database A but I'm stumped.

Any ideas? If I've left out any info let me know.

 

[John Spencer]

RANDOM THOUGHTS:

Did you check that the lockfile got removed after you closed down?

You might try dropping any indexes on the offending table and removing any
relationships (to get rid of those invisible indexes), then compacting the
database and re-establishing the indexes and relationships.

Also, check for missing indexes.

I'm not sure any of those will help - but they are some of the things I would try.

Are you running the two databases A and B on the same computer or on different
computers? Your problem could be network related.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Jerry Whittle]

Along with what John said, I'd do a manual Compact and Repair of the database
that contains the table in question.

I'd also consider breaking the links to that table and recreating them.