system resources exceeded error

[gnarlesv]

i have been using access for many, many years and am using a current office 365 version (2009) with windows fully up to date on an lenovo thinkpad with 32gb ram etc. while i had sporadically gotten the system resource exceeded error since moving to office 365, it is now happening with increased frequency. for example I have a table called, say, "any_table", it has 984,604 records with 60 (or so fields). if i run a query to return all the records with no joins, it works fine. If i add a criteria for either a text field or a numeric field it fails. meanwhile i can run very complex multi-table/view (saved queries) queries just fine. the common denominator may be the absence of values in the where clause. not sure. this started happening just two days ago. not sure if there were auto updates do my system/access but i did subsequently bring everything up to date to try to fix things, unsuccessfully.

[#2]

@gnarlesv This is the same issue I have. MS will not help me. My system was upgraded yesterday and since then my Access code is failing due to this error. I thought my table was corrupted. After rebuilding it and removing all bar 100,000 records, it still does it. There is something dreadfully wrong with probably Win 10 as this problem only appeared rarely in Access since the W10 update. My access table which is called when the system resources exceeded error occurs had 386,000 records before W10 update, but after W10 update, it now stops. As I said, I culled it back to 100,000 records and for some code, it runs, but for others it now stops with that error. The program DOES NOT WORK, and MS does not want to help. I support your complaint.

[#3]

@NevilleA 

I'll bring this problem to the attention of the MS Access team to see if they are aware of it and if there is a potential fix to address it.

[#4]

@George Hepworth 

This is a known problem but up until recently, it's been unresolved. However, "... there is a fix for this in version 2010, which is currently available for Office Insders, and will be in Current Channel at the end of this month.

So if you know of instances where this is happening, please try version 2010 if you can to see if it resolves the issue."

George

[#5]

@George Hepworth 

Try this :

Start.exe /affinity 1 [path-to]MSACCESS.EXE [path-to-your-db]

[#6]

@George Hepworth 

Thanks.

First, in your first reply, you said version 2010. Do you mean I should install Access 2010? I am using Access 2016.

Second reply, in Run command, I entered

Start.exe /affinity 1 C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE d:\Documents\Racing

Is says Cannot find Start.exe

(I am not a programmer. )

The more I try work arounds in my DB, the worse it appears to be.

The table where the system resources exceeded error appeared had 400,000 records.

I managed to move out 300,000 of these records into another archive file.

Yesterday, my DB was mostly working with the cut down 100,000 record size table. Occasionally, system resources when using it in form queries, but I closed that form and didn't bother proceeding with that one.

TodayI am trying to devise other work arounds that will allow me to continue to add more records to this table, by Copying the 100,000 file to a temp file, clean out original, do my updating to original which has 0 records, then return the temp records. 

But now, Access won't even let me do the Copy of the original table, reporting system resources exceeded.

This issue only arose after a W10 update, and then MS updated my Office programs on Thursday.

The MS help people are no use, claiming no knowledge.

Thank you so much for your assistance.

Neville Aganoff

[#7]

@George Hepworth , Thanks George, I am re-assured that there might be a solution to this but do I need access to the office insiders channel to fix this?

[#8]

@gnarlesv . I have an update on this issue. It seems related, either to my updating to the most current windows feature update or to a windows update that might have happened just prior to that. In any event, the error persists with the most current windows, windows features, and access versions. when i moved my tables to a local postgres database and connected to the tables via odbc, the systems resources error when away. I also moved my databases to my old laptop, also a lenovo thinkpad, which had not be upgraded to the most current windows, features, and access versions and the queries run just fine. My IT guys are trying to roll back the computer that has been having the issues. I will keep everyone post. 

[#9]

@gnarlesv yes, this is when it started for me. Last Thursday week, MS updated Win 10, and they also have me Office 365 (for no apparent reason Office 365 went off my system on Wednesday, whether MS did it in the background I don’t know). I use Office 2016 Professional, and now my machine is running Office 2016 Professional again. The issue still remains.

To get around resources exceeded problem, I have had to write code to work around queries and table updates to my archive table. This is unsatisfactory. The database is made to query data, not to fail because of record count. My code is failing in many spots and it is extremely difficult to do work arounds. 

I did find that some of the “exceeded” errors can be prevented by closing other MS apps. I almost got through an entire process after closing teams, outlook and excel.

That is unsatisfactory. I didn’t buy a good quality computer to have to close applications to be able to use Access.

If unwinding the update resolves the problem, I’m eager to hear. 

[#10]

@George Hepworth Do you have a source link for the known issue status and upcoming Current Channel fix?  We have had some success changing processor affinity in the past, but are now seeing these system resources exceeded errors frequently regardless of our affinity settings.  (A365, version 2008)  I don't see this issue mentioned in the beta or current (preview) channel release notes.  

Thanks!

[#11]

@George Hepworth hello, George.
Is this matter being looked into at MS to your knowledge?  
First time user, and I don’t know the procedure.

This is an obvious problem but I don’t know if this forum is just a discussion that is trying to find work arounds rather than the supplier dealing with the problem. 

If it is just a forum for solutions, do you know how one would tell MS about this?

My contacts with MS online chat and by phone have been to their staff who in essence told me they don’t know the answer as Access is a specialist product, and I have to pay MS a fee to have it investigated. Unsatisfactory. 

Today, after splitting my archive table and successfully using the smaller table of more current data, today I turned to access the larger second table in a query, and it failed due to system resources exceeded. MS Access is not functional any longer. To anyone who reads this post, this is not a trivial complaint, but it goes to the core failure of MS to be able to handle data in its application that it has always been able to over the last 20 years.

Even though this a MS forum, does any reader know of a similar application to Access that I can buy as Access is now not usable.

thanks

Neville

[#12]

This is just a discussion forum where people try and help one another with their Access database related problems.

There are different variation of this issue and I know MS is working on it, but no clue as to how long it will take it if they will fix this specific version of the issue.

With every new version of Access, this problem has been getting worse and worse. This is why sticking with an older version is actually beneficial as you are less likely to see this issue.

Use access.uservoice.com and the feedback command within Access itself to express yourself to Microsoft. Everyone needs to do so if ever things are going to change.

As for alternatives there are tons such as Alpha 5, FileMaker, Zoho, PowerApps, ... But truthfully I would not invest in another proprietary application and would instead turn to PHP/MySQL as it opens so many more doors and there is tons of information, forums, easy to find developers, ...

[#13]

@gnarlesv   I have been using MS Access for more than 10-15  years and this system resources exceeded error that started happening a month ago has been very frustrating. I have tried various combinations of set affinity in the start path /task manager and I am constantly getting this error even for small tables. This has to be fixed ASAP since I am sure thousands of users are currently struggling with this. Meanwhile  Just want to share with you  an alternate solution that I am using for the time being to do my data transformations. I am using   python  and a package called pandas to do my data manipulations.  and it is working fine. Once I am  done with my work inside python , I export the data to a csv file and then load to MS Access. I am a beginner in python but it is easy to learn like VBA

[#14]

I have just experienced what I believe to be the same error on the Siemens WinCC application (used for making industrial user interfaces).

The WinCC runs in a Win10 as the only application (except for what else is running on a Windows PC), but it uses a number of MS-Access-files through ODBC where these .mdb-files are registered as data sources.

I believe the behaviour is related to Win10. I have had the same application run on Win7 for many years without any such issues.

I can have the code trap the error "Error #-2147024882: [Microsoft][ODBC Microsoft Access Driver] System resource exceeded" - and make a reboot of the system - which solves the problem (for a while).

The just experienced incident is on a Windows 10 Pro, version 2004

I believe it has also happened on a Windows 10 Pro, version 1903, but here I did not have proper logging of errors set up, so I don't know for sure.

Anyway - I hope a fix for Win10 will address this soon - and then I need to find a way to install that on installations with no internet-access.

In the meantime I guess, I'll make logic to perform a reboot in case this error occurs... 

[#15]

@Jake104E thanks. 
I think also that W10 is the problem.

It first started when W10 had an update on my computer, but at the same time, MS updated Office (to an Office 365 version). The W10 update was in October.

I do not own Office 365 and a number of days later, the Office 365 version disappeared (I don’t know how, but didn’t bother to research), and my Office returned to the version.

After the initial update, the system resources error appeared, and has remained.

The error comes when my VB code is accessing large data tables. I closed Teams, Outlook, Excel and Word, then ran my access code, and it went through without error.

In an another run, accessing a different larger table, the error appeared though.

Since my access has not been changed, the problem can only be W10.

[#16]

@NevilleA This worked for me... I had MS ACCESS DB located in the area that is set to use OneDrive.  I moved the DB to an area that is not part of OneDrive and did not receive the error.

[#17]

@rtdavis3Can you be more specific as to where you moved the datbase? Where is OneNote not used?

[#18]

@rtdavis3 Thanks. It’s good you found a fix. I don’t use OneDrive however, so my error persists.

[#19]

@NevilleA 

I found a workaround. You need to to run the program as an administrator which, I speculate, frees up resources and the error stops.

Here are the steps to run the program as an administrator.  Start by copying your database/file location:

1. Right-Click on the desktop -> New -> Shortcut
2. Paste in your file location.
3. Name shortcut
4. Right-Click on the new shortcut -> Properties
5. Click Advanced
6. Check “Run as Administrator”
7. Apply

NOTE: When I first start the program I get "system resources exceeded". I close the program and re-open and it works fine. Not sure why I have to open, then re-close, for it to work.

[#20]

And I am getting this with version 2012 of Access 365. Not good.