sql - MS Access 2010 throwing "Query Too Complex" error with larger record set -

i've having repeated sporadic issues access 2010 throwing "query complex" errors on queries aren't hugely complex, involve decent number of records (say more 5000). involves working linked sharepoint list.

here's example (the table i'm inserting on sharepoint server, other tables local in access file on drive):

insert [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&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] (staging_sh_keydata sh      left join staging_sap_cn cn on sh.[nr_id] = cn.[nr_id])      left join staging_sh_cpostatus cpo on sh.[nr_unique_id] = cpo.[nr_unique_id] sh.[nr_unique_id] not in (select [title] [nr info]); 

this 1 causes issues when working on big record sets. tried use insert 13,000 rows local file sharepoint server. ended having use top 1000 temporarily on select , run query 13+ times. then, i'd periodically error, though closing file , re-opening fix few more runs. i'd "system resource" related issues.

insert [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] [nr info] nri 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] staging_orderitems soi soi.[order_item] not in (select [title] [order items]); 

any appreciated!

as per this article:

jet gives generic message time cannot pinpoint what's wrong. cause can wrong delimiters, misinterpreted data types, mismatched brackets, using reserved words table or field names or aliases, confusion name autocorrect, or host of other possibilities.

solutions

in cases, solution figure out part access not understand. check data types match in expressions, joins, , criteria. explicitly typecast needed. explicitly declare parameters, jet knows type.

verify names (tables, fields, aliases) not on reserved words list. add square brackets around suspect names, , explicitly specify source table, e.g. "select [table1].[name] ... " rather "select name ... "

make sure name autocorrect off. compact/repair database.

if necessary, break query down find problem. drop half of clause, omit group clause until query works. start adding thing until pinpoint culprit.

occasionally, query complex: many unions, more 32 tables, 100 ands in clause, or nesting queries more 50 levels deep. list of limits, open in main access window, , enter specifications. in access 2007, go topic ha10030739.