System Resource Exceeded Error 3035 (1 Viewer)

[stepone]

Hi all,

This is a follow up to my post from last September. We have an Access process which we run every day (several times) but for some large data sets we get the above error - System Resource Exceeded.

The process is split into 3 main subroutines - each of which calls a large number of queries - update, append and make table queries. For example we have a routine like the following ;

Sub PrepareSummary()
	DoCmd.OpenQuery "qryA1"
	DoCmd.OpenQuery "qryA2"
	DoCmd.OpenQuery "qryA3"
	.......
	(60 other queries)
	.......
	DoCmd.OpenQuery "qryZ1"
	DoCmd.OpenQuery "qryZ2"
	DoCmd.OpenQuery "qryZ3"
	DoCmd.OpenQuery "qryZ4"
End Sub

Generally the process will fall over somewhere in the middle of the 2nd or 3rd routine (which have 100 to 200 queries in them). It doesn't always fall over at the same place but there are a few common places which it might fail at.

I managed to make some progress last year by adding 'Compact and Repair's programatically in between each of the main subroutines. This has helped, however we still see the issue on some of our largest datasets (> 400,000 lines in the main table).

This week I've been trying some other things.

1. I tried using currentdb.execute to call the queries instead of DoCmd.Openquery. This appeared to make no difference - it still failed at the same point.

2. I then tried wrapping each .execute statement in a transaction ;

stSQL = "qryB011_AddTest"
    ws.BeginTrans
        .Execute stSQL, dbFailOnError
    ws.CommitTrans

This just made things worse - it falls over much earlier in the process with the same 'System Resource Exceeded' error.

I also tried changing the Max locks setting first ;

DBEngine.SetOption dbMaxLocksPerFile, 200000

However this made no difference.

3. I also tried, in the Properties of the main query which fails, setting 'Use Transaction' to No (as recommended in a MS Knowledge Base article I found). This also made no difference. (It seemed to help sometimes when I manually run the queries, but if I run the entire process from scratch it still falls over).

My next step is going to be to split the routines into smaller chunks, and call these chunks separately, doing a compact and repair between each one.

However, this is not a very neat solution in my mind, and I know that 'Compact and Repair' is not without its risks (in fact, during this process, we have seen a table being deleted by a Compact and Repair). So I am still looking for that elusive silver bullet that will resolve this. Does anyone have any more suggestions of things that I could try ?

Thanks very much...

StepOne

 

[spikepl]

So you've tried everything you know on the process as is, and it still keels over. Perhaps time to review the process?

a large number of queries - update, append and make table queries

Make table -queries are often indicative of stop-gap measures - I'd review the entire process.

 

[CJ_London]

you may also have got some duff data in there somewhere - track to which query is falling over and look at the datasets it is working on

 

[Simon_MT]

You could try closing each Query after execution.

Simon

 

[stepone]

Thanks for the replies.

Spike - the process definitely needs re-designed. It has grown from a single spread-sheet, over the last 15 years, through several different developers (none of which have documented or commented anything!). I estimate 12 months to design and develop a new system, and I am speaking to management about this. In the meantime I am doing what I can to improve the day to day issues.

CJ. I will take a look at the data.

Simon - are you able to expand on this at all ? Nowhere in any Access documentation is this mentioned, but I have seen people on these boards say it before. These are all action queries, so I do not believe they are actually 'opened' and I'm not aware of a DoCmd.CloseQuery method. Anyway I have also tried using .execute and the same problem occurred.

As an update, I have been going through some of the queries setting 'Use Transaction' to NO, and also I have been adding indices to some of the fields using in these queries, and I think these things are helping, although this morning I am still seeing the process fail on my large test dataset.

Thanks,
StepOne

 

[Simon_MT]

This is anecdotal as I can't remember why I implemented this but I think there was a problem with Too many databases are open!

    DoCmd.SetWarnings True
    Call Delete_WebOriginals
    DoCmd.OpenQuery "UpdateOriginals", acNormal, acEdit
    DoCmd.Close acQuery, "UpdateOriginals"
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "UpdateOriginalsImagesSmall", acNormal, acEdit
    DoCmd.Close acQuery, "UpdateOriginalsImagesSmall"
    DoCmd.OpenQuery "UpdateMediumOriginals", acNormal, acEdit
    DoCmd.Close acQuery, "UpdateMediumOriginals"
    DoCmd.OpenQuery "UpdateArtistsOriginals", acNormal, acEdit
    DoCmd.Close acQuery, "UpdateArtistsOriginals"
    DoCmd.SetWarnings True

Simon

 

[TBA]

Hi

This is caused by running out of available virtual memory (VM) aka swap disk. A 32 bit app cannot use more than 2gb and for some reason Access uses a lot of VM and when it needs more and cannot get any then you run out of system resources.

Solution is to make sure your VM is at least 4 times the RAM and to restart your PC at least daily, only this clears out the VM from garbage left lying around from other apps.

You will never have had this issue on a 32 bit OS, its only now with 64 bit OS that this happens.

 

[The_Doc_Man]

What tickles my interest is the "SYSTEM RESOURCES EXCEEDED" message, which has to do with something totally outside of Access.

Windows has these things called "Resources" which are, in essence, memory consumers. For instance, a defined Font or a set of icons or a set of opened files. Here are a couple of links related to resources:

http://www.dummies.com/how-to/content/how-to-monitor-system-resources-on-a-windows-pc.html

