Simple query of Sharepoint List locks up computer

[Dale Fye]

I'm using some Sharepoint Lists as tables for an Access application (mdb file
in 2007 for compatibility across all users). The query looks like:

SELECT tbl_LD.LD_ID, tbl_LD.LD_NUM,
tbl_LD_1.Created AS DateOccurred,
"LD (" & [tbl_LD_1].[ld_num] & ") added to AWFC" AS WhatHappened
FROM tbl_LD AS tbl_LD_1
INNER JOIN (local_Parameters INNER JOIN tbl_LD
ON local_Parameters.DefaultYear = tbl_LD.LD_Year)
ON tbl_LD_1.Parent_ID = tbl_LD.LD_ID
WHERE tbl_LD.LD_NUM Is Not Null

This is a relatively simple query, where the Local_Parameters.DefaultYear is
used as a Filter to tbl_LD. Then, tbl_LD is added a second time as the child
in a heirarchical data set. Both the LD_ID and Parent_ID columns are Numeric
in SharePoint.

Unfortunately, it appears that SharePoint (or Access) is having difficulties
with the query as it locks up my computer. With the Task Manager running, I
see huge fluctuations in CPU Usage, but the physical memory usage starts out
at about 1G and climbs steadily to about 1.95G (out of 2G). The only way to
stop the query is to terminate Access using the Task Manager.

Has anyone else run into this problem with SharePoint lists.

 

[Jay Ward]

Install Fiddler (http://www.fiddlertool.com/fiddler/version.asp) and use it
to analyse the queries being generated by Access and the responses coming
back from SharePoint. You may discover that the query is just taking a long
time to complete. If the number of records returned by the query is more than
several thousand, that could easily be the problem. If that's the only
problem, try the "cache list data" option on the External Data tab.

Also, try rebuilding the query with literal date filter instead of a
parameter, then try to get the parameter to work.

-Jay