RUn Time error 3035 - System Resource exceeded (1 Viewer)

[stepone]

Hi,

I might become a regular poster on here as I have a new job which is supporting a miscellany of Access and Excel applications, something I have not done for a few years now....

One thing I have inherited is an Access database which we use on a daily basis to run reports for customers.

Each time it's used, a customer-specific dataset is imported to a table, then update and make-table queries are run to perform various calculations before the data is copied back into Excel to create the final report.

The majority of the time this runs fine, but if the initial dataset is large (> 100,000 rows) then we sometimes hit an error "Run Time error 3035 - System Resource exceeded". The solution (up to now) has been to run the same job on an old Windows XP machine, running Access 2003, which seems to be able to handle even the largest datasets. It's only our newer, Windows 7 pcs with Access 2013 which have the problem. Since XP is no longer supported we really need to get the issue with Access 2013 resolved.

The process is kicked off from within Excel VBA, which opens our Master database, imports the dataset to the main table, then calls an Access VBA sub-routine, which in turn calls about 60 Access update queries and make-table queries one after the other, using docmd.openquery.

So, in Excel we have ;

    Dim acApp As Object
    Dim db As Object
    Set acApp = CreateObject("Access.Application")
    
    acApp.OpenCurrentDatabase (m_strDBCopyPath & m_strDBCopyName)
    'Set db = CurrentDb
    acApp.Visible = True
    acApp.Run "UpdateTables"

The 'UpdateTables' subroutine in Access is just a list of about 60 DoCmd.OpenQuery calls.

The report that I am running today is falling over consistently at a particular update query. I am told that it is not necessarily the same query that falls over each time and I am also told that occasionally just pressing F5 in the Debug window will eventually get the query to run, although I've not seen that myself. If I comment out the rogue query and press F5, then the process moves on 2 more queries before failing again with the same error message.

I have tried compacting and repairing the database, and adding Indices to the join fields being used in the query, but to no avail. I also googled and found a link to a Microsoft Hotfix for exactly this issue, but when I tried to install it, it said that it could not find any relevant application installed on my pc.

I have also tried changing the OLE timeout setting from 30 to 999 but it did not make any difference.

Does anyone have any other suggestions ? Do you think it might be worth splitting the subroutine so that I run, say, 10 queries at a time, closing and re-opening Access db in between ? Or is there a simpler way to free up resources between each query within the Access routine ?

Thanks very much for any suggestions...

StepOne

 

[JHB]

..The 'UpdateTables' subroutine in Access is just a list of about 60 DoCmd.OpenQuery calls.
...

Do you close them afterwards and before the next is open?
Why do you run 60 queries for updating data, it doesn't sound correct?

 

[stepone]

Hi,

Umm, .... no. The routine is basically as follows ;

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

StepOne

 

[stepone]

Sorry, I just saw the second part of your question. I know that 60 seems a lot. It's true that the number could possibly be reduced, however there are a large number of columns, each of which is updated based on values from a collection of lookup tables, so we really have to do each column one at a time as each column has a different lookup table, and a different set of criteria.

The database and process has grown over the years as more customer and products have come online. Part of my job will be to tidy it up, however at the moment I am just at the stage of trying to understand what each part does (there is no documentation !!).

Regarding docmd.openquery - is there a .closequery or similar I should be running after each call ?

Thanks,
StepOne

 

[JHB]

Sorry, I just saw the second part of your question. I know that 60 seems a lot. It's true that the number could possibly be reduced, however there are a large number of columns, each of which is updated based on values from a collection of lookup tables, so we really have to do each column one at a time as each column has a different lookup table, and a different set of criteria.

The database and process has grown over the years as more customer and products have come online. Part of my job will be to tidy it up, however at the moment I am just at the stage of trying to understand what each part does (there is no documentation !!).

Regarding docmd.openquery - is there a .closequery or similar I should be running after each call ?

Thanks,
StepOne

Look in the documentation for DoCmd.Close to get the different parameter and what they do!
Closing a query:

DoCmd.Close acQuery, "YourQueryName", acSaveNo

Do the user close them manually just now?
I think it could be the cause why the System Resource exceeded when they all still are open.
I know it is a hard job to learn and understand what is going on, in an application made by others, it takes time (many, many hours), and it is not so easy!
I'll only give you a good advice, put in comments in the code when you change something also when you think: "Now I understand the procedure/sub".

... however there are a large number of columns, each of which is updated based on values from a collection of lookup tables, so we really have to do each column one at a time as each column has a different lookup table, and a different set of criteria. ...

For me it sounds crazy and strange and very time consuming.

 

[stepone]

Do the user close them manually just now?

Hi,

No, the queries never actually appear to the user. They are all Update and Append queries. They run (the tables are updated and appended), then the database is closed by VBA. No need for the user to do anything.

I've checked documentation online, and nowhere does it say that I need to 'close' the queries programmatically each time. I will give it a go and see if it helps.

Thanks,
StepOne

 

[gemma-the-husky]

do you still have the A2003 disks? you could install A2003 on the W7 machine

A2003 has a lighter footprint than later versions, so big queries might be able to run on that, but run out of space on the W7 machine.

