title: MS Access 2010 throwing "Query Too Complex" error with larger record set
body: <p>I've having repeated but sporadic issues with Access 2010 throwing up "Query Too Complex" errors on queries that actually aren't hugely complex, but involve a decent number of records (say more than 5000). This always involves working with a linked SharePoint list. </p>
  
  <p>Here's an example (the table I'm inserting into is on the SharePoint server, the other tables are local in the Access file on my drive):</p>
  
  <pre><code>INSERT INTO [NR Info] 
  
  
  (Title, 
      [NR ID], 
      [Project], 
      [Sub-project], 
      [PD Number], 
      [WBS Number], 
      [Network], 
      [IA PO Activity], 
      [FA PO Activity], 
      [ASP], 
      [status], 
      [MS10],
      [PO Status], 
      [2G/3G HW CPO], 
      [4G HW CPO], 
      [4G SW CPO], 
      [Additional HW CPO], 
      [Antenna HW CPO], 
      [2G/3G/4G HWAC CPO], 
      [Swap Prep CPO], 
      [VOD I&amp;C CPO], 
      [Antenna Refresh CPO], 
      [Additional Services CPO], 
      [Decom CPO], 
      [Variances Build Services CPO], 
      [Unknown Variances CPO], 
      [Other CPO A], 
      [Other CPO B], 
      [Other CPO C], 
      [Other CPO D], 
      [Other CPO E] )
  SELECT sh.[NR_Unique_ID], 
      sh.[NR_ID], 
      sh.[Project_lookup], 
      sh.[SubProject_lookup], 
      cn.[project_definition], 
      cn.[wbs_element], 
      cn.[network], 
      cpo.[IAPO_activity], 
      cpo.[FAPO_activity], 
      sh.[ASP_lookup], 
      sh.[Status], 
      cpo.[MS10],
      cpo.[cpo_state], 
      cpo.[2G3G_HW_State], 
      cpo.[4G_HW_State], 
      cpo.[4G_SW_State], 
      cpo.[Addl_HW_State], 
      cpo.[Antenna_HW_State], 
      cpo.[2G3G4G_HWACS_State], 
      cpo.[Swap_Prep_State], 
      cpo.[VOD_IC_State], 
      cpo.[Antenna_Refresh_State], 
      cpo.[Addl_Serv_State], 
      cpo.[Decom_State], 
      cpo.[Var_Build_State], 
      cpo.[Unknown_Var_State], 
      cpo.[Other_POA_State], 
      cpo.[Other_POB_State], 
      cpo.[Other_POC_State], 
      cpo.[Other_POD_State], 
      cpo.[Other_POE_State]
  FROM (Staging_SH_keyData AS sh 
      LEFT JOIN Staging_SAP_CN AS cn ON sh.[NR_ID] = cn.[nr_id]) 
      LEFT JOIN staging_SH_cpoStatus AS cpo ON sh.[NR_Unique_ID] = cpo.[NR_Unique_ID]
  WHERE sh.[NR_Unique_ID] NOT IN (SELECT [title] FROM [NR Info]);
  </code></pre>
  
  <p>This would be another one which also causes issues when working on big record sets. I tried to use it to insert about 13,000 rows from my local file to the Sharepoint server. I ended up having to use a TOP 1000 temporarily on the SELECT and run the query 13+ times. Even then, I'd periodically get the error, though closing the file and re-opening it would fix it for a few more runs. I'd also sometimes get "System Resource" related issues.</p>
  
  <pre><code>INSERT INTO [Order Items]
      ([Title], 
          [Order Item],
          [NR ID],
          [Vfe Material ID], 
          [E/// Material Num], 
          [Material Description], 
          [Item Category], 
          [Quantity Ordered], 
          [Date Requested], 
          [Requester], 
          [Item Request Status], 
          [PO2 Number], 
          [PO2 Line Number], 
          [PO Technical Check], 
          [PO Accepted Date], 
          [SO Number], 
          [SO Line Number], 
          [SO Line Status], 
          [SO Item Quantity], 
          [SO Release Date], 
          [SO Activated Date], 
          [Event Day], 
          [MS10])
  SELECT [Order_Item], 
      [Order_Item],
      (SELECT TOP 1 [id] FROM [NR Info] nri WHERE nri.[NR ID]=soi.[NR_ID]),
      [VF_SAP_Material], 
      [Mat_Num], 
      [Mat_Desc], 
      [Mat_Cat], 
      [Order_Quan], 
      [Request_Date], 
      [Requester], 
      [Item_ReqStatus], 
      [PO2_Num], 
      [PO2_LineNum], 
      [PO_TechCheck], 
      [PO_AcceptedDate], 
      [SO_Num], 
      [SO_LineNum], 
      [SO_LineStatus], 
      [SO_Quantity], 
      [SO_RelDate], 
      [SO_ActivatedDate], 
      [EventDay], 
      [MS10]
  FROM Staging_OrderItems AS soi
  WHERE soi.[Order_Item] NOT IN (SELECT [Title] FROM [Order Items]);
  </code></pre>
  
  <p>Any help greatly appreciated!</p>