MS Access – Improved Charting

Here’s a demo I’ve been wanting to share for years and finally got around to it!

Charting has always been one of Access’ greatest weaknesses in my opinion. When compared to the ease of charting in Excel, Access has been lagging behind for years/decades even. I find the dialogs confusing, and understanding how data series relate to my selected table/fields not to be obvious. Then add to that the fact that Microsoft added new charting capabilities, but not all versions have it… it just a nightmare to manage (open the new charts in older versions and get a blank area!).

What the heck is East, West,North, 1st Qtr, 2nd Qtr, … and how is that relating to my selected data?! Where are these color originating from? … And then double-click on the chart in design view and things get more confusing! A Datasheet with values, God knows what they’re all about … Nothing like computers simplifying our lives!!! LOL.

When I see this, I have to say I’m at a loss! What are 1,2,3,4 and A,B,C Where is this coming from? How does it relate to my actual data that I selected (Month and Quantity)? What is this #@!! (feel free to add a few more choice words here)?

Ever needed to color the color of individual lines, bars, pie slices? Have fun in Access!

There has to be a better way that makes some semblance of sense!

So What’s The Solution Then

So forget the frustrations and let’s leverage the power of the web! That’s right, let’s use a web browser and a charting library to generate charts and have relative ease in doing so.

For my example, I chose to implement chart.js, but there are many, many libraries (depending on the look, feel, features you are looking for – Teechart, dygraphs, NVD3, jqPlot, and many more) you could leverage in a similar manner.

I’m not going to bore you with all the details, instead download and dissect the sample database I provide below, but the basic concept is I insert a webbrowser control in a form/report. It in turn reads a standard html file (this is the actual chart). The Update button is where all the ‘magic’ happens. It takes a template html file (basic setup without settings and data) and then add in the selected settings and data from the database and pushes it to the html file and then refreshes the webbrowser control.

Using a table such as

You can then very easily create things like

With this library, chart.js, you can create bar charts, line charts, pie charts, donut charts, ploar areas, bubbles, scatter, radar, …

As you can see, I added a column to in the table (shown above) to control the colors, that’s how easy it is! You could therefore, make an interactive form where users could pick their colors. You can even get into fonts, font weight, … all that fun stuff.

And yes, I went wild with my color selection, but you can use anything you’d like. You can make them all different or uniform, whatever make you happy.

I’m hoping to continue the demo further as I’d like to provide a dynamic version to show off some really useful functionalities I’ve setup before for some of my clients using a similar approach.

I know that this isn’t a click and dialog interactive solution. That said, once you’ve setup your basic HTML template file, it can be implemented in a minute or so and give you complete control! You can even have these settings in tables (as it should be) so you can easily edit, or better yet, allow your users to edit the values (colors, titles, fonts, …) so they can customize the output as they see fit.

Update 2019-10-10

I continued development today and made some nice progress. The latest version (2.000) now includes:

  • A startup form that will take care of the entire Browser Emulation registry fix
  • A new chart designer which allows you to
    • Modify the various chart properties (title, axis labels, legend…) on the fly
    • Edit the chart’s source data and have the chart reflect these changes in real-time
  • Added a Line chart to the options

This will pretty much do it for me for this demo as I think I’ve shown just how flexible and interactive such an approach can be!  Enjoy.

Using Chart.js V2...! What About Newer Versions?
Do note that we can only use Chart.js Version 2 or prior with the Web browser control due to compatibility issues.  So, currently, we cannot use V3 or V4.  Hopefully, once the new web browser control is released it will support things (HTML, JS, CSS, …) properly and we will be able to enjoy the new features that V4+ have to offer.

A special thanks to both Ben Clothier and Tom van Stiphout for helping me sort out the proper Registry hive to set for the Browser Emulation as things changed since I last automated this. Not sure if it was Windows 8 or 10, but things changed and they helped me understand what needed to be adjusted in my code.

Disclaimer/Notes:

If you do not have Microsoft Access, simply download and install the freely available runtime version (this permits running MS Access databases, but not modifying their design):

Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime

All code samples, download samples, links, ... on this site are provided 'AS IS'.

