System resources exceeded -> really?? (1 Viewer)

sportsguy

Finance wiz, Access hack
Local time
Today, 08:31
Joined
Dec 28, 2004
Messages
358
Access 2010 x32, Windows 7 x64
FE db size 35M
BE db size 1.5G single table

made a table from BE in FE with 25K records.
deleted records after creation
keyed table,
ran same query as append to same table
Error message System Resources exceeded

How can I fix the issue? hotfix applied, error seldom happens,
but this time, I need the table to update some missing data,
and I don't understand why I get the message. . .

Thanks
sportsguy
 

Ranman256

Well-known member
Local time
Today, 08:31
Joined
Apr 9, 2015
Messages
4,337
I get this message occasionally, but its really a bad query message.
The query is probably too complex and building large temp data.

Streamline the query into smaller queries or put data into temp tables, then query from that.
 

sportsguy

Finance wiz, Access hack
Local time
Today, 08:31
Joined
Dec 28, 2004
Messages
358
Query is from a single table, 4 fields, i don't think its the query complexity.
was trying to populate a tmp table. . .

any other suggestions?
any registry changes I can make?

Thanks
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:31
Joined
Feb 28, 2001
Messages
26,996
This is a WINDOWS problem, not an ACCESS problem (though Access IS a contributor to the issue.)

In the thread linked below, our member Ridders offers some links to two threads. I contributed but so did other able members of the forum.

https://access-programmers.co.uk/forums/showthread.php?t=298876&highlight=Resources

It is possible that by diddling with your "Max Locks per File" setting, you might be able to work your way around this.
 

sportsguy

Finance wiz, Access hack
Local time
Today, 08:31
Joined
Dec 28, 2004
Messages
358
Thanks!

I had searched on here prior to posting and I didn't see these posts.
I will be able to use something here to fix my issue,
though I am waiting for IS to approve my Azure db to make these issues
go away

sportsguy
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:31
Joined
Sep 12, 2006
Messages
15,613
it could be an erroneous message. It might be a minor database corruption.


You ought to keep occasional backups of tested code, so if you get a problem you can start again from a safe copy. If you get this sort of issue, try this sort of stuff.


1. copy the database, in case you make it worse
2. Compact and repair.
3. compile the database, to make sure there are no code errors

Then try again.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:31
Joined
Feb 28, 2001
Messages
26,996
Dave has a point. We've seen corrupted database do all sorts of bad "stuff" that could be fixed by a simple C & R (but never forget the value of a good Backup.)

It's a variant on the old saying: One whoops cancels a thousand "attaboys"; one backup can cancel a thousand "whoops."
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 08:31
Joined
Apr 27, 2015
Messages
6,280
@SportsGuy,

If by chance you choose to use the Decomplie advice Ridders gave, please report back here on either its success or not.

Very interested on its effectiveness...
 

InstructionWhich7142

Registered User.
Local time
Today, 12:31
Joined
Feb 24, 2010
Messages
199
edit: apologies if I shouldn't have posted this 4 times but it's the answer to all 4 issues, at least in my case and I found I still had these threads open so thought I would share in-case the previous creators are still watching, delete if I'm in error, thanks

I went through this thread and some others, for reasons of ODBC compatibility with Foxpro i'm stuck on 32bit, and Access 2016/office365 has only recently been made Large Address Aware, so you may find your issues have resolved themselves, if you're still on 2010 it turns out this simple utility lets you change it to 4GB without issue, I've been running like that for a year or two on 2010 without issues,

Code:
Large Address Aware.exe

you can check your memory usage with

Code:
vmmap

amusingly I just found this as well

 
Last edited:

isladogs

MVP / VIP
Local time
Today, 12:31
Joined
Jan 14, 2017
Messages
18,186
Thanks for posting (multiple times!)

I'm not going to reply to all four of your messages but your comments are inaccurate.
LAA was added to 32-bit Access 365 last year in version 2310.
It is also included in Access 2016/2019/2021. The only exception is the MSI version of A2016

You missed several other useful links on this subject including:



The third link also includes the utility I use for adding LAA to older versions such as A2010 / A2013
 

InstructionWhich7142

Registered User.
Local time
Today, 12:31
Joined
Feb 24, 2010
Messages
199
Apologies, I know you an Doc would have got the notification a few times but it looked to be a subject everyone was revisiting so I thought for the benefit of the Original posters in each instance I'd share it on the 4 threads I'd delved into at least.

It was only as I was looking for the name of the tool that I came across one post saying it'd been added in an update, which I took with a pinch of salt, anyway to some extent raising people's awareness that this could have been their issue and this is why it may have magically resolved itself last year when office updated could be helpful, also for those still on older versions of office like 2010 etc, I wasn't sure if I should post a link to an EXE directly which is why I omitted that,
 

isladogs

MVP / VIP
Local time
Today, 12:31
Joined
Jan 14, 2017
Messages
18,186
Yes I got notifications for each post....!
Anyway, it would be appreciated if you could correct your comments about LAA not being available in A2016/365.
The DevHut article is also incorrect in that respect.
 

InstructionWhich7142

Registered User.
Local time
Today, 12:31
Joined
Feb 24, 2010
Messages
199
I've amended for relevant versions, apologies for the spam and bad info, I just don't want to be DenverCoder9 if you know the XKCD and as I'd solved my issue I wanted to share back, cheers
 

Users who are viewing this thread

Top Bottom