Getting Error 3035 System resource exceeded. (1 Viewer)

[sportsguy]

good afternoon readers!

I have manual queries which run just fine, but in the following sub, the second append query blows up and give the error 3035 System resource exceeded. :banghead: so I am asking for the experts' help.

The data is copied from excel and pasted into a form in table stgIS4515, works fine. The table has many duplicate records which must be totaled for usefulness. round numbers, 85K rows get totaled into 20K rows and appended in a second table, tmpIS4515. the first table, stgIS4515, have all records deleted and then the second table, tmpIS4515, is pasted back into the first table, stgIS4515, to continue with processing. The stg and tmp tables are identical with autonumber key field.

the second query is a simple append query, which has no totalling. . .

Any suggestions for getting the code to work without erroring and corrupting the stgIS4515 table?

thanks in advance, sportsguy

Public Sub Rebuildstg4515()
On Error GoTo Err_Rebuildstg4515

    Forms!frmHome.Visible = True
    DoCmd.Close acForm, "frm4515D", acSaveNo

With DoCmd
    .Hourglass True
    .SetWarnings False
    .RunSQL "DELETE tmpIS4515.* FROM tmpIS4515;"
        Debug.Print "Query Delete1"
        Call Pause(5)
    .OpenQuery "aptmpIS4515", acViewNormal, acAdd
        Debug.Print "Query Append1"
        Call Pause(5)
    .RunSQL "DELETE stgIS4515.* FROM stgIS4515;"
        Debug.Print "Query Delete2"
        Call Pause(5)
    .OpenQuery "apstgIS4515", acViewNormal, acAdd
        Debug.Print "Query Append2"
        Call Pause(5)
    .RunSQL "DELETE tmpIS4515.* FROM tmpIS4515;"
        Debug.Print "Query Delete3"
        Call Pause(5)
    .Hourglass False
    .SetWarnings True
End With

    DoCmd.OpenForm "frm4515D", acNormal
    Forms!frmHome.Visible = False
    MsgBox "Data Totaled and Saved!", vbExclamation, "NATIONAL GRID Financial Business Partners"

Sub_Exit:
    Exit Sub

Err_Rebuildstg4515:
    Debug.Print Err.Number & " " & Err.Description
        Application.Echo True
        DoCmd.Hourglass False
        DoCmd.SetWarnings True
    Resume Sub_Exit
    
End Sub

append query 2 which blows up the table is seen below

INSERT INTO stgIS4515 ( FY, Fiscal_Qtr, FiscalPeriod, FYMonthSAP, CYMonthSAP, L04_OCC, L05_OCC, L06_OCC, CostCenter, CostCenter_Desc, EEID, EE_Name, Job_Code, Position_Key, Job_Title, Supervisor, [Band], L03_RCC, L04_RCC, L05_RCC, L06_RCC, Dept, Dept_Desc, OrderID, Order_Text, INVP, INVP_Descr, INVP_Family, INVP_Fam_Descr, INVP_Org, WBS, Project, Project_Text, Activity, Activity_Text, Allocation_Code, L02_CostType, L03_CostType, Functional_Cost, FERC, GL_Account, GL_Acct_Desc, Payroll_Area, Wage_Type, Work_Date, Hours, Total_Amt, OPEX, CAPEX, OTHER, OPEX_OtherL03, OPEX_OtherL04, OPEX_OtherL05, Delivery_Team, Productivity, ProductivityActivity, ProjectType, TimeEntryType, ExclusionManual )
SELECT tmpIS4515.FY, tmpIS4515.Fiscal_Qtr, tmpIS4515.FiscalPeriod, tmpIS4515.FYMonthSAP, tmpIS4515.CYMonthSAP, tmpIS4515.L04_OCC, tmpIS4515.L05_OCC, tmpIS4515.L06_OCC, tmpIS4515.CostCenter, tmpIS4515.CostCenter_Desc, tmpIS4515.EEID, tmpIS4515.EE_Name, tmpIS4515.Job_Code, tmpIS4515.Position_Key, tmpIS4515.Job_Title, tmpIS4515.Supervisor, tmpIS4515.[Band], tmpIS4515.L03_RCC, tmpIS4515.L04_RCC, tmpIS4515.L05_RCC, tmpIS4515.L06_RCC, tmpIS4515.Dept, tmpIS4515.Dept_Desc, tmpIS4515.OrderID, tmpIS4515.Order_Text, tmpIS4515.INVP, tmpIS4515.INVP_Descr, tmpIS4515.INVP_Family, tmpIS4515.INVP_Fam_Descr, tmpIS4515.INVP_Org, tmpIS4515.WBS, tmpIS4515.Project, tmpIS4515.Project_Text, tmpIS4515.Activity, tmpIS4515.Activity_Text, tmpIS4515.Allocation_Code, tmpIS4515.L02_CostType, tmpIS4515.L03_CostType, tmpIS4515.Functional_Cost, tmpIS4515.FERC, tmpIS4515.GL_Account, tmpIS4515.GL_Acct_Desc, tmpIS4515.Payroll_Area, tmpIS4515.Wage_Type, tmpIS4515.Work_Date, tmpIS4515.Hours, tmpIS4515.Total_Amt, tmpIS4515.OPEX, tmpIS4515.CAPEX, tmpIS4515.OTHER, tmpIS4515.OPEX_OtherL03, tmpIS4515.OPEX_OtherL04, tmpIS4515.OPEX_OtherL05, tmpIS4515.Delivery_Team, tmpIS4515.Productivity, tmpIS4515.ProductivityActivity, tmpIS4515.ProjectType, tmpIS4515.TimeEntryType, tmpIS4515.ExclusionManual
FROM tmpIS4515;

 

