Use MSChart with Access Database

I am creating a student database project in vb6 and I need to display a graph of the students results. I could not find any good tutorials on how to do it. I found this how to create Mschart in VB6? but the tutorial that was linked to it was removed. How do I connect the MSChart with my database?

Thanks for the help :)

Solution

I'm not entirely certain what you want to do, but here's a wild stab in the dark that might at least provide some ideas.

Table: Grades
Holds a row for each Student and their Grade in percent from 0 to 100.
Field: ID AutoNumber, Primary Key
Field: Student Text(50)
Field: Grade Byte

I want to plot counts of student Grade values as a bar chart in grade-range intervals, e.g. 0-9%, 10-19%, ... 100%.

To be sure I get the intervals with a count of 0 students in that range, I'll do a LEFT OUTER JOIN of Grades to another table GradeRanges:

Table: GradeRanges
Predefined auxiliary table with values from 0 to 100 in steps of 10 for GradeRange values.
Field: ID AutoNumber, Primary Key
Field: GradeRange Byte

I happen to have grades for 30 students. Results:

Option Explicit

Private Sub Form_Load()
    Dim CN As ADODB.Connection
    Dim RS As ADODB.Recordset

    With MSChart1
        .TitleText = "Counts of Student Grades in 10% Intervals"
        With .Title.VtFont
            .Size = 14
            .Style = VtFontStyleBold
            .VtColor.Set 32, 96, 192
        End With
        With .Plot
            With .Wall.Brush
                .Style = VtBrushStyleSolid
                .FillColor.Set 255, 255, 255
            End With
            With .Axis(VtChAxisIdY).ValueScale
                .Auto = False
                .Maximum = 15
                .Minimum = 0
                .MajorDivision = 3
                .MinorDivision = 2
            End With
            With .SeriesCollection.Item(1).DataPoints.Item(-1)
                With .Brush
                    .Style = VtBrushStyleSolid
                    .FillColor.Set 192, 224, 255
                End With
                With .DataPointLabel
                    .LocationType = VtChLabelLocationTypeAbovePoint
                    .VtFont.Style = VtFontStyleBold
                End With
            End With
        End With
    End With

    Set CN = New ADODB.Connection
    CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Mode=Share Exclusive;" _
          & "Data Source='grades.mdb'"

    On Error Resume Next 'In case previous run failed try dropping this here.
    CN.Execute "DROP TABLE [TempGrades]", , adCmdText Or adExecuteNoRecords
    On Error GoTo 0

    'Create TempGrades table containing CalcRange values so we can do a
    'LEFT OUTER JOIN to the GradeRanges table.  This allows us to plot
    'the ranges with a Count of 0 in them:
    CN.Execute "SELECT [Grade], ([Grade] \ 10) * 10 AS [CalcRange] " _
             & "INTO [TempGrades] FROM [Grades]", _
               , _
               adCmdText Or adExecuteNoRecords

    Set RS = New ADODB.Recordset
    With RS
        .CursorLocation = adUseClient
        .Open "SELECT CStr([GradeRange]) & '%', COUNT([Grade]) AS [Count] " _
            & "FROM [GradeRanges] LEFT OUTER JOIN [TempGrades] " _
            & "ON [GradeRanges].[GradeRange] = [TempGrades].[CalcRange] " _
            & "GROUP BY [GradeRange] " _
            & "ORDER BY [GradeRange]", _
              CN, _
              adOpenStatic, _
              adLockReadOnly, _
              adCmdText
        .MoveFirst 'Important, otherwise MSChart will lose the 1st row data!
                   'Also note that the 1st column MUST BE String data or it
                   'will not be used as X-axis Label values.
        Set MSChart1.DataSource = RS
        .Close
    End With

    CN.Execute "DROP TABLE [TempGrades]", , adCmdText Or adExecuteNoRecords
    CN.Close
End Sub

Private Sub Form_Resize()
    If WindowState <> vbMinimized Then
        MSChart1.Move 0, 0, ScaleWidth, ScaleHeight
    End If
End Sub

I'd love to do this without the aux and temp tables. Maybe somebody else can suggest an alternative.