none
Charting in Access RRS feed

  • Question

  • I have an financial analysis package that is currently being performed in Excel.  This analysis incorporates an extensive use of graphs.  Now, the amount of data used in this analysis is growing due to an increase in the number of participants.  For example, let's say for illustration purposes that I have 100 records.  I may use all of these records, but in different Excel files because a separate file is generated for each client.  The file is identical, in regards to the calculations and graphs, the data is just different.  That is what made me think Access would be a good solution for this; however, I am not familiar with Access' charting capabilities.  I created a small test DB and the only charting features I see are ones that seem to be very basic.  I guess my question is this:

    1) Does Access have a more or less robust charting feature than Excel?

    2) If my data set is growing too large for Excel, then I guess I could use Access to house and sort the data, then export what data I needed for each client to individual Excel files where the charting is performed.  Does that sound like a vialbe solution?

    Tuesday, October 18, 2011 9:09 PM

Answers

  • JD,

        My sample db named Charts_AccessAndExcel might be of interest to you. It is available at Rogers Access Library. Link:
    http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45
     
        This sample db (access 2000 file format) covers a combination chart showing the readings for patient's blood pressure - systolic & diastolic as twin line series, along with pulse readings in column style.

        For ready appreciation, the same set of readings is displayed in two alternative modes, one as access native graph (on form as well as report) and the other as an Excel graph, dynamically created in Excel (based upon Access data) and then displayed on access form via OLE control.

        References:
            (a) Microsoft Excel Object library (version 9.0 or later)
            (b) Microsoft Scripting RunTime
            (c) Microsoft Graph - (Appropriate Version)
            (d) DAO 3.6

    Best wishes,
    A.D. Tejpal
    ------------
     
    ----- Original Message -----
    From: jdmcleod
    Newsgroups: Msdn.en-US.accessdev
    Sent: Wednesday, October 19, 2011 02:39
    Subject: Charting in Access

    I have an financial analysis package that is currently being performed in Excel.  This analysis incorporates an extensive use of graphs.  Now, the amount of data used in this analysis is growing due to an increase in the number of participants.  For example, let's say for illustration purposes that I have 100 records.  I may use all of these records, but in different Excel files because a separate file is generated for each client.  The file is identical, in regards to the calculations and graphs, the data is just different.  That is what made me think Access would be a good solution for this; however, I am not familiar with Access' charting capabilities.  I created a small test DB and the only charting features I see are ones that seem to be very basic.  I guess my question is this:

    1) Does Access have a more or less robust charting feature than Excel?

    2) If my data set is growing too large for Excel, then I guess I could use Access to house and sort the data, then export what data I needed for each client to individual Excel files where the charting is performed.  Does that sound like a vialbe solution?


    A.D. Tejpal
    • Marked as answer by Bruce Song Wednesday, November 2, 2011 4:14 AM
    Wednesday, October 19, 2011 3:37 AM
  • SysWizard,
     
        Although the sample db (Charts_AccessAndExcel) was developed in Access 2000 file format (in conjunction with Excel 2000), tests at this end show that it works smoothly on Access 2010 installation (Win XP) as well, without needing any modification. It was not found necessary to explicitly tinker with any reference, setting or property.
     
        It is further observed that even on a brand new accdb file in Access 2010, freshly inserted unbound OLE control for excel chart, shows the following properties:
        "Class: Excel.Sheet.8 and OLE Class: Microsoft Excel 2003"
     
        Similar exercise on insertion of access native chart control (in accdb file - Access 2010) leads to following properties:
         "Class: MSGraph.Chart.8 and OLE Class: Microsoft Graph Chart"
     
        Apparently, in this context, the situation has not changed drastically since Access 2000.
     
        The alternative of linked arrangement instead of embedded as mentioned in your post can be adopted if desired.
     
        Regarding your observation that in the VBA references, there is no "Sheet" class in the Excel library, it is to be noted that from access end, excel chart gets referenced via Charts collection of excel workbook. This is demonstrated in subroutine named P_SetChartTitles() in VBA module of mdb file.
     
        Note:
        Excel workbook used as template has just two sheets - one for chart proper and the other for chart data that is pushed from access via its subroutine P_SendDataToExcel(), depending upon selection of patient for whom the chart is to be generated.
     
    Best wishes,
    A.D. Tejpal
    ------------
     
    ----- Original Message -----
    Newsgroups: Msdn.en-US.accessdev
    Sent: Wednesday, October 19, 2011 18:45
    Subject: Re: Charting in Access

    Thanks AD. Using AC/XL 2007....This is strange: for the Excel example, the properties of the embedded chart are :

    Class: Excel.Sheet.8 and OLE Class: Microsoft Excel 2000

    Now i changed the class to : Excel.Sheet.12, but the OLE Class did not change and it was read-only.
    Interestingly, all of my VBA references were to Office 2007 libraries.

    On the embedded Graph for the Access example....

    Class: MSGraph.Chart.8 and OLE Class: Microsoft Graph Chart

    Once again I was able to change this to the 2007 .12 reference....but the class name did not change.

    Also interesting: in the VBA references, there is no "Sheet" class in the Excel library...so I still can't determine what level of control is available via VBA with the Excel vs. MSGraph(Access) approach. My feeling is that the Excel approach provides much more control. However, your implementation makes the data referenced on the graph static. A better approach may be to use a querytable connection in Excel and LINK rather than EMBED the Excel Chart. Thus, data can then be refreshed without having to regenerate the chart. The new 2010 trigger feature could insure this refresh occurs upon any underlying table change.


    A.D. Tejpal
    • Marked as answer by Bruce Song Wednesday, November 2, 2011 4:14 AM
    Wednesday, October 19, 2011 4:55 PM

