Still having error 3035 problems. (1 Viewer)

[Royce]

I have two different situation where I have not been able to solve the Error 3035 "System Resource" exceeded problem. I've looked for memory leaks, set the MaxRecordLocks to 1 million as suggested elsewhere, and tried various rewrites, all to no avail.

The latest is a routine that moves data from one database to another. I open a recordset with a list of tables to migrate, build a sql string in code and pass it to an ExecuteSQL routine that wraps dbLocal.Execute strSQL with error handling. (dbLocal is a function that returns a reference to CurrentDb)

Most of the time it works. When I am moving a lot of data I get the Error 3035.

Sure would appreciate some help on this.

 

[The_Doc_Man]

The problem with "System Resources Exceeded" is that it isn't Access that eats your socks here. It is Windows. Access is a consumer of system resources, but so is everything else that can be run, displayed, or iconized.

Without knowing more about the details of your process, it is hard to decide how best to proceed.

Some things that might cause higher than normal consumption of resources:

* Opening and closing items that can be re-used. For example, instead of having a function to re-generate a pointer to the CurrentDB, make that point ONCE in a global variable (Public in a General module is the easiest way) and then just forever use that pointer for as long as the app is open.

* If you have application objects, don't create and destroy THEM, either. Instead, open the thing you wanted, diddle, and close the thing you diddled - but leave the app object extant until your program exits. Again, using globals or general/Public variables for this will work ok as long as you don't have to do a code reset because of an improperly handled trap.

* Depending on site policy, you might wish to find a few fonts that you really don't need and de-activate them. Fonts take up resources too! Last I looked, "standard" Windows comes with well over 100 font families. Some are really necessary. But some are purely redundant and not at ALL necessary in a business establishment on a database host. Example: For a graphics design business, the graphics-application machines WOULD need the fonts - but a standalone database workstation might not.

The issue is that it is difficult to identify where all of your resources went because damned near ANYTHING on the machine, maybe except for document files for your various applications, will consume resources.

You can look up the topic of "Windows Resources" on the web to find some articles that suggest how to manage this problem.

I found a couple of articles that MIGHT give you some hints. But don't assume I found everything.

http://tweaks.com/windows/37057/disable-unnecessary-services-to-free-system-resources/

https://www.howtogeek.com/school/pc-maintenance-for-beginners/lesson2/all/

http://www.apptools.com/rants/resources.php

The last of those three gives a reasonable explanation of the resources that you are running out of, although it is an old article and doesn't cover Win7,8,10.

 

[jdraw]

Royce,
What is the rationale for this?

The latest is a routine that moves data from one database to another.

.That is, why do you have to move the data, in business/plain English terms?

 

[Royce]

Moving data to a SQL database to make it accessible to more users. (growth issues). As I said, I actually have two different situations, very different situations. The other is a business app where the problem seems to be a form with 42+ subforms (Calendar app) which has been rewritten several times in various ways, none of which solve the problem.

I'm actually looking for general information about the problem. Looking for a clue.

 

[HiTechCoach]

I'm actually looking for general information about the problem. Looking for a clue.

IMHO, The general information is: The issue is specific to the design of your database and your unique code.

 

[Royce]

Perhaps I should have said I'm looking for things outside the common ones that might help me figure out what is wrong. For example, is there anything in Performance Monitor I should look at? Is there any way to find out what resource is the problem? (I'm not seeing anything in Memory, Threads, Handles, etc. that looks abnormal.)

The code in my latest problem is code that has worked elsewhere. I did discover that the error is occurring on a table with 300,000+ records after I have moved more than a million records in some 30 tables. I don't know if that is significant, but I suspect it is involved.

I'm already using a global dbLocal variable that points to CurrentDb, using dbLocal.Execute strSQL, and have tried to check for memory leaks, etc. Memory, CPU Usage does not seem to be a problem on either machine (The one running Access or the SQL server host.) This code is very simple, and I never have more than one form open. Basically looping through all the tables and passing insert queries to SQL server via an ODBC Connection.

 

[Royce]

DocMan,

I printed out your suggestions as a checklist and went back through the code, also looked at the links, but I had already implemented all of them.

Right now I'm looking at rewriting it to export to text files and use bcp to import them to sql. (Time is also an issue. 3 hours! Taking too long to test, though when I'm done it won't matter.

 

[The_Doc_Man]

I had a good description somewhere of all the things that consume resources (and it told what kind of resources were consumed by each.) Unfortunately, it was something I found only after drilling down a long way. Like an idiot, I didn't bookmark it.

The problem, as best I can describe it in vague terms, is that a "resource" has multiple meanings. You don't usually get a "resource" error message when running out of memory even though memory is a consumable resource. You get an error that is specific and includes the word "memory" to let you know what resource is involved. It is the OTHER type of resource that gets the "System Resources Exceeded" message.

A Windows System Resource (WSR) is a data structure of some type that is rebuilt with each reboot. It lists everything in the system's startup list and service list and icon list and font list because all of those items can consume a WSR. Each WSR is like a pre-registration that says "During the course of this session, this registered item might have to be displayed." The part that is so confusing is that there are many types of basic resources - and that is because damned near anything in Windows can consume one or more of these WSRs.

This Wikipedia article might help you understand better what resources your are consuming. The "Resources Exceeded" message is specific to the entities listed in the article.

https://en.wikipedia.org/wiki/Resource_(Windows)

The next reference lists resource types. I'm betting that the resource you are consuming is one of the application-based custom resources.

https://msdn.microsoft.com/en-us/library/windows/desktop/ms632583(v=vs.85).aspx

The reason you run out of these is that as the startup code identifies things to be run, it notes each resource. That's why your disk chatters like crazy at startup. It is looking at every file that has been identified as a potential resource consumer. The upside is that Windows has pointers to the things it needs for the display, dialog, or whatever, all of them ready to go. BUT the downside is that the resource table apparently has a finite size and cannot expand past some point. So if you reach that point, ... "System Resources Exceeded."

I wish I could tell you how to fix this but it boils down to the typical Windows problem of trying to stuff too much crap in the box. I swear sometimes I think Microsoft could make a new memory model that supports hardware memory management in the RAM Petabyte range and Windows would STILL run out of some resource somehow!

 

[static]

https://support.microsoft.com/en-us...ssage-when-you-perform-a-query-in-access-2010