Query hangs on local tables; runs OK on linked tables

Query hangs on local tables; runs OK on linked tables

Post by Ned Balze » Thu, 24 May 2007 01:22:23


Hi all,

I have a really weird problem. I have a query in Access 2007 that
runs fine against linked tables but not against local ones. Here is
the query:
SELECT DISTINCT loc_tcm.code_univ_yr_sem AS SEMESTER,
loc_tcm.code_csec_loc, loc_tcm.code_csec_delivery,
loc_tcm.code_csec_crse_key, Left(loc_TCM.code_CSEC_crse_key,5) AS
COURSENAME, Mid(loc_TCM.code_CSEC_crse_key,6,4) AS COURSENUMB,
loc_tcm.numb_csec, loc_tcm.code_csec_cancel, loc_tcm.code_csec_acdt,
loc_tcm.code_csec_coll, loc_csec_instructor.numb_csec_insr,
loc_csec_instructor.pcnt_csec_insr_effort,
loc_instructor_bio.NAME_EMPL_LAST_LEGAL,
loc_instructor_bio.NAME_EMPL_FIRST_LEGAL
FROM (loc_tcm INNER JOIN loc_csec_instructor ON
(loc_tcm.code_univ_yr_sem=loc_csec_instructor.code_univ_yr_sem) AND
(loc_tcm.code_csec_loc=loc_csec_instructor.code_csec_loc) AND
(loc_tcm.code_csec_delivery=loc_csec_instructor.code_csec_delivery)
AND
(loc_tcm.code_csec_crse_key=loc_csec_instructor.code_csec_crse_key)
AND (loc_tcm.numb_csec=loc_csec_instructor.numb_csec)) INNER JOIN
loc_instructor_bio ON
(loc_csec_instructor.numb_csec_insr=loc_instructor_bio.NUMB_CSEC_INSR)
AND
(loc_csec_instructor.code_univ_yr_sem=loc_instructor_bio.CODE_UNIV_YR_SEM)
WHERE (((loc_tcm.code_univ_yr_sem)="200607FA") AND
((loc_tcm.code_csec_loc)="UP" Or (loc_tcm.code_csec_loc)="XS") AND
((loc_tcm.code_csec_delivery)="R") AND
((loc_tcm.code_csec_cancel)<>"C") AND
((loc_tcm.code_csec_coll)="LA"));

The query against the local tables just hangs -- I have let it run for
as long as 15 minutes before killing Access from task manager.

I would think that the query would run slower against linked tables.
The reason I am trying to get it to run against local tables is not
for performance, but because the contents of some of the linked tables
have changed, and I have the linked tables saved to local from a
couple of months ago.

I am wondering if maybe the local versions have been corrupted or
something. Here's another wrinkle: I just upgraded to Access2007
yesterday -- prior to that I was using Office2003, and this query
worked OK (local version). I imported these local tables from an .mdb
file created in Access2003.

So, is there anything I should check to try to diagnose this problem?

Thanks in advance.

-- Ned Balzer