http://computer.howstuffworks.com/question466.htm

The web can give you a bunch of articles on system resources.

If changing the MaxLocks parameter didn't help, it wasn't a file lock that got you. It was some other kind of resources. The links will get you started on figuring out (using Task Manager and the associated Resource Manager) what is going to Hell in a handbasket on your system. Until you know that, you won't be moving very quickly on your problem.

 

[sxschech]

May not apply in your case, but had a situation where people had same computer model and software (bought and set up at same time) and some would get the error and some not. The difference was that the ones who added virus checker running got the error. They have to turn off the virus program in order to get things to run and then turn it back on after.

 

[The_Doc_Man]

sxschech, that incident would probably have been due to the FILE resources issue, or at least I'm guessing that it would. Particularly if the A/V program stayed open with signature files open for monitoring network traffic. The resources consumed in this case would be the file handles.

 

[sxschech]

This kb seemed to help for one of the computers that was still having a problem. It suggested to change the virtual memory. Scroll down to the last section "Change the size of virtual memory" for the steps.

https://support.microsoft.com/en-us/help/15055/windows-7-optimize-windows-better-performance

 

[missinglinq]

Access is asynchronous, which is to say, if given a series of commands, it starts to execute one, moves on to the next one and starts executing it, and so forth. It doesn't wait for the first command to be completed before starting the second one, and this can cause timing problems.

An example that I always give, for this problem, is just what you're facing, the running of multiple Queries. In this example a button that runs a series of Queries where all but the first Query is dependent upon the previous Query being completed before it starts to execute. The following VBA code

DoCmd.OpenQuery "QueryA"
DoCmd.OpenQuery "QueryB"
DoCmd.OpenQuery "QueryC"

will immediately run all three, not waiting for one to finish executing before starting the next one. The answer to halting the code in this type of situation is to use DoEvents.

DoCmd.OpenQuery "QueryA"
DoEvents
DoCmd.OpenQuery "QueryB"
DoEvents
DoCmd.OpenQuery "QueryC"

DoEvents returns control to Windows, allowing QueryA to complete running before starting to run QueryB. It then allows QueryB to finish running before starting QueryC, and so forth.


It'll also work where, at times, so many Queries are running that system resources, as you're seeing here, are simply overwhelmed!

DoEvents is an easy, safe bet when encountering what seems to be timing issues or resource problems.

Linq ;0)>

 

[The_Doc_Man]

linq - this has been discussed in other threads. Access is NOT asynchronous - but either Ace or Jet could be running a few things with a little bit of overlap. The solution is NOT to use DoEvents but to use DBEngine.Idle (look that up in Object Browser or online for details.) In essence, "DoEvents" lets things happen but is using a probability issue in hoping that the asynchronous ops close by letting time pass. Or, you could use DBEngine.Idle to wait specifically for asynchronous opts to close.

The error "WINDOWS RESOURCES EXCEEDED" usually isn't about queries overlapping inside Ace/Jet. A Windows Resource is a specific Windows data structure. I wonder if this could be solved by "sand-boxing."

Stepone, can you perhaps try this? Your big "import" is running into problems with resources, and resources are consumed by things that are running. Talk to your security people about this, because your business may not like this idea. Running programs consume resources like crazy. Ask your in-house support person if there is a way to set up your machine to turn off services that aren't absolutely necessary to a system acting on a dedicated program.

I don't recall whether you have a split FE/BE on two machines, but networking services can be extensive. There is also the "Windows Indexer" service. Then there is the possibility that you could set up a separate user profile that has practically NOTHING installed, and reboot your system before you switch to that profile. I don't recall having done this recently, but it is also possible to remove fonts, and every one of the fonts in your list represents one or more resources. Every ICON on your desktop is a resource (which is why I was suggesting a dumbed-down profile with minimum things installed.)

Windows in general likes to install stuff in the background of your system, opening services you never even HEARD of. If you have a desktop support person, get some help with the services and icons issue.

TBA, you are incorrect regarding the relevance of Windows VM/swap file. The Windows Resources table is RESIDENT, not swappable. Besides, there is a specific and very different error message for running out of virtual memory. WINDOWS RESOURCES EXCEEDED is different than any swap-file problem.

 

[missinglinq]

All I can tell, you, my frind, is that your post flies in the face of everything I've read for over twenty years...and DoEvents does, in fact, resolve this type of issue.

 

[sxschech]

I think I posted #11 to the wrong thread. It was supposed to be for the one using recordsets - Getting file sharing lock count exceeded error, but when I did the search this one came up and since it had been a while, thought it was this one since the error message was about System Resources rather than sharing lock. The suggestion was more for thread http://www.access-programmers.co.uk...52&highlight=system+resources+exceeded&page=2

Should I post there or leave as is?

I agree with missinglinq about adding DoEvents between queries or other parts of vba code that need a moment before doing the next step.

 

[The_Doc_Man]

Other threads in this forum have discussed asynchronicity, linq. Ace or Jet can go multi-threaded but Access itself cannot for a single database. Which is why you use the dbEngine options to wait for the engine to go idle.

DoEvents maybe DOES work most of the time, but it is doing it by hoping that enough time passes for the asynch operation to finish. DoEvents suffers from a fatal flaw in this case - it says "Do all pending events and come back to me" - but what if no events are pending? On a lightly loaded system, it can happen, and in that case, it is a no-op.