Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2012-03-22
14
Medium Priority
?
505 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
[X]
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
14 Comments
 

Author Comment

by:tonewell
ID: 37755618
Problem also occurs when Access 2010 is used to run the database in .mdb format.
0
 
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)

JeffCoachman
0
 

Author Comment

by:tonewell
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?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

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

Author Comment

by:tonewell
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.
0
 
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.

JeffCoachman
0
 

Author Comment

by:tonewell
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.
0
 
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...
http://support.microsoft.com/kb/118468
0
 

Author Comment

by:tonewell
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.
0
 
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.
0
 

Author Comment

by:tonewell
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.
0
 
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, ...as I too want to see what resolves this issue...

;-)

Jeff
0
 

Accepted Solution

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

Author Closing Comment

by:tonewell
ID: 38564958
The problem was not solved any other way
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

715 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