"System Resource Exceeded" for simple select query in Access 2013

Question

_{Wednesday, May 22, 2013 3:02 PM}

Using Access 2013 32-bit on a Windows Server 2008 R2 Enterprise. This computer has 8 GB of RAM.

I am getting:

"System Resource Exceeded"  errors in two different databases for simple queries like:

SELECT FROM .... GROUP BY ...

UPDATE... SET ... WHERE ...

I compacted the databases several times, no result. One database size is approx 1 GB, the other one is approx. 600 MB.

I didn't have any problems in Office 2010 so I had to revert to this version.

Please advise.

Regards,

M.R.

All replies (16)

_{Friday, May 24, 2013 1:29 AM ✅Answered}

Hi,

Then try to start Access 2013 in safe mode to check whether the issue is caused by some add-ins.

Jaynet Zhang
TechNet Community Support

_{Thursday, May 23, 2013 2:25 AM | 1 vote}

Hi,

Try to install the hotfix in the following link to check the issue:

http://support.microsoft.com/kb/2760362

Jaynet Zhang
TechNet Community Support

_{Thursday, May 23, 2013 5:41 PM}

I tried your suggestion but got the message " The update is already installed on this system"

_{Friday, December 12, 2014 3:33 PM}

I have the same problem. I run an application that works well under XP and Access 2003, also under W7 and 2003 on selected machines. On others it just stops. I migrated the application to Access 2013 64 bit (also W7 64 bit), but gets the same problem. However, now I get the "System Resources Exceeded" error which is an improvement over an application that just stops. I have downloaded the HOT fix, set the registry keys that were recommended, but still get the error. There are about 30 000 records in the table when the error occurs (but about the double in the 2003 version that works perfectly). If I reduce the number of records to about 20 000, everything is fine.

I have monitored memory usage which lies around 2.2 Gb (there is 8 installed). To me it seems that this must have something to do with systems settings, since it works on other machines.  Makes me miss XP and Access 2003!

_{Monday, January 12, 2015 10:29 PM}

Was any solution found for this issue? We are receiving this error as well on very simple queries (appending and deleting data around 40,000 records).  This issue never occurred in 2007 and began after we upgraded to 2013.  The hotfix actually worked for one process, but the error is still occurring in two others. 

_{Thursday, April 9, 2015 4:58 PM}

  I am getting this error under Access 2010 and Windows 2012R2.

  We are using the most recent runtimes and libraries which are newer than the hotfix.

  We tried adding the registry key from the hotfix and are still getting the error.

     Greg

_{Friday, April 24, 2015 3:45 PM | 5 votes}

I have seen this problem when running Access on a computer with multiple processors. Try changing the Processor Affinity for the MSACCESS process in Task Manager down to just one processor, and see if that improves performance.

_{Friday, April 24, 2015 3:59 PM}

  Mark, thanks for the suggestion.  Unfortunately this is not possible for us as it is running on RDP servers with 20 people on the each server. I think 'bad things' would happen if everyone was running it on the same processor.  Unless maybe we came up with some script to randomly pick a processor when the person starts the program.  I think you may be on to something though I have seen strange errors in other software resolve when I set the processor affinity.

 With over a hundred people using the application all day long we only get the error about two or three times a week so it is pretty hard to track down.

   Greg

_{Friday, April 24, 2015 9:38 PM}

Hi Greg. I too am running Access on an RDP server. Checking Task Manager, I can see many copies of MSACCESS running in the process list, from all users on the server. We typically have 40-60 users on that server. I am only changing the Processor Affinity for MY copy, and only when I run into this problem. Restarting Access daily, I always get back to multi-processor mode soon thereafter.

As this problem only seems to happen on very large Access table updates, and as there are only three of us performing those kind of updates, we have good control on who might want to change the affinity setting to solve this problem. However, I understand that in other environments this might not be a good solution. In my case, we have 16 processors on the server, so I always take #1, my co-worker here in the US always takes #2, etc. This works for us, and I am only describing it here in case it works for someone else.

