Run time Error 3035, System resource exceeded

  • Thread starter Thread starter Ryan
  • Start date Start date
R

Ryan

I have this union query in access which produces a Run Time 3035 System
Resource Exceeded error. If I remove one of the queries from the Union it
works, but I have 10 more to add. Each of the queries have 24 columns. The
result of the Union Query is passed to a Crosstab query. I tried changing
the MaxLocksPerFile in the registry editor but that didnt help. Any
suggestions??
select * from B03ChargesPD
UNION ALL
select * from B04ChargesPD
UNION ALL
select * from C10ChargesPD
UNION ALL
select * from C11ChargesPD
UNION ALL
select * from C12ChargesPD
UNION ALL
select * from C16ChargesPD
UNION ALL
select * from C18ChargesPD
UNION ALL
select * from C24ChargesPD
UNION ALL
select * from C25ChargesPD
UNION ALL
select * from C27ChargesPD
UNION ALL
select * from C28ChargesPD
UNION ALL
select * from C29ChargesPD
UNION ALL
select * from C30ChargesPD
UNION ALL
select * from C31ChargesPD
UNION ALL
select * from C32ChargesPD
UNION ALL
select * from C32ChargesPD
UNION ALL
select * from C36ChargesPD
UNION ALL
select * from C37ChargesPD
UNION ALL
select * from C38ChargesPD
UNION ALL
select * from C39ChargesPD
UNION ALL
select * from C40ChargesPD
UNION ALL
select * from C41ChargesPD
UNION ALL
select * from C42ChargesPD
UNION ALL
select * from C43ChargesPD
UNION ALL
select * from C45ChargesPD
UNION ALL
select * from C46ChargesPD
UNION ALL
select * from C47ChargesPD
UNION ALL
select * from C48ChargesPD
UNION ALL
select * from C50ChargesPD
UNION ALL
select * from C51ChargesPD
UNION ALL
select * from C52ChargesPD
UNION ALL
select * from C56ChargesPD;
 
I am able to run the Union Query, the problem occures when the union query is
passed to the Crosstab query. Here is my crosstab query if it will help.

PARAMETERS [Forms]![Parameters].[LocationFilter] Text ( 255 ),
[Forms]![Parameters].[StartDateFilter2] Text ( 255 ),
[Forms]![Parameters].[EndDateFilter2] Text ( 255 );
TRANSFORM Sum(AllChargesPD.Charges) AS SumOfCharges
SELECT AllChargesPD.[Financial Class], Sum(AllChargesPD.Charges) AS
[Financial Class Total]
FROM AllChargesPD
WHERE (((InStr("," & Forms!Parameters.LocationFilter & ",","," & [Database]
& ","))>0))
GROUP BY AllChargesPD.[Financial Class]
PIVOT AllChargesPD.Database;
 
Microsoft Access database specifications
Number of tables in a query 32
Could this be the reason? I would think this would be the reason, but the
Union query will run, I only get the error when the crosstab query runs.
 
Same message (Run-time error '3035': System Resource exceeded.) appear to me when I run Query in Access 2007:

sSQL ="UPDATE TempTbl SET [fldID] = bmConvCode([fldID],2,4), [fldDesc] = bmConvCode([fldDesc],2,4);"

where bmConvCode() is my function...

I generateed SQL by code, a and execute by "CurrentDb.Execute sSQL"

an it works fine for 24 similar update queries, and than stoped at 25th.

Database have over 30 tabels and I generate SQL Update for every tabe and text field in table.
Database size is about 350MB.

How to finish the job?
 
Last edited:
I found a solution:

Application.DBEngine.SetOption dbMaxLocksPerFile, 1000000

after that everythng is OK...

Strange...

because I Create Linked Tabel, execute SQL, and delete TableDef... so I always had one table in game... and 25th table is not event to long... it is one of shorter...

Strange...

Microsoft product...

Strange...
 
Back
Top