[theDBguy]

Hi. What are the size of your files (FE and BE)?

 

[sportsguy]

FE size = 16 megs,
BE size = 150 megs after loading 80K records, 13megs after loading 20K records

don't think that is the issue. . .

sportsguy

 

[The_Doc_Man]

Just for the record, you can leave off "tmpIS4515" as a prefix for that SELECT component of the INSERT INTO query that represents your "Query 2" that is in that sequence. You have only one "FROM" source table so there is no chance of any ambiguity. Less typing, easier to read, and might even help with assurance of proper alignment.

I think I counted 59 fields in that query. How many of them are indexed besides the PK? Because deleting and repopulating indexes isn't a cheap action.

When you try to run the query, before you do so, check the DB size for the component that contains these tables. Then when it barfs, immediately do a size check. You might expect some size changes. Do you do a compact & repair of the DB component holding those tables before you run your sequence?

Is there a chance that any of the fields being copied are complex, i.e. they are a multi-value field? Or are they all elementary data types?

You describe some kind of processing that must be done in your "Query 1" (as your message box names it) because all that we can see in the other queries is just simple deletes or a simple append-from-table. Is that some sort of Aggregate query with perhaps some kind of GROUP BY clause? If so, is the field on which the grouping is based an indexed field?

EDIT: You were a participant in a thread (in fact, one that you started) titled "System resources exceeded -> really??" in which you were given links to other threads that have explored this issue in some depth. This isn't a "ding" but it is interesting that you have run into this same problem more than once. One of the methods suggested in the other thread (if you followed the links) involved the MaxFileLocks parameter. Have you diddled with that one yet?

 

[sportsguy]

Docman, yes, I have a cloud version of Access 16, the corporate savior Office 365 (16) I can't find the registry entry after looking in all the places which were referenced in the links, so that is one of my issues, see the attachment for registry entries

The other is yes, I checked the BE file for sizes, before and after, although, I didn't check the front end, with the group by query as the first query may be doing something, but not sure why the BE table is corrupted in code when it works manually. The second query, from your comment, was a copy of the access SQL of the query, which I run as an access query, not in code.

The numeric fields are decimal, but not sure why that would matter when all field sizes are the same, with plenty of left of the decimal space.

 

[gemma-the-husky]

I would setwarnings true not false while you are testing
you need to see the system messages.



If you import a table, and then build a cleaned staging table from that - whyy not just carry on from the cleaned table? - why bother deleting and re-appending the records. It shouldn't matter, but it seems somewhat pointless, and you never know - it uses up more resources.

 

[sportsguy]

Gemma, its the duplicate records which must be summed / totaled no matter what, because they system report has issues, and they are working on it.

so thanks, but not really answering the question why does this work manually just fine, and not with VBA. . .

sportsguy

 

[isladogs]

A couple of points to consider.
1. When you do your processing to reduce 85K rows to 20K, why do you delete the records in the first staging table then paste back from the tmp table before continuing processing? Why not just continue from the tmp table?

2. System resource exceeded errors can often be managed by temporarily or permanently increasing the MaxLocksPerFile settings in the registry. The default is 9500. Try increasing to say 15000 ... or more in small steps.
Similarly there is another registry setting MaxBufferSize which could be increased if necessary

 

[Deleted member 147267]

if you don't mind me doing a bit of straw-grasping:
1) set a variable (db) to CurrentDb and change action sql call method to

