Not-able-to-print puzzle (1 Viewer)

mcw21j

Registered User.
Local time
Today, 14:26
Joined
Nov 23, 2013
Messages
26
Hello,
I have a local (C:\desktop) Access database which uses an ODBC connection for numerous queries/subreports which ultimately get pulled into a single overall main report. I am able to consistently print preview the main report.

However, when I attempt to print (either to PDF or to hardcopy) I get an error that says “There isn’t enough memory to perform this operation. Close unneeded programs and try the operation again.” SOMETIMES I also a “System resources exceeded” error.

I would think this error to be accurate except that my other PC, which has considerably less memory (16GB vs. 32GB), is able to print the main report.

My IT people cannot find an error with the ODBC connection (“tnsnames.ora” file was updated among other QC checks) nor with the…

configuration of the faulty computer. FYI, the faulty computer matches the configuration of the working PC (e.g., QC’d environmental variable “TNS_ADMIN”, updated software versions, printer configurations, etc).

Has anyone ever seen something like this? I’m a completely puzzled since it works on the lower-powered machine but not on the high-powered machine. My IT people think it’s a database issue which they cannot provide help on which is why I’m hoping your beautiful minds can help me with.

Any help is appreciated.

Thank you,
Mark
 

Ranman256

Well-known member
Local time
Today, 17:26
Joined
Apr 9, 2015
Messages
4,339
this can happen if:
a form has lots of subforms in it,
a query has lots of tables and subqueries in it.
(your memory available be damned)

1.try to simplify the queries

2.on forms, instead of binding every subform to a query, use only ONE subform, then swap out the record source when needed. I use tabs. User clicks the [Payment] tab, then the 1 subform recordsource becomes subFrm.sourceobject = "frmPaymentSub"
 

Micron

AWF VIP
Local time
Today, 17:26
Joined
Oct 20, 2018
Messages
3,476

isladogs

MVP / VIP
Local time
Today, 21:26
Joined
Jan 14, 2017
Messages
18,186

isladogs

MVP / VIP
Local time
Today, 21:26
Joined
Jan 14, 2017
Messages
18,186
Ah but those were the 2 links I gave in the post I referenced ;)
 

mcw21j

Registered User.
Local time
Today, 14:26
Joined
Nov 23, 2013
Messages
26
Thanks to all who replied so far. Great suggestions! I will need to look through these links in detail and run some tests. Will update when i've succeeded (or gotten tired of banging my head against the wall).
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:26
Joined
Sep 12, 2006
Messages
15,614
Sometimes its genuine. A lot of records and a complex query. One solution is to "stage" the process. Write temporary data to a table, and continue the process with the table. That might work.
 

mcw21j

Registered User.
Local time
Today, 14:26
Joined
Nov 23, 2013
Messages
26
Thanks again for these suggestions. I am considering doing a redesign as a worst-case fix. I am still hopeful that I can find some tweak that will get this to work (I need IT Admin rights to do registry tweaks). I am just so puzzled because it prints correctly on 2 other lower-powered machines, which seem to be configured the same.
 

isladogs

MVP / VIP
Local time
Today, 21:26
Joined
Jan 14, 2017
Messages
18,186
Thanks again for these suggestions. I am considering doing a redesign as a worst-case fix. I am still hopeful that I can find some tweak that will get this to work (I need IT Admin rights to do registry tweaks). I am just so puzzled because it prints correctly on 2 other lower-powered machines, which seem to be configured the same.

If you are able to run Access as an administrator, you can do changes to the HKLM hive using VBA. However, I would still suggest you ask permission first.

Or you can just increase the MaxLocksPerFile setting for that session only using VBA. No registry changes involved.
 

mcw21j

Registered User.
Local time
Today, 14:26
Joined
Nov 23, 2013
Messages
26
I went with the MaxLocksPerFile-using-VBA suggestion in the On Load event of the initial window the user sees.

Code:
DBEngine.SetOption dbMaxLocksPerFile, 15000

The code compiles fine but i'm not sure if it actually changes the MaxLocksPerFile setting. Still get the same errors when trying to print. Is there a way to monitor the MaxLocksPerFile setting to see if it changes when the VBA executes?

Should I be using the code somewhere else?
 

isladogs

MVP / VIP
Local time
Today, 21:26
Joined
Jan 14, 2017
Messages
18,186
I think you mean its in the Form_Load event of your startup form.
That should be fine or you could do it just before running the problem code
However, the code should be
Code:
[B]DAO.[/B]DBEngine.SetOption dbMaxLocksPerFile, 15000
Doing this will increase the value until DBEngine is closed
https://support.microsoft.com/en-nz/help/815281/file-sharing-lock-count-exceeded-error-message-during-large-transactio

You could try increasing a bit more in slow stages....
Of course the issue may be unrelated to max locks per file :(
 
Last edited:

mcw21j

Registered User.
Local time
Today, 14:26
Joined
Nov 23, 2013
Messages
26
Thanks isladogs!
Yes, you are correct. And I will edit the code and give that a try.
 

mcw21j

Registered User.
Local time
Today, 14:26
Joined
Nov 23, 2013
Messages
26
Hello everyone.
Just wanted to update this post and thank you all again for the advice.

Unfortunately, none of it worked to save my original database design. I had to take the nuclear option and break up the database. I was able to isolate the problem to be subreports. The underlying queries worked fine so i'm still at a loss to explain the reason for this problem. Anyway, I cut out the problem subreports and ported them into a completely separate database that mimicked the original design. Not pretty but functional and useable. My users are just happy to get their reports out even though it takes a few more steps.

Thanks again to all of you and for this great resource.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:26
Joined
Feb 28, 2001
Messages
27,001
The problem with "Resources" is that it is a Windows problem first and an Access problem second. A "Windows Resource" is actually reflective of some type of graphical element (or potentially graphical element). What consumes resources? Damn near anything. But it is a matter of what is installed on the computer, AND THEN what the computer is trying to do with what is left.

To see that a putatively smaller computer has less trouble with the report that a bigger computer cannot run tells me that the smaller computer has less other stuff installed than the bigger computer. But there is another factor to be considered. You had to "break up" the database into parts. By any chance was the bigger database one for which many obsolete reports had been designed and abandoned (rather than deleted)? Because when the App gets loaded, each one of those extant reports (and forms, BTW) potentially consumes a Windows Resource (and for sub-report cases, more than one.) So cleaning out the main DB might have helped. By transferring specific reports to the new DB but NOT transferring all of the other reports, you implemented the effect of cleaning out the DB of old resource eaters.
 

mcw21j

Registered User.
Local time
Today, 14:26
Joined
Nov 23, 2013
Messages
26
That's very possible The_Doc_Man. The "bigger" PC wherein the issue emerged, was configured by our IT department. The old, "smaller" PC was also configured by IT but 5 years ago. With problems of security breaches, especially at a state-run office like mine, i imagine IT installed a lot of other software that was not installed 5 years ago.

Your 2nd point about cleaning out resource hogs is also a viable explanation. The subreports I determined to be the problem were based on very specific query criteria. For the most part, those criteria were never met BUT in the original DB, those queries needed to activate first before it could determine that the criteria were not met. Pulling those out and plopping them into something new, yes, I suspect could have cleaned out the resource hogs allowing the 2 individual DBs to each run successfully.
 

Users who are viewing this thread

Top Bottom