In no event will Devhut.net or CARDA Consultants Inc. be liable to the client/end-user or any third party for any damages, including any lost profits, lost savings or other incidental, consequential or special damages arising out of the operation of or inability to operate the software which CARDA Consultants Inc. has provided, even if CARDA Consultants Inc. has been advised of the possibility of such damages.

Download a Demo Database

Feel free to download a 100% unlocked demo copy by using the link provided below:

Download “Access - Improved Charting” Access_Charting.zip – Downloaded 10873 times – 192.96 KB

Version History

Version Date Changes
V1.000 2019-10-10 Initial Release
V2.000 2019-10-11 Web Emulation Automatic Fix, Interactive Chart Designer Form with added Line chart

Other Efforts on this Subject

I was recently, Nov. 2020, contacted by Thomas Möller, who is well known in the Access community, and who informed me that he had been working on a very similar project. So if this subject is of interest to you, you may wish to look at his offering at https://github.com/team-moeller/better-access-charts. He has also published a posting on his blog at https://translate.google.com/translate?hl=en&sl=auto&tl=en&u=https%3A%2F%2Fblog.team-moeller.de%2F2020%2F11%2Fich-bin-nicht-alleine.html (I’ve provided a translate link as the original page is in German, the original page is https://blog.team-moeller.de/2020/11/ich-bin-nicht-alleine.html).

References on the Subject

chart.js
Access WebBrowser Emulation

27 responses on “MS Access – Improved Charting”

  1. Robert Smith

    This looks interesting, thanks for your imagination and work on this. I’d like to learn how to automate production of a standard chart from Access data. Currently, I have to create charts in Excel for reports – which means re-entering my Access data. Is it possible to import a chart design from Excel into this database as a way to capture the design I’ve been using?

  2. Dr. Ahmad Hammoud

    Hello. It is an excellent tool. I am really happy to find it. I will ask my students to use it. It did add a lot to my knowledge, but there is one small issue: the charts are not displaying ARABIC characters. They are replaced with question marks. Is there any simple update that I can apply in order to support UNICODE labels.

  3. carlos Alvarez

    Hello,
    It looks great, unfortunately I was not able to run the demo, as my ms Acces wont recognize it. Wouldnt this work with access 2007?
    thanks

    1. Daniel Pineault Post author

      The demo itself was created using Access 2013, but I believe the true issue you are facing relates to the fact that I built my solution upon the Web Browser Control which was introduced in Access 2010 (I believe) and which basically replaces the Microsoft Web Browser ActiveX.

      I will try and get some time and see what I can do to downgrade the demo, but can’t make any promises.

  5. Branislav Mihaljev

    How to force chart Y-axis starts at 0? In the sample database, try changing all month quantity to 1 and 2 – the chart Y-axis start from 1 instead 0.

    1. Daniel Pineault Post author

      I sent you an e-mail, but for anyone else, there are several configuration options that you can employ to fully customize chart axes, refer to https://www.chartjs.org/docs/latest/axes/. Now to answer your direct question.

      If you edit the chart_designer’s VBA module, specifically the GenScales function, originally you will have:

      Function GenScales() As String
    Dim sScales               As String

    sScales = "                  scales: { " & vbCrLf & _
              "                       xAxes: [{ " & vbCrLf & _
              "                           scaleLabel: { " & vbCrLf & _
              "                               display: $xAxesDisplay, " & vbCrLf & _
              "                               labelString: '$xAxisLabel' " & vbCrLf & _
              "                           } " & vbCrLf & _
              "                       }], " & vbCrLf & _
              "                       yAxes: [{ " & vbCrLf & _
              "                           scaleLabel: { " & vbCrLf & _
              "                               display: $yAxesDisplay, " & vbCrLf & _
              "                               labelString: '$yAxisLabel' " & vbCrLf & _
              "                           } " & vbCrLf & _
              "                       }] " & vbCrLf & _
              "                   }"
    sScales = replace(sScales, "$xAxesDisplay", IIf(Me.chk_XAxis = True, "true", "false"))
    sScales = replace(sScales, "$xAxisLabel", Nz(Me.txt_XAxis, ""))
    sScales = replace(sScales, "$yAxesDisplay", IIf(Me.chk_YAxis = True, "true", "false"))
    sScales = replace(sScales, "$yAxisLabel", Nz(Me.txt_YAxis, ""))

    GenScales = sScales