the other thing - is the dbs you are using ON the W7 machine, or on a network. I would be inclined to run this all on the local machine, to see if it makes a difference. It's similar to doing a compact and repair.

Trying to compact and repair a large backend from a connected PC is failure prone. Copy it to the host PC, do the c&R, and copy back is far quicker.

Edit - I now see you are doing this all from excel, which is likely to add a further layer of problems. could you not do this all directly in access?

 

[stepone]

Hi Dave,

do you still have the A2003 disks

I went looking for the disks at one point, and was directed to a cupboard. A rather big cupboard, with lots of shelves, lots of boxes, lots of dust, lots of old pcs, broken printers, various cables, user manuals.... you name it. There were some disks, but none of them were Office 2003.

is the dbs you are using ON the W7 machine, or on a network

I appreciate that would at least let me confirm whether it was Access 2013, or Windows 7 that is the problem. If I ever do find the disks I will give it a go.

The master Access database sits on the server, but is copied to the local pcs whenever they run a new job, so it will always be local when running the queries. We do compact and repair here pretty often, but since each user takes a fresh copy of the master db before running each job I don't think that's the issue.

could you not do this all directly in access?

We could run the VBA from within Access I guess, but do you really think that would make a difference ? When the problem occurs, I can manually open the db in Access and attempt to run the query that fell over, and I will get the same System Resource error. So it's not got to do with Excel or VBA running the queries, it's just that, after running a certain number of queries, Access itself complains of a lack of resources. One thing I still need to try is running the whole process manually. i.e. instead of using the VBA, just go into Access and run all the queries one after the other and see if I get the same issues. I will need to do that but it will take some time :banghead:

I will persevere and let you know how I get on. Thanks for all the help..

StepOne

 

[JHB]

..
I've checked documentation online, and nowhere does it say that I need to 'close' the queries programmatically each time. I will give it a go and see if it helps.

If you don't close them, they will be visible in the MS-Access window/workspace and when you run 60 queries all 60 will be stacked in the window.

 

[stepone]

If you don't close them, they will be visible in the MS-Access window/workspace and when you run 60 queries all 60 will be stacked in the window.

Hi,

I don't think they are - these are update and append queries. They don't open in the Window if you run them manually (by double-clicking on them), so I don't think they actually 'open' when called by VBA either, unless I am missing something. Anyway the VBA closes the Access application once the queries are run, so nothing is visible to the end user.

I have been testing the whole process, and it seems to be that, after the IMPORT routines, if we Compact and Repair the database before running the CALCULATE routines, it seems to run okay. I will look into whether we can make this part of the process, or include a 'Compact and Repair' programmatically.

Thanks for all your help,
StepOne

 

[sengerc]

Hi,

I don't think they are - these are update and append queries. They don't open in the Window if you run them manually (by double-clicking on them), so I don't think they actually 'open' when called by VBA either, unless I am missing something. Anyway the VBA closes the Access application once the queries are run, so nothing is visible to the end user.

I have been testing the whole process, and it seems to be that, after the IMPORT routines, if we Compact and Repair the database before running the CALCULATE routines, it seems to run okay. I will look into whether we can make this part of the process, or include a 'Compact and Repair' programmatically.

Thanks for all your help,
StepOne

StepOne,

Did you ever figure this out? I I have a very similar situation and have much better hardware 16GB RAM, SSD, Fast Processor, running MS Access 2013 64 bit, I get the error "RUn Time error 3035 - System Resource exceeded".

Running on an old PC, 4GB RAM, MS Access 2003 I have no problems.

Thanks for any help you can send. This is frustrating.

 

[gemma-the-husky]

if you are running into system resources issues, it may be an issue with the size of the query dataset, possibly hitting the 2Gb maximum.

Another solution is to do everything using recordset processing

 set rst = db.openrecordset("datatable")
 while not rst.eof   
    process record
    rst.movenext
 wend

it might take longer, and you need some programming, but you can display a progress bar, and just leave it running. Unlikely to crash for system resource problems.

 

[sengerc]

Gemma,

Thanks for the quick reply. The process I'm running doesn't update record by record, but it does break it down and take a day's worth of records at a time. It is run via vba. The query is only updating <300 records. When it errors out and I debug it, it will eventually run if I re-run it enough.

Do you have any things to check for on why the process works fine in Access 2003, but gives the on Access 2013?

In this instance, what it's erroring out on works perfect in Access 2003 on a lesser machine. If I need to rewrite all of my queries to do a single record update, it will be quite a task and much larger than I can undertake at this point and will have to shift back to 2003.

Thanks again!

 

[CJ_London]

you could also use

currentdb.execute("query1") rather than openquery

 

[spikepl]

#12

Doing everything using recordsets would probably bang your head against number of simultaneous something-or-other (don't quite remember what, but it's that thing at 70,000 as default, or thereabouts.)

I suspect the culprit in the OP's case was attempting to do everything in one subroutine - the garbage collection takes place after exit, and too much garbage kills the poor thing. I'd try to break it down into more subroutines.

As to what sengerc's problem is i don't know since sengerc has not specified what is going on in the code.

 

[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.

Nearly always in conjunction with a DOA operation.