Solved

Linked Image in Report Causes HUGE System Memory Usage with Access 2010 / WORKS IN 2003

Posted on 2011-09-13
24
725 Views
Last Modified: 2012-08-14
Hello Everyone,

Access just threw me a huge curve ball.  What's new, right?  I've been converting a 2003 database to a 2007 database using the 2010 version of Access.  Everything has been going great, until I started working on some reports with links to images I use to underlay the report.  Basically, I have a large linked image, 7.5"x9.5" let's say, of a form that I then overlay with report data.  I've been doing this forever and it has never been an issue.  The average image size is about 1mb and I have two of them to a report, the front and back of the page.  Simple stuff, right?

Everything worked as normal until I went to Print Preview.  Only one of the pictures showed up in Preview.  Thinking I just missed something, I checked it all over and it looked good.  Tried again and the same result.  After tinkering around a bit, I started thinking maybe it was a memory problem.  I closed the app, reopened and tried again.  Same result, but this time I checked the Task Manager and bam!  When opening the report, Access's memory usage jumped from 70mb to over 450mb!  It stayed that way until I closed the report.  Sometimes it would show one picture, sometimes none and it would never actually print.  Remember, this was the result of adding 2 b&w, text images at about 1mb a piece.  Insane!

Ok, so I started playing with picture size.  After compressing them down gradually, the memory usage also went down.  Once I got down to about 200kb, things went back to normal, albeit a crappy compressed, unusable image.  This still doesn't explain why it is happening in the first place.  Doing the exact same thing in 2003, the same report even, only bumps the memory up a tad.  About the amount of the picture size, which makes sense.  Something has to completely out of whack with 2010.  I noticed a couple other references to 2007 in other posts, so this must have started with that.

To further my research, I tested this on a couple different machines & operating systems and got the same result.  Because Access, in my experience, can have sporadic results going from print driver to print driver, I also tested it using different default printers.  Some made a difference, but it was still huge memory jump.

I can't be the only one who uses image backgrounds/underlays/overlays like this, so I can't believe I can't find more references to this problem.  The couple I did find didn't provide any solutions.  Based on my tests with multiple machines, it can't just be me.

Any thoughts would be appreciated.

Thanks!

Tom

I should add that doing this same thing in Access 2003, I have some linked images as big as 5-6mb and that is no problem either.
0
Comment
Question by:tommyboy115
  • 14
  • 6
  • 4
24 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
First question:
Does this same issue crop up when you open the report in "Report View" explictly?
Is the database compacted and the code compiled on a regular basis?
(Just curious)


As far as Print Preview in concerned...
The ability of Access to display images has a lot to do with how complex the report is.

Print Preview uses more resources than Report View because it actually renders everything on the report to give you a near 100% faithful representation of how the report will look when printed.

So if you have an image on a report with 150 controls, Comboboxes, Active-x controls, Graphs, Colors, Calculated controls, Aggregate functions, Multiple grouping levels, subreports, conditional formatting, VBA Code (ex. Recordsets), ...all for a report that is 100 pages long, ... then, yes, Access will have trouble rendering this report.
;-)

So first try simplifying a copy of the report.

Then try "embedding" the image.
(Might sound strange, but sometimes embedding the image will improve performance because Access does not have to "Go Out" and get the image.  Also note that while an image may be linked, Access still creates an "internal" bitmap of the image so it can display it)

Try this:
Download a copy of the Northwind sample Database and open every form and report at the same time.
For this, (Note, I am using Acc2007) I could not get the Access process to use much more than about 100mb.

I then inserted a 3mb Bitmap on the Alphabetical products list report, and even then, the mem usages was not much more than about 107mb, so something else may be at play here in your report...


What "Format" is the DB in? (.mdb or .accdb)?

As far as actually testing this in an access 2010 format DB, I will try this tommorrow at work and report back)

Perhaps I missed something, so let's see what other experts say...

JeffCoachman
0
 

Author Comment

by:tommyboy115
Comment Utility
The reports I'm doing this with are very simple.  Just a bunch of textboxes.  Think of a W9 tax form as the image and the only controls are for the form fields, like name, ssn, etc, on top.  Embedding was the same result.

I agree that rendering for a Print Preview is more resource intensive, but as I mentioned, it works fine in previous versions.  It's one of the biggest things I use Access for and I'm pretty accustomed to what normally happens.

I downloaded the Northwinds Database and got the same result as you.  It seems to be exponential though.  The Northwinds database, when just open uses about 27-30mb of memory.  Adding a picture of the same size you did took it to the 100-105mb of usage, which seems like a lot.  My database is around 70mb.  Adding two pictures gave it the huge jump.

Opening my original database in 2003, I go from the 70mb to about 95mb of memory usage when opening the exact same report.  The images I'm using are B&W text, not pictures or anything crazy.  These are reports I just imported into 2010.  Nothing is different.
0
 

Author Comment

by:tommyboy115
Comment Utility
Also, your first questions:

1. Report View is not an issue and it works fine, though I don't use it.
2. Yes, it is compacted and compiled a lot.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
OK,

Again, I'll test this at work tomorrow...
0
 

Author Comment

by:tommyboy115
Comment Utility
So we're on the same page, I created a blank database and imported one of my reports.  I changed the image links to the root of the C:\ drive, so you can just drop them there and they should link properly.

When I open the database, it uses just under 11mb of memory while idle.  When I open the report, with nothing in it but the images (no controls, queries, code, etc.), it jumps up to anywhere from 150mb to 450mb of memory usage.  Sometimes it shows both images, sometimes one, and sometimes none.  Make sure you switch pages to see the second increase in memory used.

Let me know what happens for you.
TITLE-01.bmp
TITLE-02.bmp
Database1.accdb
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility

 First thing I'd do is make sure your on SP1.  A number of issues has been addressed.