End Function

      Now you have 2 options: ticks->beginAtZero or ticks->min, I’ll show an example or each below, simply replace the function with one of these.

      Function GenScales() As String
    Dim sScales               As String

    sScales = "                  scales: { " & vbCrLf & _
              "                       xAxes: [{ " & vbCrLf & _
              "                           scaleLabel: { " & vbCrLf & _
              "                               display: $xAxesDisplay, " & vbCrLf & _
              "                               labelString: '$xAxisLabel' " & vbCrLf & _
              "                           } " & vbCrLf & _
              "                       }], " & vbCrLf & _
              "                       yAxes: [{ " & vbCrLf & _
              "                           scaleLabel: { " & vbCrLf & _
              "                               display: $yAxesDisplay, " & vbCrLf & _
              "                               labelString: '$yAxisLabel' " & vbCrLf & _
              "                           }, " & vbCrLf & _
              "                           ticks: { " & vbCrLf & _
              "                               beginAtZero: true " & vbCrLf & _
              "                           } " & vbCrLf & _
              "                       }] " & vbCrLf & _
              "                   }"
    sScales = replace(sScales, "$xAxesDisplay", IIf(Me.chk_XAxis = True, "true", "false"))
    sScales = replace(sScales, "$xAxisLabel", Nz(Me.txt_XAxis, ""))
    sScales = replace(sScales, "$yAxesDisplay", IIf(Me.chk_YAxis = True, "true", "false"))
    sScales = replace(sScales, "$yAxisLabel", Nz(Me.txt_YAxis, ""))

    GenScales = sScales
End Function
      Function GenScales() As String
    Dim sScales               As String

    sScales = "                  scales: { " & vbCrLf & _
              "                       xAxes: [{ " & vbCrLf & _
              "                           scaleLabel: { " & vbCrLf & _
              "                               display: $xAxesDisplay, " & vbCrLf & _
              "                               labelString: '$xAxisLabel' " & vbCrLf & _
              "                           } " & vbCrLf & _
              "                       }], " & vbCrLf & _
              "                       yAxes: [{ " & vbCrLf & _
              "                           scaleLabel: { " & vbCrLf & _
              "                               display: $yAxesDisplay, " & vbCrLf & _
              "                               labelString: '$yAxisLabel' " & vbCrLf & _
              "                           }, " & vbCrLf & _
              "                           ticks: { " & vbCrLf & _
              "                               min: 0 " & vbCrLf & _
              "                           } " & vbCrLf & _
              "                       }] " & vbCrLf & _
              "                   }"
    sScales = replace(sScales, "$xAxesDisplay", IIf(Me.chk_XAxis = True, "true", "false"))
    sScales = replace(sScales, "$xAxisLabel", Nz(Me.txt_XAxis, ""))
    sScales = replace(sScales, "$yAxesDisplay", IIf(Me.chk_YAxis = True, "true", "false"))
    sScales = replace(sScales, "$yAxisLabel", Nz(Me.txt_YAxis, ""))

    GenScales = sScales
