System Resource Exceeded, On Big Table
Can not allways count rows, update fields (ADODB/DAO),
SUCCESS RATE ................ONLY 40-80%...!!!!
BUG, Confirmed by :.......... Deepak Panchal10 - 9,745Points
Microsoft Office for Developers.Access for Developers
Steve Calabria commented
I have a business-critical situation that concerns a known problem in MS Access that I believe Microsoft should give its highest priority to addressing as the issue exists with the current versions of Access and Windows.
The problem has been reported to the Microsoft o365 group who has investigated. The group has witnessed the errors demonstrated during screen shares and confirmed:
• Office and Windows are up to date
• No third-party software is running
• I am using Access within its guaranteed specifications
The o365 recommendation was open a $500 ticket with the Microsoft Professional Group, which I have done.
The Microsoft Professional Group has also investigated, witnessed the errors, and has advised me that this is a known problem. I have been told Microsoft may, or may not, eventually fix the problem and my best option is to leave a comment on the User Voice forum.
The Microsoft Professional group has made the following recommendations:
1. Purchase Premier support at a cost of $12K per year so this matter can be escalated to Development
2. Wait for Microsoft to get around to fixing the problem
3. Open a $500 ticket with the SQL group and then migrate to SQL Server
I have a split FE/BE Access database running on Win10 Pro and Office 365. The size of the FE MDB is now over 330MB. The BE is over a LAN and consists of 8 different MDB files, one of which is very large.. (>1 million records, 240 fields, 1.2 GB)
I constantly get system resources exceeded as well as other very strange error messages when I run complex queries. In addition, I get errors using SaveAS MDE and also during Compact and Repair operations.
The problem occurs with both the x86 and x64 versions of Access o365 and has occurred on 3 different machines with up to 16GB of RAM and plenty of hard disk space.
Errors during SaveAS MDE and Compact include:
1. Could not start transaction; too many transactions already started.
2. The query cannot be completed. Either the size of the query result is larger than the maximum size of ta database (2 GB), or there is not enough temporary storage space on the disk to store the query results.
3. System Resource Exceeded
The problem occurrence frequency has increased significantly as the FE mdb file has grown in size.
The only workaround offered has been to set the affinity to one core, a workaround which worked while the FE MDB was much smaller in size, however, as the size of the FE MDB has grown, setting the affinity to one is no longer effective.
System Resource Exceeded errors occur while running a number of different queries that include Big Back-End tables. It occurs when joins are used as well as when no joins exist. A simple query of a large table with filters and sorts will cause the problem to show up.
The affinity workaround is a horrible recommendation. Even if the developer includes code to set the affinity to one core, if a database maintenance is performed such as a compact and repair, without remembering to set the affinity to one, Access will drop indexes randomly, or completely corrupt the database file when attempting to compact.
The only workaround I have found is to clear memory by rebooting the machine immediately before attempting to compile (Save AS MDE) or Compact.
Microsoft o365 group has advised me that a prior investigation determined the problem may be related to issues that concern the bitness of the system whereas 64 bit operating system typically have more memory but with 32-bit Windows the apps may be able to handle larger tables as 32-bit Access would get a message from the OS, then free unneeded memory, and thus can work with larger desktop database files.
I am using the latest version of Microsoft Access, the latest version of Win10, have all updates installed, no third party software running, I am operating within all of the specified limits of Access, yet unless I reboot my machine before using built-in Access features such as SaveAs and Compact, I get error messages.
I have paid the $500 support fee to Microsoft’s Professional group and still the problem remains unresolved even after more than a hundred hours of engineering development work on my end, working with the community on Expert-Exchange and Stack Overflow, and more than 30 hours of telephone/Microsoft Meetings support.
1. Wait for Microsoft to fix this since they already know about the problem.
2. Migrate to SQL Server
While I suspect migration to SQL Server may solve the query problems it seems unlikely that this migration will fix the SaveAs and Compact problems.
Please be responsible. Fix this problem.
João Rodrigues commented
Windows 10 Corporation Microsoft Access 2010 V.14.0.7229.5000 (32bits)