Jim.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
FWIW...

I tested your DB and I got similar results in Acc2010

1.
For Page 1 you are using an Unbound object to display the image.
For Page 2 you are using an Image control
Why the two different controls? ...just curious...

2. I have seen this technique of having an "Image" to simulate a paper form.
Can I ask, why not just use controls for this?
I tried this once and it was just a nightmare managing what objects to "Bring To Front" and "Send to back" while designing the report.
The advantage to using controls is that if you do eventually make this a form:
1. The controls are already in place
2. The issues you are currently having should go away.
Sure it took me some time, but in the end, the Report was much more complex than yours with no "Image/Resource" issues at all.

I just really don't like inserting "Big" images into a report unless it is absolutely necessary.
(to that end, you could try converting the image to .jpg, ..perhaps...?)

I could be off base here, or not understanding something, so let's see what Jim posts....

Jeff

0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<I could be off base here, or not understanding something, so let's see what Jim posts....>>

  Sorry, but no ideas here...

Jim.
0
 

Author Comment

by:tommyboy115
Comment Utility
In regards to your questions Jeff,

1. No reason I can remember or think of why.  Probably by accident.  I made this a while ago.  I changed to an image control and it didn't help.

2. I use so many of these, I can't even imagine the complexity of duplicating them.  And I have done that before for some.  While this one is fairly simple, many are complex forms and I have no access to logos, graphics, fonts, etc., to build them, other than the original PDF I get.  Many of these have to be exact because they are legal forms.  There are a lot of reasons for doing this.  And it's just easier.  You insert an image send it to the back, put the control boxes on top, and you're done.  When there is a change to the original, you just swap it out.

The samples are bmp because of old Access, but I've tried different image types, like jpg, png, gif, etc., and I get this same result.

In regards to your Question Jim,

I am using SP1.

0
 

Author Comment

by:tommyboy115
Comment Utility
I don't have Access 2007, could anyone confirm this happens in that version as well?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Using two Image controls, in 2007 I got similar mem usage, both images did display in Print Preview, but only Image one displayed when actually Printed out.

In 2010 only Image One displayed in Print Preview, and only image one printed...
But again remember that "Your Mileage may vary".

I'll try this at home tonight on acc2003 though...

Other options:
If this is just a "Form" for users to fill out, then simply try this in Word, Excel, Powerpoint, or Publisher.
...Or even just print out the image(s) themselves as files directly, ...or possibly save the images as PDF's...?
0
 

Author Comment

by:tommyboy115
Comment Utility
The data entry happens in other parts of the database and this is one of many forms that use the information to print a package of documents that are all printed at once.  PDF's, other programs, etc., aren't an option.  What you're going to find with Access 2003, or earlier, is that this will work without an issue, like it has for me for the last decade.  That's what makes this so frustrating.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:tommyboy115
Comment Utility
sorry:

...and this is one of many REPORTS that use the information...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Yes, when saved as 2002-2003 (.mdb), both pages displayed and printed fine...

It is beyond me to speculate on why this works fine in 2002-2003 and not in 2007/2010

You can take some time and play around with:
Disabling Layout view in the Report and in the Access options. (and Report View)
Set "Print Preview" as the default view in the report.
Set the HasModule property to No...

Beyond just wild guesses, I'm just shooting blanks here...
:-(

JeffCoachman
0
 

Author Comment

by:tommyboy115
Comment Utility
To me, it seems like a bug.  Are you, or is anyone else, familiar with the best way to bring this to Microsoft's attention?  Or, based on your experience, will they even do anything about it in a decent time frame?

Also, is it possible for you to flag this to other Expert Geniuses in case someone else has encountered this but didn't see my post?
0
 

Author Comment

by:tommyboy115
Comment Utility
One other thing.  Are you familiar with any alternate image controls that are designed to work in a report?  Most of the ones I've seen seem form focused.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
I only ever use "Image" controls.

FMSInc has one, but it is mainly for "Animation"
http://www.fmsinc.com/MicrosoftAccess/controls/components/animation/index.html
...but who knows, ...?

There may be other "Image" controls out there that you can try, but beware that many are not specifically designed too work with MS Access, ...so be careful.

Also note that using an external control will require you to install it on *Every* machine that will access the db...


Jeff
0
 

Author Comment

by:tommyboy115
Comment Utility
I've seen quite a few posts that reference DBPix.  I've downloaded the demo and it seems to work, though I'll have to test it more.  I can say that after re-creating the sample database I uploaded with this tool, it previewed and printed correctly.  I just HATE using ActiveX controls and it really p***es me off that this won't work natively in 2010.

Any thoughts on the best way to report this to Microsoft?  Or is it a joke to even try?
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<DBPix>>

 That's one of the Access favorites.  DatabaseMX has used it quite a bit I believe.

<<Any thoughts on the best way to report this to Microsoft?  Or is it a joke to even try? >>

 After we've had other Expert's look in, I'll buck it up the chain.

Jim.
0
 

Author Comment

by:tommyboy115
Comment Utility
Thanks guys!
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility

 Just a forewarning though; depending on the severity of the error and possible impact, we may get anything from no response, to "that's the way it works", to "fix will be in next service pack, to "try this hot fix".

 But no matter what, it's not going to be quick.  I would continue to explore DBPix.

Jim.
0
 

Author Comment

by:tommyboy115
Comment Utility
I figured as much and am well on my way.   I'd love to drop the overhead in the future though.  Thanks.
0
 

Accepted Solution

by:
tommyboy115 earned 0 total points
Comment Utility
I found that if the width of the image did not exceed 2500 pixels, things work correctly.
0
 

Author Closing Comment

by:tommyboy115
Comment Utility
No else came up with anything.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now