End Function

      Now you can use the min to set it to 0 to show the 0 in the axis, but then it will not show any negative values, as the min is now set to showing nothing below 0, so in my opinion the beginAtZero is the proper solution, but you see what fit the bill best for you. If you wanted, you could use DMin and DMax in real-time to set to set the suggestedMin or Min and suggestedMax or Max properties dynamically based on your data. This is the beauty of extending to different libraries you have so much more flexibility and customizations (look over the documentation to see everything that is possible).

      I hope this helps.

  6. Daniel Sanders

    I saw that you are using the FEATURE_BROWSER_EMULATION Value for internet explorer 11. I am wondering if it’s possible to set the value to Google Chrome? I am trying to open a webpage inside a Access Form in Google Chrome. Could this be a solution?

    Thanks.

    1. Daniel Pineault Post author

      No, WebBrowser control is IE based, there is no way to make it use alternate browser, nor set their versions. You can only use/set IE (the Microsoft browser).

    1. Daniel Pineault Post author

      I just downloaded and tested and it worked just fine in both Access 2013 & 2016.

      “not work” doesn’t tell me much of anything to try and help you. You would need to explain. Did you enable content, create a Trusted Location for the database? …

  8. Dennis

    Hi, You mentioned using this technique on Forms and Reports, I have tried to get the web-browser control working on reports and cannot. I notice your demo download only has Form examples and not Report examples, have you had this working on Reports as from all the information I can find the web-browser control does not work on Reports?

    1. Daniel Pineault Post author

      The only solutionworkaround has been to use a couple API to save an image of charts and use the image in reports. The process can be fully automated if you use some type of naming convention (I simply used the record’s PK value).

  9. Lynn

    Thank you Daniel, this solution is working brilliantly on my development machine (running Office 365 64-bit)! However when I load a second machine (also 365 64-bit) I get an error:
    Line: 0
    Char: 0
    Error: Script error.
    Code: 0
    URL: file: ///C:/…/js/Chartjs/Chart.min.js
    I’ve gone through all of the IE security settings and on both machines they appear to be identical. Can you help?

  10. Phil

    Hi,
    I’ve tested out your charting tool and the one created by Thomas Möller. Both work great. I see that your charting tool uses Chart.js 2.8.0, while the tool created by Thomas uses 2.9.4. Chart.js made some significant changes in versions 3.0.2 and above (now up to 3.7.0), and I’m testing out the use of this Chart.js version. I’m finding that for charts created using the 3.x standard I can render the chart in a browser (like Edge), but I can’t get it to render in the web browser control in Access. I get scripting errors and nothing displays in the web browser control, but that same file renders appropriately in the Edge browser.

    I’m wondering if you’ve tried or had any success with rendering charts created in Chart.js 3.x versions?

    1. Daniel Pineault Post author

      No, I’ve haven’t revisited that proof of concept since I published my article. If the newer version use HTML canvas and other advanced features, sadly they aren’t supported by the existing webbrowser control.

      1. Phillip G Ferrero

        Wouldn’t this IE Rendering engine in the web browser control be overridden when I specify which version of IE using :

  11. Milton

    Hi Dan
    I am getting this error on my machine.
    The code in this project must be updated for use on 64-bit systems.
    Please review and update declare statement and then mark them with the PtrSafe attribute.

    Thanks for developing such a wonderful concept.

    Regards
    Milton

    1. Daniel Pineault Post author

      The Registry code uses some APIs and they would need to be updated for 64-bit compatibility. This is not something I have the time to do. I have provided a fully functional proof of concept. Feel free to do the convertion and if you are feeling generous send me a message with a copy and I’ll be more than happy to add to this post.

  12. Kelly Torres

    I just wanted to thank you for sharing this. I have searched high and low for a charting solution for Access. I am an Access/VBA developer and I have used Chart.js in ASP but I never thought to implement it within Access. Great idea!!!

  13. Luis Ramos

    Daniel,

    Great stuff and I was able to use it successfully with MS Access 2016. Basically, I am rendering Chart.Js charts in MS Access Web Controls. It was working perfectly but all of the sudden it stopped working. As of now, charts are not being displayed in the MS Access Web Controls anymore and I haven’t done any code modification. Any idea what could be the problem?

    Thanks!

    1. Luis Ramos

      Update on my query: I was able to resolve the issue. Looks like my laptop cache memory was getting full…I just restarted my computer and guala!, Graphs rendered nicely again..

  14. Umar Yousufzai

    Hi Sir I have downloaded your Access database. There is no end to my happiness. I’ve wanted this for years but I’m a beginner if you could make a video on how we can use it in our database because I have no idea. Millions of thanks. Love you sir

  15. Francisco Alsina

    Can MS Access provide a scatter graphs with individual points being pie charts of data. A Google image search of ‘scatter pie chart’ is what I’m after.

    1. Daniel Pineault Post author

      I’m afraid I do not truly understand the question. You’re best bet would be to post your question in a forum and provide an example of what you are trying to achieve and someone there will be able to guide you. Best of luck to you with your project.