Solved

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

Posted on 2012-03-22
14
464 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
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

816 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

8 Experts available now in Live!

Get 1:1 Help Now