db.Execute "sqlStatement Or QueryName Here", dbFailOnError
Maybe there is less overhead compared to DoCmd. Regardless, the added benefit will be that it will allow you to suppress action messages and still report a failure.

2) if you have Track Changes option enabled, it can be troublesome. Suggest trying with that off.

 

[gemma-the-husky]

Gemma, its the duplicate records which must be summed / totaled no matter what, because they system report has issues, and they are working on it.

so thanks, but not really answering the question why does this work manually just fine, and not with VBA. . .

sportsguy

no. but running the query with warnings true might give some helpful messages while it is doing it.

micron's and isladog's suggestions (isladog's suggestion 1 was what I suggested as well ) are also worth trying.


There must be something different happening by running the process in code, and just opening the query.

for example
currentdb.execute ("delete * from tablername") is instant
docnd.openquery to do the same is not instant, I think.

 

[sportsguy]

Gladly show me where in the registry where to increase records locks, etc, with cloud based office 365 . . . I have looked, and can't find the entry. See the attached graphic on the reply to DOCMAN. . . or which location i create the entry.

I realize everyone is trying to help, I am just approved for azure database and they are awaiting a response to some questions, as access is just too slow and small, as I routinely exceed a single access databases' limits. . . but i have to finish this month's data first

 

[sportsguy]

and I know which query is blowing up the database, I just don't know how to fix it at the moment or why

 

[sportsguy]

isladogs,

actually, I will implement that strategy. . . I overthought the process, based on normal practice of having the form open for data review, but you make a great suggestion which can be implemented very easily, and still works for what i *need* to do. . .

Thanks again, and I would like to know where to enter a change in the registry. . .I am not a registry geek with Office 365 . . .

sportsguy

 

[Deleted member 147267]

FYI- if still interested in max locks setting, search registry for "Engines" Menu > Edit> Find or ctrl+F3
Should pinpoint the location of your ACE folder (or just try searching ACE). Mine is at

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Wow6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\ACE

Locations from other threads possibly outdated because not only does it change on version but bitness too.
I see you posted while I was searching. Have edited this post.


What is "cloud based 365"? Surely you are not using this db over OneDrive, as in tables on OneDrive, front end on PC?

 

[The_Doc_Man]

If your data is attached via cloud then you are at risk of corruption. Cloud connections will appear to support Access because SMB protocols can be used, but if the cloud connection ever breaks for just a moment, that connection is dead and the table is left in a half-updated state or worse.

For that matter, I believe you can diddle with MaxLocksPerFile this way:

DBEngine.SetOption dbMaxLocksPerFile, 200000

Note that the number I used is arbitrarily large and I have no idea how big a number you will actually need. Colin (Isladogs) has also played with this and uses 15000 as his first-try number. Setting this number too high can affect other aspects of program performance, so it is a "tuning" type of thing first and foremost.

 

[isladogs]

I have several registry locations for MaxLocksPerFile. This may be partly because I have A2010 & A2019(365) on the same PC. The locations will depend on your versions and bitnesses of both Access and Windows.
For example, the location given by Micron is for 32-bit Access for Office 365 running in 64-bit Windows. Your results may be different.
The easiest solution is to do a registry search for MaxLocksPerFile and modify all of those you find.
Be warned that the value revert after certain updates so you may have to repeat at intervals.

Alternatively use VBA to temporarily increase the values as Doc suggested. This may be a better solution at least in the short term.

 

[sportsguy]

Micron,

LOL! no, i am not a noob! long long time access user who prefers sql server, but am working at a very old style, slow moving, company with a monopoly status legally, and i just need 6 more months for 401K vesting and 2-3 more years for retirement, and its like banking my head everyday as the company is 10 years behind in many technologies.

Thanks and sorry for the repeats,

 

[The_Doc_Man]

Actually, I can sympathize. Working for the U.S.Navy didn't necessarily put me on the leading, bleeding edge of technology. We stayed with WinXP for a long time after Win7 and Win8 and Win8.1 were out. Eventually, just as Win10 started to deploy to the rest of the world, we switched to Win7. On the other hand, we were able to get pretty much up-to-date versions of Office and NOT the cloud-based stuff, either.

 

[sportsguy]

So, I have tried running the query as a select query, works just fine.
When i convert the query to an append query, and try to append it to the empty table in design view, the system resources exceed error message shows up in design view.

The query will run without grouping and summing, meaning record for record append works. As soon as the 80K records go to 20 records in the grouping and summing for the append, the query exceeds system resources.

so, I appreciate the help, setting the max locks etc didn't help.

 

[sportsguy]

I seemed to have solved the issue, too many group by columns, so i eliminated enough columns to have the append query run. . .

sportsguy