Report Glitching Upon Opening (1 Viewer)

Hello,

I have a report that has two charts on it. The report itself has no record source. The charts are created using the wizard from two separate queries. Sometimes, when the report is opened, via a button, the report will "glitch out" for several seconds. I would say around 60 - 90 seconds. The mouse wheel just sits and spins and the report cannot be closed until it has loaded. It will load eventually, but I cannot allow this problem to continue on a production database. Does anyone have any ideas or suggestions that I can try? I only use VBA programming so there are no macros present. This problem is not consistent. It seems to be intermittent. This occurs on the majority of the reports in the database that have charts. It doesn't seem to matter if the report has one chart or two.

Thank you for any and all help.

I don't know the cause of your problem but if you attach a zipped copy of the db to a post here I'd be happy to try to help.

Never encountered this so no idea. Without examining data, design, code cannot advise.

Glitch out? Is the only symptom the dreaded mouse cursor's "spinning whirligig" for a while?

You might (stress MIGHT) consider trying to open this while you have the Windows Task Manager loaded up and preset to show I/O and CPU time. Then click on task manager column header to cause it to show top CPU users first, then open your report. Then try it again with top I/O users selected. Finally, one more try with top paging use. What you would be trying to do is to decide where your bottleneck sits. If it is CPU bound or I/O bound, you would expect MSACCESS.EXE to bounce up to the top of the list, but you would like to know why.

Here's another question, though: If you are driving charts from queries, do the tables have indexes on the fields in those queries on which you are doing sorts?

abandin access graph it a sore to the eyes. use excel chart, import it as image abd out the image to your report. much pleasing for presentation.

I have a db with numerous graphs and never had issues with their presentation, never sore to the eyes.

Agree with June.

I have many charts in Access, some simple and others complex. All work well.
None of them have a time lag.
Doing the charts direct in Access means the data is 'live' whereas importing as an image clearly isn't

It is true that Excel charts have more options/types. However I've never needed any of those types to present my Access data effectively.

look carefully at your graph, jagged text, jagged lines...

Don't know what you mean by 'jagged text, jagged lines'. As I said, never any issue with presentation.

Ditto. I've used Access charts without problems of that type for over 15 years.

BUT don't take my word for it.
Have a look at the (fairly extreme) line charts DEMO by Chris Old to see what is possible in Access.
a) Original version in A2003 format at https://www.access-programmers.co.uk/forums/showthread.php?t=189196
b) Updated version for A2016 at https://www.access-programmers.co.uk/forums/showthread.php?t=299247
Much more complicated than a typical chart with lots of code that isn't needed normally

I'd have to agree with arnelgp on this one.
Excel charts seem better to me.

Here is the CERT data in Access and Excel in charts.

Gasman said:

I'd have to agree with arnelgp on this one.
Excel charts seem better to me.

Here is the CERT data in Access and Excel in charts.

Click to expand...

I CHOSE to make the Access charts smoothed to show 'possible trends' rather than a series of straight connecting lines. I did this as I thought it unlikely people would update every day or at regular intervals.

Again I chose to have a thicker line and show the data point

Removing the smoothing but keeping the points / line thickness, you get the attached.
One section is jagged by that method. Which version is better?

Colin,
I still think Excel in this instance.
I created a chart a good while back when working just to see what was produced and that was quite chunky I seem to recall.?

Now that post https://www.access-programmers.co.uk/forums/showthread.php?t=299247 does show a much smarter/neater graph.

Fair enough .... though I actually meant which looks better - smoothed or unsmoothed?
Anyway no matter.

If I was going to publish my charts for a glossy brochure or a presentation to clients I might indeed use the more powerful charting features of Excel.
However, IMO, for everyday 'live data' use, I don't think its necessary

TBH a line graph/scatterplot is incorrect in this case because the points aren't really linked. If the chart was restricted to one currency, a column chart would work well.
However as the app also allows you to plot multiple currencies (as attached), I decided bar charts wouldn't work. So the decision was a compromise in this case.

