Runtime Error '3005': System Resources Exceeded - Microsoft Community

i trying run pass-through query oracle database. query not complex, 141,662 characters long due huge dynamically built case statement (via vba). have tried registry , hotfix fix issue, did not work (https://support.microsoft.com/en-us/help/2760362/description-of-the-access-2013-hotfix-package-ace-x-none-msp-february)

here scenario:

local access table 3950 unique ids. need match each id local table oracle table (hence pass through), , retrieve data on these ids various related oracle tables. access cannot join local table oracle, , cannot import oracle data locally. have written 2 vba variations achieve this, 1 slow, other gets " system resources exceeded" error.

solution 1 (works, takes 10 minutes loop through list) :

i can loop through each id in local table, insert id oracle query, run query, return results, loop , repeat each id. ends running query 3950 times in row. works, takes 10 minutes.

solution 2 ( system resources exceeded via vba, works manually ):

loop through local table, , insert each id case entry in big single oracle query. so, single query has 3950 case entries, hence 141,662 characters. now, works, partially - vba creates query, errors " system resources exceeded" trying save query, before runs query. if end debugger, can manually double click query has created , runs perfectly, , runs in 10 seconds rather 10 minutes.

here vba fails, picking after loop build case statement:

              casesql = left(casesql, len(casesql) - 14) + " end) "

                 ' end massive case statement properly.

                 s = currentdb().querydefs("oracletemplate").sql

                 ' import template query

                s = replace(s, "??id??", casesql)

                ' replace ??id?? template query huge case statement

            

                currentdb().querydefs("oracle_final").sql = s                     ' this fails

              ' set oracle_finalquery newly build s query.

                currentdb().execute "insert id_results select * oracle_final"

                ' never gets here. if want run oracle_final query, have double-click it, in case runs fine , quickly.

any way around this? suggestions better way achieve trying do?

               

 

ended parsing groups of 999. did trick, kept query under 50,000 characters.

Office / Access / Microsoft Office Programming / Office 2013