Query: System Resource exceeded

[Pau905]

Hello,

When I run this I get "System Resource exceeded". My table is 120000 rows, 100 columns. I use W10, Access 2016. Can I do it in some other way?

UPDATE Table1 SET Table1.Col1 = Table1.Col2

(I will do some other calculation to set to Col1)

/P

 

[Joe4]

Have you compacted your database first, to help ensure that you are not exceeding the 2 GB size limit?

Also, your query seems a bit odd. Why would you simply be copying over Col1 to Col2 within the same table?
What is the point of that?

What I am getting at is you may not need to do this at all, and may just be able to use a Select Query to get what you need.

 

[Pau905]

Thanks for the answer. I tried to compact the database, still the same problem.

I will calculate the values in "Col1" with several columns as input values. When this did not work I narrowed it down to this simple task.

Can I loop every row? Or do something else to make this happaend?

 

[Joe4]

Do you really need to actually update the underlying Table?
Can't you do the calculations in a Calculated Field in a Query?
Typically, you do NOT want to hard-code/save calculated fields back to the underlying table.
It can violate the Rules of Normalization and undermine the dynamic nature of the database.

 

[Micron]

Agree that most often it is ill advised to store calculations. You didn't say how large the db was after compacting, which would help.
The error message has several fixes, depending on who you're asking. Make sure you don't have a lot of junk running in the background (e.g. Skype). Check your virtual memory allocation in Task Manager before running this. Perhaps you can compare the value when the error is raised again and see if you're maxed out. Others say that using 64 bit Access solves the issue, although I don't see why it would. Switching may make some 32 bit db's that you've created to become unusable, unless 64 is what you're using anyway.

 

[Pau905]

Thanks for the feedback! I will think about how to do it instead.

/P

 

[Joe4]

Thanks for the feedback! I will think about how to do it instead.

Note that people often mistakenly think that they need to store the calculated values in a Table, instead of just doing them in the Query.
Most of the time, you don't. You can use a Query for pretty much anything you use a Table for (Forms, Reports, Exports, etc).
So most of the time, just doing it in a Query will suffice.

If you are not sure if that is the case, please explain exactly what you are ultimately doing with these calculations, and we may be better able to advise you.

 

[Micron]

IMHO in 99.9% of cases it's not a matter of whether or not you can/cannot or need or don't need - it's that you should not.
The main reason would be that if just one input value gets changed the stored value is wrong. If it's not because you've done something behind the form to ensure accuracy then there's no point in storing a calculation that you form is handling anyway. Calculations should be done via forms and reports, or at least the queries behind them. The one exception might be that if your calculation regularly involves discounting (or a similar issue) where for the odd record there is no discount, then you might have to store the discount amount result. I'd rather structure the calculation to divide/multiply by 1 in order to handle cases where there's no discount.

 

[xenou]

In a generalish sort of way a 100 column table is somewhat large ... you may be overdue for some re-thinking the database structure ... I don't recall getting such errors even with tables with 500,000 rows or more in them, so I'm just thinking that there's more to the story somehow --- sorry that's a very vague bit of advice!