Solved

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

Posted on 2012-03-22
14
438 Views
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!
0
Comment
Question by:tonewell
  • 9
  • 5
14 Comments
 

Author Comment

by:tonewell
Comment Utility
Problem also occurs when Access 2010 is used to run the database in .mdb format.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<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)

JeffCoachman
0
 

Author Comment

by:tonewell
Comment Utility
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?
0
 

Author Comment

by:tonewell
Comment Utility
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?
0
 

Author Comment

by:tonewell
Comment Utility
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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.

JeffCoachman
0
 

Author Comment

by:tonewell
Comment Utility
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Any code on this report...?

You may be able to workaround this issue by using a few strategically placed "DoEvents" in the code...
http://support.microsoft.com/kb/118468
0
 

Author Comment

by:tonewell
Comment Utility
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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.
0
 

Author Comment

by:tonewell
Comment Utility
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.
0
 
LVL 74

Expert Comment

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

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

;-)

Jeff
0
 

Accepted Solution

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

Author Closing Comment

by:tonewell
Comment Utility
The problem was not solved any other way
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 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

11 Experts available now in Live!

Get 1:1 Help Now