Error 3035 - System Resources Exceeded (1 Viewer)

[darbid]

I am currently testng the following - This select works fine. (This is from the query objects of Access)

SELECT patent_akz AS q_akz, em.status AS q_status, em.decision_number AS q_decision_number, CVDATE(em.decision_date) AS q_decision_date, em.work_around_ability AS q_work_around_ability, em.attractive AS q_attractive, em.used_by_third_party AS q_used_by_third_party, em.total_eval AS q_total_eval, em.can_use AS q_can_use, em.eval_reason AS q_eval_reason, em.use_inhouse AS q_use_inhouse, em.product AS q_product, em.decider AS q_decider, em.bkz_id AS q_bkz_id, em.bkz_questionaire AS q_bkz_questionnaire, em.hierarchie_level_2 AS q_hierarchie_level_2, em.hierarchie_level_3 AS q_hierarchie_level_3, em.hierarchie_level_4 AS q_hierarchie_level_4, em.hierarchie_level_5 AS q_hierarchie_level_5, em.piv AS q_piv, em.piv_name AS q_piv_name, pik_coordinator AS q_pik_coordinator, pik_name AS q_pik_name, fal_pik AS q_fal_pik, fal_name AS q_fal_name, l_akz AS q_l_akz, derwent_title AS q_derwent_title, derwent_abstract AS q_derwent_abstract, CVDATE(date_of_lapse) AS q_date_of_lapse, countries AS q_countries, only_siemens_in_charge AS q_only_siemens_in_charge, em.type AS q_type, em.em_akten_id AS q_em_akten_id, connect.srf_akten_id AS q_srf_akten_id
FROM [SELECT IIf(IsNull(connect.srf_akz), em.akz, connect.srf_akz) as patent_akz, em.status, em.decision_number, em.decision_date, em.work_around_ability, em.attractive, em.used_by_third_party, em.total_eval, em.can_use, em.eval_reason, em.use_inhouse, em.product, em.decider, em.bkz_id, em.bkz_questionaire, em.hierarchie_level_2, em.hierarchie_level_3, em.hierarchie_level_4, em.hierarchie_level_5, em.piv, em.piv_name, '' as pik_coordinator, '' as pik_name, '' as fal_pik, '' as fal_name, '' as l_akz, '' as derwent_title, '' as derwent_abstract, null as date_of_lapse, '' as countries, '0' as only_siemens_in_charge, em.type, em.em_akten_id, connect.srf_akten_id
FROM (em
LEFT JOIN connect
ON em.em_akten_id = connect.em_akten_id)
UNION select srf.akz as patent_akz, srf.status, srf.decision_number, srf.decision_date, srf.work_around_ability, srf.attractive, srf.used_by_third_party, srf.total_eval, srf.can_use, srf.eval_reason, srf.use_inhouse, srf.product, srf.decider, srf.bkz_id, srf.bkz_questionaire, srf.hierarchie_level_2, srf.hierarchie_level_3, srf.hierarchie_level_4, srf.hierarchie_level_5, srf.piv, srf.piv_name, srf.pik_coordinator, srf.pik_name, srf.fal_pik, srf.fal_name, srf.l_akz, srf.derwent_title, srf.derwent_abstract, srf.date_of_lapse, srf.countries, srf.only_siemens_in_charge, srf.type, null, srf.srf_akten_id
FROM srf]. AS [%$##@_Alias];

Now I need to add this result to a linked table which is empty. The back end is SQL Server.

So to the above I add

INSERT INTO tbl_questionnaire

This give me the error message. I have tried it with DAO in code as well but I still get an error.

Any thoughts why?

I have tried this

Application.DBEngine.SetOption dbMaxLocksPerFile, 1000000

 

[gemma-the-husky]

how many rows do you get?

what are you trying to insert into the table (or is the insert bit only part of the SQL)?

 

[darbid]

how many rows do you get?

what are you trying to insert into the table (or is the insert bit only part of the SQL)?

From the SELECT I get about 78,000 records.

I am trying to INSERT the whole 78,000 into the table.

 

[darbid]

Hi Dave,

I gave up on trying to execute this in one sql. Instead I regressed to opening 2 record sets (source and target) and then added records this way by looping through.

This probably takes longer but I can control things better, can show progress and Access does not lock up so badly.