Forum Discussion
system resources exceeded error
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.
- I just had this problem in Access 2010 32 and 64 bit. It came up when displaying a report whose query spanned 3 mid to large size tables. The report record source was updated dynamically in the report_open code. My workaround was - created a new empty saved query. Changed it's recordset type to snapshot (I think that did the trick of consuming less resources and performing better) and then changed the report_open code to save the dynamic sql string into the new query, and then set it as the reports' recordsource. Hope this helps
- I haven't experience specifically this issue; however, I received many error messages related to custom VBA functions created in Access 2002/2003/2007/2010. All error were gone after changing the MS Access 2016/2019 installation from 64 Bit to 32Bit, which its backward compatibility support of MS Access works much better with these custom VBA functions.
Note: I Know the main reason and the 'PtrSafe' fix for those errors caused by older VBA functions, we choose to remove them as well. I did not have option to fix these 'PtrSafe' errors, and continue, may be the next error could be the "... system resources exceed error" .
If you have subscription with these two versions, the default installation is 64 bit. You might want to try installing 32bit of Office instead. Aside from that, if your database have linked tables to some external sources such as QuickBooks and Sage 300, you have to have the 32 bit anyway. I have to say that both QuickBooks and Sage 300 support 32 bit of Office 365 (2016) and Microsoft 365 (2019) while your Windows 10 is 64bit, they do not support 64 bit of Office.
Following steps might not exact, but very close for installing 32 bit of 365;
- Check your installation in Add/ Remove Programs
- Continue if its 64 bit, or stop if its already 32 bit.
- Login to your Microsoft Account,
- Select Office Product, Office Products page opens,
- Open down arrow near Install button, select 32 Bit,
- Download - the default download folder is your C:\yourprofile\Downloads,
- Uninstall 64 bit of office,
- Install 32 bit using the file you downloaded gnarlesvI'm on a SQL server 2014 back end with ODBC connections to an Office 365 front end. My application is always up to date. This error occurs when you have too many connections open to the data source at one time. It usually happens to me when I have multiple datasets open either on forms on in tables and I leave the database open overnight.
A big culprit of this error are having too many open recordsets. One thing to check if you're coding in VBA is to make sure that you're closing out your recordsets by setting them to nothing. Another thing to check is the number of DLookups you're using. DLookups leave an open connection and can seriously tie up resources.
I've used a DLookup substitute called Elookup by Allen Browne for years. It really helps with this issue. Here's the link: http://allenbrowne.com/ser-42.html
HTH,
Patrick
ppaton open record sets may be the explanation. My code is lengthy mostly sql but it does have record set in it.
Even now as I have worked around the problem of resources exceeded by creating smaller temporary tables to work with, I do get code stopping occassionallly at an open record set command. It goes into debug mode, and I just hit F5 a couple of times to continue and let it finish without problems.
I must not have a record set close command.
I’ll take a look.
Thanks
gnarlesv I had a lot of Calculated fields in my Tables and experienced all kinds of failures, including the "resources exceeded" error, but also failed backups, and failed Compacts. Once I took the Calculated fields out of my Tables and put them into my Queries, all is now fine. No errors, Compacts complete (quickly) and no strange, out of the blue, application Quits.
Try removing ALL calculated fields from your tables, and good luck, I hope it works for you, too!
- I run 4 computers, 3 at work, 1 at home. I run 2 windows 10 and 2 windows 11. The only issue I have is on my surface pro which is running windows 11. Up until I upgraded to windows 11(beta) on the surface it didn't have the issued. Though I run it the database on my Windows 11 at home and have no issues. I only get the systems exceeded message when I try to view a sharepoint list through access. It seems to be a security issue more than anything else with sharepoint
- When you use beta releases anything can and will happen.
One should never use beta versions in production situations. It's fine for preliminary testing, but nothing beyond that.
And I am getting this with version 2012 of Access 365. Not good.
I have just experienced this same error again: "Error #-2147024882: [Microsoft][ODBC Microsoft Access Driver] System resource exceeded"
on a Win10 PC running Siemens WinCC - about 5 months after the first incident. A reboot of the Win10 solved the issue.
But is there some way to get a status from Microsoft on this issue?
Somewhere in this thread it appears that it is a recognized issue - but not indication of any actions.
Is it likely that Microsoft will issue a fix - of shall I keep using code to reboot in case of this error?
I don't see it listed on their official issues page
https://support.microsoft.com/en-us/office/fixes-or-workarounds-for-recent-issues-in-access-54962069-14f4-4474-823a-ff7e5974a570
That said, I know they are at the very least very well aware about the general resource exceeded issue. As to what is being done, when we might see an actual fix is anyone's guess. Considering this has been an issue for months, years,. It's not looking very good.I'd urge everyone having this issue to use the feedback button with Access to flag the issue, as well as post on access.uservoice.com . Remember, the squeaky wheel gets the grease!
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
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...
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.
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.
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.
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.
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.
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