All replies

  • 1) No. Less "robust"

    2) I guess it depends on how "robust" you need. Charting in Access was not a priority feature as in Excel. You may be able to export the data to an Excel file for charting purposes and then import or link the chart into an Access report but I have never experimented with that.

    Tuesday, October 18, 2011 11:21 PM
  • 1) No. Less "robust"

    2) I guess it depends on how "robust" you need. Charting in Access was not a priority feature as in Excel. You may be able to export the data to an Excel file for charting purposes and then import or link the chart into an Access report but I have never experimented with that.

    I'm very interested in this as well.

    Does anyone know the specific shortcomings of Access charting compare to Excel ?

     

    Wednesday, October 19, 2011 2:27 AM
  • JD,

        My sample db named Charts_AccessAndExcel might be of interest to you. It is available at Rogers Access Library. Link:
    http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45
     
        This sample db (access 2000 file format) covers a combination chart showing the readings for patient's blood pressure - systolic & diastolic as twin line series, along with pulse readings in column style.

        For ready appreciation, the same set of readings is displayed in two alternative modes, one as access native graph (on form as well as report) and the other as an Excel graph, dynamically created in Excel (based upon Access data) and then displayed on access form via OLE control.

        References:
            (a) Microsoft Excel Object library (version 9.0 or later)
            (b) Microsoft Scripting RunTime
            (c) Microsoft Graph - (Appropriate Version)
            (d) DAO 3.6

    Best wishes,
    A.D. Tejpal
    ------------
     
    ----- Original Message -----
    From: jdmcleod
    Newsgroups: Msdn.en-US.accessdev
    Sent: Wednesday, October 19, 2011 02:39
    Subject: Charting in Access

    I have an financial analysis package that is currently being performed in Excel.  This analysis incorporates an extensive use of graphs.  Now, the amount of data used in this analysis is growing due to an increase in the number of participants.  For example, let's say for illustration purposes that I have 100 records.  I may use all of these records, but in different Excel files because a separate file is generated for each client.  The file is identical, in regards to the calculations and graphs, the data is just different.  That is what made me think Access would be a good solution for this; however, I am not familiar with Access' charting capabilities.  I created a small test DB and the only charting features I see are ones that seem to be very basic.  I guess my question is this:

    1) Does Access have a more or less robust charting feature than Excel?

    2) If my data set is growing too large for Excel, then I guess I could use Access to house and sort the data, then export what data I needed for each client to individual Excel files where the charting is performed.  Does that sound like a vialbe solution?


    A.D. Tejpal
    • Marked as answer by Bruce Song Wednesday, November 2, 2011 4:14 AM
    Wednesday, October 19, 2011 3:37 AM
  • Thanks AD. Using AC/XL 2007....This is strange: for the Excel example, the properties of the embedded chart are :

    Class: Excel.Sheet.8 and OLE Class: Microsoft Excel 2000

    Now i changed the class to : Excel.Sheet.12, but the OLE Class did not change and it was read-only.
    Interestingly, all of my VBA references were to Office 2007 libraries.

    On the embedded Graph for the Access example....

    Class: MSGraph.Chart.8 and OLE Class: Microsoft Graph Chart

    Once again I was able to change this to the 2007 .12 reference....but the class name did not change.

    Also interesting: in the VBA references, there is no "Sheet" class in the Excel library...so I still can't determine what level of control is available via VBA with the Excel vs. MSGraph(Access) approach. My feeling is that the Excel approach provides much more control. However, your implementation makes the data referenced on the graph static. A better approach may be to use a querytable connection in Excel and LINK rather than EMBED the Excel Chart. Thus, data can then be refreshed without having to regenerate the chart. The new 2010 trigger feature could insure this refresh occurs upon any underlying table change.

     

    Wednesday, October 19, 2011 1:15 PM
  • SysWizard,
     
        Although the sample db (Charts_AccessAndExcel) was developed in Access 2000 file format (in conjunction with Excel 2000), tests at this end show that it works smoothly on Access 2010 installation (Win XP) as well, without needing any modification. It was not found necessary to explicitly tinker with any reference, setting or property.
     
        It is further observed that even on a brand new accdb file in Access 2010, freshly inserted unbound OLE control for excel chart, shows the following properties:
        "Class: Excel.Sheet.8 and OLE Class: Microsoft Excel 2003"
     
        Similar exercise on insertion of access native chart control (in accdb file - Access 2010) leads to following properties:
         "Class: MSGraph.Chart.8 and OLE Class: Microsoft Graph Chart"
     
        Apparently, in this context, the situation has not changed drastically since Access 2000.
     
        The alternative of linked arrangement instead of embedded as mentioned in your post can be adopted if desired.
     
        Regarding your observation that in the VBA references, there is no "Sheet" class in the Excel library, it is to be noted that from access end, excel chart gets referenced via Charts collection of excel workbook. This is demonstrated in subroutine named P_SetChartTitles() in VBA module of mdb file.
     
        Note:
        Excel workbook used as template has just two sheets - one for chart proper and the other for chart data that is pushed from access via its subroutine P_SendDataToExcel(), depending upon selection of patient for whom the chart is to be generated.
     
    Best wishes,
    A.D. Tejpal
    ------------
     
    ----- Original Message -----
    Newsgroups: Msdn.en-US.accessdev
    Sent: Wednesday, October 19, 2011 18:45
    Subject: Re: Charting in Access

    Thanks AD. Using AC/XL 2007....This is strange: for the Excel example, the properties of the embedded chart are :

    Class: Excel.Sheet.8 and OLE Class: Microsoft Excel 2000

    Now i changed the class to : Excel.Sheet.12, but the OLE Class did not change and it was read-only.
    Interestingly, all of my VBA references were to Office 2007 libraries.

    On the embedded Graph for the Access example....

    Class: MSGraph.Chart.8 and OLE Class: Microsoft Graph Chart

    Once again I was able to change this to the 2007 .12 reference....but the class name did not change.

    Also interesting: in the VBA references, there is no "Sheet" class in the Excel library...so I still can't determine what level of control is available via VBA with the Excel vs. MSGraph(Access) approach. My feeling is that the Excel approach provides much more control. However, your implementation makes the data referenced on the graph static. A better approach may be to use a querytable connection in Excel and LINK rather than EMBED the Excel Chart. Thus, data can then be refreshed without having to regenerate the chart. The new 2010 trigger feature could insure this refresh occurs upon any underlying table change.


    A.D. Tejpal
    • Marked as answer by Bruce Song Wednesday, November 2, 2011 4:14 AM
    Wednesday, October 19, 2011 4:55 PM
  • I wonder if the reference changes if a Chartsheet is used instead of a standard Worksheet ?
    Wednesday, October 19, 2011 7:05 PM