I have plenty of better looking charts than that one

I'd be happy with the Access chart TBH, as it will still show the trend whatever. However I find management generally like to see something prettier. Less functional, that is not a problem, but prettier is better.

@The Doc Man - Thank you, sir. I'll give this a shot tonight. I think my hardware could be posing an issue, as it is quite old. It is a 2006 or 2007 model Toshiba with only 3 GB of RAM and I am running Access, Excel, and the VBA editor simultaneously. I usually have notepad open, as well. I will attempt to perform this tonight and reply back.

@The Doc Man - So I took your advice and fired up the task manager and went to the performance tab and viewed memory and CPU usage during the opening of the reports. During normal working conditions with Access, Excel, and the VBA window open, my memory usage was 40 - 42%. The CPU usage usually stayed below 15% until any action was taken. Upon opening a report the CPU usage would spike into the high 60s/low 70s, then immediately drop back down below 15% once the report opened and loaded. When a report opened and the "glitching" occurred, CPU usage would spike into the low to mid 70s, sometimes low 80s, and then only drop to the low 50s where it stayed until the report finally loaded both charts, then immediately dropped back below 15%. There was a time when I had Access open and was in the processing of opening Excel where CPU usage was 96%.

@The Doc Man - I also viewed the Processes tab and MSACCESS.EXE did show up at the top during the glitching event. It showed 48% CPU usage and 50,000 - 52,000 KB of memory being used during this event.

A question I forgot to ask: How many CPUs or cores does your system have? For a system from that year, it might be that you only have 2 active threads (whether 2 single-core CPUs or one dual-core CPU). Without knowing the specific Toshiba model, I can't tell.

If that IS a dual-core system, then a reading of 50% indicates one of the threads is saturated (because task manager corrects the percentages for the number of cores.) In that case, the 60-70% situation is that Access is eating CPU time and MSGRAPH.EXE is also eating time since the way they work, they have to exchange data. And that 96% is a saturated system.

It has been a while since I played with this, but the saturation occurs because MSGRAPH and its partner have to establish a channel between them and do a no-network socket operation to pass data from the data source to the graphics package. A no-network socket is an I/O channel with no physical device because it is data between two tasks in the same physical memory, but they don't have the ability to declare a shared area between them (akin to a FORTRAN COMMON array). So they alternately load up and unload data buffers using socket I/O processing (or something close to that).

To know that MSACCESS.EXE tops the process list is not a surprise. Do you see a live copy of MSGRAPH.EXE floating around at the time? And if you do, you would also see a CPU usage on it that is different from MSACCESS.EXE usage.

My question about indexes becomes more significant. When you are making this graph using Access, are the source tables indexed on the field that is your X axis? If not, they should be.

Arnel, hate to tell you this, but something else is at work. When you make a chart using EITHER Excel or Access, you are using MSGRAPH.EXE (the same image regardless of what is driving it). In theory there is NO DIFFERENCE between the things you can do from either front-end. It's the same graphics builder. I would expect Access-origin and Excel-origin graphs to have similar characteristics if you built them right.

The only difference is in the ease of use of the interface. And there, I would grant you that Access is a little bit clunkier than Excel. There IS the other issue - that depending on how you display the chart in Access, it might be adjusted to a granularity of twips. But if it is a truly embedded chart, it should be naturally smooth. And of course, the difference is that Excel doesn't use twips for everything.

DecafPlease said:

.. Sometimes, when the report is opened, via a button, the report will "glitch out" for several seconds. I would say around 60 - 90 seconds.

Click to expand...

What if you place the graph on a form, does it take the same amount of time to open?

DecafPlease said:

..
This occurs on the majority of the reports in the database that have charts. It doesn't seem to matter if the report has one chart or two.
..

Click to expand...

How long does it take to open the report "JHBReport" in the attached database, (by me it open immediately)?

It might be interesting to see one of your simple databases where it takes a long time to open a graph, (only for testing how long it takes by us to open the graph)!