Query based on SharePoint lists returning strange results

[Dale_Fye via AccessMonster.com]

In an application I've developed I'm using SharePoint lists for some of my
data. I've had to do this because the leadership wanted the application
accessible to users across our enterprise, but didn't want to deal with SQL
Server (don't ask)!

At any rate, I've got this query (I'll call it MainQuery), with links my
primary table to a bunch of the other lookup type tables joined by Left Joins,
because of the fields in the main table are NULL. The query is relatively
small, with only about a 8 tables, and generates 276 records.

Now, I've got another table (tbl_POCs), which contains a autonumber ID field
(as all SharePoint lists do), which should join to two separate fields
(POC_ID and LO_POC_ID) in the main table. When I add this table to the query,
and join tbl_POCs.ID to either of these fields, the query works fine (still
returns 276 records), but when I add a second instance of the table, and join
it to the other field, Access goes berserk and siezes all of my computers
resourses (CPU and Physical Memory both go to 100%).

So, I saved the main query, and tried to create a query based on it, with two
copies of tbl_POC (see query below), and it still does the same thing. I
wrote a little test program to open the query as a recordset, and step
through the records, and decided to shut it down after 50,000 records.

Any body have any idea what is going on? Is there a know bug associated with
using multiple copies of the same linked SharePoint list in a query.

SELECT MainQuery.Sort_Order,
MainQuery.IsAWFC,
MainQuery.LD_NUM,
MainQuery.LO_Priority,
MainQuery.LD_Name,
MainQuery.LD_Desc,
MainQuery.Status,
MainQuery.Sponsor,
[POC1].[POC_Last_Name] & (", "+[POC1].[poc_first_name]) AS
[Sponsor POC],
[POC2].[POC_Last_Name] & (", "+[POC2].[POC_First_Name]) AS [LO
POC],
MainQuery.LD_TimeFrame,
MainQuery.LTIOV,
MainQuery.POC_ID,
MainQuery.LO_POC_ID
FROM (qry_rpt_Prioritized_List_Excel_Export AS MainQuery
LEFT JOIN tbl_POCs AS POC1 ON MainQuery.LO_POC_ID = POC1.ID)
LEFT JOIN tbl_POCs AS POC2 ON MainQuery.POC_ID = POC2.ID;

 

[Albert D. Kallal]

Are you using 2007?

If yes, then perahps you make a copy of this accdbfile and then try this in
the off-line mode?

 

[Dale Fye]

Albert,

Yes, I'm running 2007 on a Vista machine. And No, the database is currently
in mdb format because some of my users are still using 2003. I thought of
this option late this afternoon (around 6PM) but was too exhausted to
continue, so I figure I will try converting to accdb and trying both the
off-line and just importing the tables into Access first thing in the morning.

It is interesting because I had another problem this afternoon with a report
I was working on (report with sub-report). It worked until I added one more
memo field to the query for each of these. At that point, the Report bogged
down, although both queries (report and sub-report) ran smoothly and quickly.
When I looked at my System Resources in Task Manager, my physical memory
usage climbed from about 900M to 1.8G (at which point it gave me a "system
resources exceeded" message and locked up my PC). I was able to log out of
Access, but my physical memory usage stayed at about 1.7G, and I was unable
to open any of the normal Office applications after this happened.

Dale