Access subreports do not always show graphs (in .jpg format)

Posted on 2012-03-22
Last Modified: 2012-11-04
I have a report that includes a large number of subreports.  Each subreport can include up to 10 graphs.  Each graph is referenced through an OLE control where the Picture property field specifies a .jpg file at a network location mapped as a drive (for example, "H:\WorkingFiles\NIMTchart.jpg")

When the report is run some graphs do not display.  If I open the subreports directly, all graphs are correctly displayed.

It's as if, when the report is run, and a subreport is opened for inclusion in the report, there is only a limited amount of time allocated to each subreport page.  If not all graphs on a page can be generated in the time available then maybe the last one gets left out of the report.  A typical case has a report page showing three out of four graphs, with the bottom right hand graph missing.

The missing graphs will not be the same every time the report is opened.

I have just converted my database to Access 2010 (.accdb) from Access 2003.  This report ran without problems under Access 2003.

What can I do about this?  Help much appreciated!
Question by:tonewell
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5

Author Comment

ID: 37755618
Problem also occurs when Access 2010 is used to run the database in .mdb format.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37755742
<I have a report that includes a large number of subreports.  Each subreport can include up to 10 graphs.  Each graph is referenced through an OLE control where the Picture property field specifies a .jpg file at a network location mapped as a drive (for example, "H:\WorkingFiles\NIMTchart.jpg")>
I'm confused, ...are you displaying Graphs, or images of graphs, ...or both, ...or neither?

The first thing I would do is make *Sure* you are only opening this report in "Print Preview" explicitly.
Many report elements are not rendered accurately in "Report View" (which is the default)


Author Comment

ID: 37763995
Clarification: I meant images of graphs.

Yes, the problem occurs when opening the report in "Print Preview".  It also occurs when the report is output as a .pdf file under vba control.

(I have tried opening the report in "Report View" ... that produces a whole different kind of mess; but that is not what I am concerned about.)

Any other ideas?
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


Author Comment

ID: 37764289
It appears that the problem is that Access is not handling LAN/WAN latency well.  I have moved my "working directory" to my C: drive; doing so seems to solve the problem.

Do the experts have a better answer?

Author Comment

ID: 37764334
I spoke too soon.  Further investigation shows that the problem is NOT solved by moving my "working directory" to my C: drive.  Thus LAN/WAN latency is not the core issue here.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37766058
My thoughts:
This, to me, has to do with the level of complexity here:
a. Subreports
b. A "large" number of subreports
c. Images in a "large" number of subreports
d. Multiple images in a "large" number of subreports
e. Multiple OLE images in a "large" number of subreports
(Also compounding this issue will be the size of the images)

Access report were simply never intended to work like this.
In Print preview, Access must fully "render" *every single image on the report*
So going into each subreport then going into each image in that subreport will use a *lot* of system resources.

This is why "Report View" is even worse, as it seeks to just show accurate raw data.
Report view gives relatively little emphasis on "graphical" elements...
(It also ignore most common vba code, and does not show page breaks, ...etc)

You can try running this report on a machine with more memory...
Or try simplify the report.

Does this report really require *Every* main Report Record?
Try filtering the report for fewer records...
For example:
One Customer, Many Orders, One order with many products, ...and a picture for each product.
Does this really need to be a report for *Every* customer?
I would try to create a filter to select one customer at a time, for the report,
or go further and just filter for one Order.


Author Comment

ID: 37825990
Thanks for your thoughts Jeff.  I have not abandoned this question; just had other things to do for a awhile.

Access reports no doubt were never intended to be used the way that this one is used.  However, this report worked perfectly well for years under Access 2003.  The problems started only on conversion to Access 2010.

I tested this application under Access 2010 a couple of months ago, and passed it: I did not notice any dropped graph-images back then.  But back then I was using an older, slower machine with less memory (2GB compared to 3GB currently).  

Now note that the report in question has 44 pages and well over 100 graph- and table-images (jpgs).  Under Access 2010 this report loads in less than 10 seconds; if the "go to last page" arrow is then clicked, it takes just 5 seconds to show this.  I do not have comparable times available for this report under Access 2003, but I think that it ran much slower under that version of Access.

My conclusion: the report is running too fast; whatever software is retrieving all those jpg's is not keeping up.  I'm thinking that maybe I can fix this problem by slowing computer down during the report generation process.  Do you (or other experts) know of a way to do this?  Redesigning the application to work a different way is not an option.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37826311
Any code on this report...?

You may be able to workaround this issue by using a few strategically placed "DoEvents" in the code...

Author Comment

ID: 37858963
This problem is still not solved.

I had already tried placing "DoEvents" commands in the subreports displaying the problem, but this did not help.  (Unfortunately the problem seems to occur towards the end of pages with 3 or 4 images on them - and it is not possible to tell Access to slow down towards each page-end.)

In a previous post I speculated that the problem may be caused by the system not keeping up the supply of linked jpg's from disk or network.  I have since tried running my report with subreports using embedded images.  The problem persists regardless of whether images are linked or embedded.

I am not sure what else to try.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37863381
You can try using the "attachment" datatype in Access 2007 and newer format databases...

You can also try exporting the report to a PDF, then viewing the PDF

Finally try removing any "Page x of y" controls in the page footer.

Author Comment

ID: 37945141
I have had to put this question aside for a time.  The problem is still not resolved.  

I have tried exporting the report to a PDF; this makes no difference, the problem persists.

I have not yet tried the other two suggestions in Jeff's last comment.  My company will soon upgrade from Windows XP to Windows 7; before taking further action I want to see if this problem disappears under Windows 7.  

EE may wish to leave this question open until I have something further to report.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37950149
Following the last post, you have 21 days until the system starts alerting you of an Open question.

I am certainly in no rush, I too want to see what resolves this issue...



Accepted Solution

tonewell earned 0 total points
ID: 38551878
Our company has since upgraded to Windows 7.  So far the problem has not recurred.

Author Closing Comment

ID: 38564958
The problem was not solved any other way

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

630 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