The big question in my mind is what multi-threading methods are employed by Microsoft for Access that would cause this problem for very large datasets. Processing time for an update query on, say, 2 million records is massively improved by going down to 1 processor. The problem is easily reproduced, and so far I have not seen it in Excel even when working with very large worksheets. Also have not seen it in MS SQL. It is just happening in Access.

_{Friday, April 24, 2015 9:49 PM}

  It does seem like maybe there is a multi-threading bug lurking in there somewhere.  Interestingly, this error on our systems seems to have nothing to do with large datasets.  Fairly small queries will cause it and there seems to be no pattern to where it will pop up.   The only pattern I was able to find is we have 6 RDP servers, a bank of three configured with one set of software and three with another set of software.  Both are identical OS and access runtime versions, but the error only ever happens on the first bank of servers.   Maybe some program installed on the one bank of servers is triggering the error somehow.

  Greg

_{Wednesday, July 27, 2016 1:05 PM}

We've got same problem on Access 2013 x64 SP1. Provided update (http://support.microsoft.com/kb/2760362) is not applicable to the system. Same requests works well on Access 2010. Is there any additional information about this problem?

_{Wednesday, July 27, 2016 3:04 PM}

  I was able to find one thing that helped on our systems.. we have a mix of linked SQL tables and local access tables.  The problem usually but not always happened on screens that used the local tables.  We are in the process of moving all local tables into SQL and have seen a definite improvement.  Maybe it caused some kind of resource or memory leak.  Not all tables are moved yet, so I don't know yet if it will completely solve the problem for us.

_{Wednesday, October 5, 2016 9:36 PM | 1 vote}

We're getting the same error.  I've tried the safe mode and we can still get the error running our code in safe mode.  The hotfix will not install on my machine.  Win 7 64-bit, running Office 2013 32-bit.  I didn't have the issue with Office 2010.  I am not able to go back to 2010 or I would do that.  O

One thing that this error does to us is it kills (yes, deletes, local tables and kills ODBC links).  I am having to import table structures again to run my code.  There is no consistency about which tables it kills or which time it will do it.  We run this process daily.

Setting the affinity in the task manager seems to get rid of the error but I'm having problems setting that in a batch file so that my users don't have to try to set it.  I have the batch file running successfully but when I check the task manager it shows all processors in use. (I have 4.)

My batch file:"C:\Windows\System32\cmd.exe" /C start /affinity 1 "C:\Program Files (x86)\Microsoft Office\Office15\MSACCESS.EXE" "c:\Users\UserName\desktop\SharePointDataRefreshExporttool.accdb"

Any news on how to deal with this issue?  I have some linked tables in SQL Server, but pushing data to our SQL Server has been really slow, so I do the processing in local tables.

_{Saturday, October 29, 2016 11:16 PM}

Have the same problem in Access 2010, 2013, 2016. When try to apply patches from this and the same themes with hotfix - it always say me that Application not found for this hotfix. I downloaded 32bit version, the same as my version office. This happened on Win 2008R2, Win 2012, Win 7

I get error when try to execute large update or delete requests. For ex. delete 10 000 rows from table with 400 000 rows. One thing that I found - my table have field with index and >50% values in this field = null. When I replace this values from null to 0, than query run Ok. This field used in where close of query (delete from table1 where myfieldwithindex is null and otherfield =2)

_{Friday, January 26, 2018 3:38 AM}

You cannot use the whole path to access. Just "C:\Windows\System32\cmd.exe /C start /affinity 1 MSACCESS.EXE" worked for me. No idea why including C\programs files... makes it not work.

_{Wednesday, February 27, 2019 6:45 AM}

Mark-NC

I like your solution and we took it a step further.  We have 4 of us who use Access on a server with 16 virtual processors on 4 real cpus.  What we do is set the affinity to use 1 processor from each cpu.  We found by doing this if we all were to run 4 cpu intensive at the same time we would each use one cpu at about 100% and really maximize the use of the server.  When we set it to 0,1,2, & 3 we would all be sharing 1 cpu.

We also tried using 2 processors each and never had a crash so that is what we do now.  We haven't taken the time to see how many processor it takes before the problem occurs.