Query: System Resource exceeded

Pau905

New Member
Joined
Nov 10, 2020
Messages
16
Office Version
  1. 365
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 10, 2020
Messages
16
Office Version
  1. 365
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 3, 2015
Messages
2,085
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Nov 10, 2020
Messages
16
Office Version
  1. 365
Thanks for the feedback! I will think about how to do it instead.

/P
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jun 3, 2015
Messages
2,085
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,827
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,549
Messages
5,636,937
Members
416,953
Latest member
prakashkumar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top