Charting in access, any way to avoid the 1990's built in garbage?

[g4tv4life]

Hey everyone, I have the unenviable task of creating automatic charting in access. I went to go and try out the built in system


and saw its was trapped in the 90's. The fact that Microsoft hasn't updated that is appalling. Anyways, I'm trying to create a custom
Chart that is a little complicated and was wondering if there was a decent add in or something to build better charts.

First off, I'm trying to create a line chart using yes and no values. The idea is that monthly, a place either gets a yes or a no for testing. If the 1st month
they get a yes, January is at 100%. If they miss the next month, drops to 50%.

Really, I could put up with the charts if I could just change the background image, is there any way programically to change it?

any suggestions for either making custom charts? I'd prefer to keep everything inside access and not have to resort to exporting the data out into
say excel.

thanks guys

[June7]

Charts require records. I assume you have records that can be manipulated to calculate the percentages?

Don't know what you mean by 'background image'.

I am not aware of any add-ins for Access charts. Did you web search?

[g4tv4life]

Charts require records. I assume you have records that can be manipulated to calculate the percentages?

Don't know what you mean by 'background image'.

I am not aware of any add-ins for Access charts. Did you web search?

I'm working on the code now to create the percentages

as for the background Image, I mean instead of the hideous charting, I would build a .jpg that looks cleaner and more modern, then impose the charting lines onto it.
I'm guessing if I were to do something like this I would have to figure out the vba to create the points.

As for add-ins,no I could not find anything in my web searches, which is sad as the charting system is beyond pathetic and antiquated.

[June7]

The chart can be transparent and gridlines can be turned off.

MS doesn't improve Access charting because of Excel. They think you should use Excel if Access chart is not adequate. Yeah, sucks! But Access isn't really supposed to be a statistical analysis tool, that's Excel.

However, I have been able to get Access to accommodate all my graphing requirements with the help of VBA in some cases. My requirements probably aren't very demanding although didn't seem that way when I was building.

[g4tv4life]

Yeah, I'm trying to keep it a one-stop-shop kinda program. Were so fragmented here that I'm condensing it all down to one database with a lot functions in it.
Plus, my eventual goal is to create these charts and email them out as I did with the form letter system you helped me with last week.

Back on topic, I can't believe I didn't think to make the thing transparent! That totally solves most of my problem as I can just use a picture frame as the background
with a watermark in it to make it look more official. I really should lay off the programming past 3, as my brain is pretty gone by then.

thanks for the idea june, when I run into problems (and I will) illl come back and ask for some help on turning the yes/no's into percentages.

[ipisors]

I agree, automating charts in Access is hideous. Excel is the right tool for the job.

But can you get away with automating excel charts in the background (invisibly), then simply returning final chart images to an Access screen image control?

I do that all the time. My access code starts up excel..(pre designed template, or from scratch w/code)...does the data dumping/manipulation...creates charts..adjusts trend lines, etc., all using the excel OM of course.
then use the excel OM's Chart.Export which creates a beautiful picture in a split second...then make your Access form's image control's .Picture to be that path.

As long as the machine that will run this code has Excel, of course.

[g4tv4life]

I had stumbled across that solution earlier but couldn't find any real help on getting it to work.
Any computer that uses access here definitely has excel as well so that's not a problem.
Do you have any material or a link on how to go about that? One reason I'm extremely interested in that
is that I need to also email out these charts which I hope to do automatically. Is there a way to store the image path into a field for later use as well? Thanks

I agree, automating charts in Access is hideous. Excel is the right tool for the job.

But can you get away with automating excel charts in the background (invisibly), then simply returning final chart images to an Access screen image control?

I do that all the time. My access code starts up excel..(pre designed template, or from scratch w/code)...does the data dumping/manipulation...creates charts..adjusts trend lines, etc., all using the excel OM of course.
then use the excel OM's Chart.Export which creates a beautiful picture in a split second...then make your Access form's image control's .Picture to be that path.

As long as the machine that will run this code has Excel, of course.

[ipisors]

That's an extremely broad question, so...I"m not sure where to start. I mean if you are new to VBA, this is not a good 1st project for you...it will not be simple.

A few really broad questions to narrow you down I guess -

1) are they all the same type of charts? (pivot, pie, bar etc)
2) can you start with excel and save a template on the network, using your vba automation from access only to replace the data in that excel workbook?

Kind of hard to answer that if you have no ideas at all how to get started...I mean you'll need to code opening excel, opening a workbook, presumably replacing data on a worksheet (depending on your answer to #2), then you have more code for Outlook automation, etc.

You may want to decide on an approach and then post your questions as they come up one by one on specific coding topics. (assuming, of course, that you make an effort on your end as well).

[g4tv4life]

That's a fair point. I'll dig more into that option and come back for code questions if I have them. Hows the performance on it? how long does it take a average computer to build the chart in excel and bring it back? My database is split and runs on a virtual network drive (3mb/s, slower than a usb 1.0 flashdrive) Do you think that would cause much issue with performance?

[ipisors]

That's a fair point. I'll dig more into that option and come back for code questions if I have them. Hows the performance on it? how long does it take a average computer to build the chart in excel and bring it back? My database is split and runs on a virtual network drive (3mb/s, slower than a usb 1.0 flashdrive) Do you think that would cause much issue with performance?

Wouldn't it depend on how much data there is.....how you're extracting the data from Access in the first place...etc?

I have some that run in a few seconds.

[g4tv4life]

again, fair point. Thats not bad at all really. I'll look into it some more.
I suppose I'm just a masocist because I always seem to take on these complicated things all
the time with this database. Even creating form letters and emailing them through lotus notes
(A lot of headaches)