MSCHART Problem using ADO recordset and MS Access

[h4fod]

Hi
I am having difficulty displaying data in an MSChart control via an ADO recordset where the data is retreived iteratively using an ADO recordset connected to an Access database. The context is a student assessment database which stores "Surname", Attainment, Effort and Behaviour in a single MS Access 97 table 'tblAssessment'. The program iterates through the database records (rows) and retreives data for display in the MS Chart - 'MSChart'.

Problem is the X-Axis labels corectly show the first (and only) 10 surnames, but then 'adds' 'R1' as the 11th Label! The Y Axis values for the data set are not sensible. The chart seems calibrated 0-100, displaying data values up to about this value. Yet, the data values can only vary (integers) between 1 and 4.

If I connect the MS Chart to an ADO data control. set the parameters and bind the MS Chart object to this the data is displayed OK. But, I need my solution to develop to take control over the data displayed and that is why I would like my algorithm to work.

Hope you can help.
In anticipation, many thanks

Private Sub Command2_Click()

Set conn = New ADODB.Connection
Set rsAssessment = New ADODB.Recordset
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & "C:\temp\dbChartPrimer.mdb" & " ;Persist Security Info = false"
conn.Open

rsAssessment.Open ("SELECT Surname, Effort, Attainment,Behaviour FROM tblAssessment"), conn, adOpenStatic, adLockReadOnly

Dim i As Integer

i = 1

MSChart.RowCount = 1
MSChart.RowLabelCount = 1
MSChart.Title = "Assessment - Year 7"
'MSChart.Backdrop

With rsAssessment
    .MoveFirst
        Do While Not .EOF
            MSChart.Row = i
            MSChart.Data = .Fields("Effort")
            MSChart.RowLabel = .Fields("Surname")
                              
            MSChart.RowCount = MSChart.RowCount + 1
            MSChart.RowLabelCount = MSChart.RowLabelCount + 1
         i = i + 1
         .MoveNext
        Loop

End With

rsAssessment.Close
conn.Close

Set conn = Nothing


End Sub

 

[genomon]

I'll revisit this when I get more time, but at first blush: is Jet.OLEDB.4.0 compatible with Access 97? I was thinking 97 used 3.2 or something earlier. Probably doesn't matter but might be worth a try modifying your connection string...

Gluais faicilleach le cupan làn

 

[h4fod]

Hi
With regard to JET 4.0 compatibility - it seems OK. I have no issues with ADO recordset programming using this interface engine with the legacy Access 97 database. As a temporary workaround (and this does work), I have set an SQL String literal to extract the X/Y data to display in the MS Chart, then assign the literal to the ADO recordsource property. Selections are made using a combo drop down list. In this case a ADODC control is used on the form and the MSChart control is bound to this object. Perhaps this is the way ahead?

strSQL_Literal = cboADORecordSourceCondition.Text
Select Case strSQL_Literal
    Case "Effort Only"
        strSQL_Literal = "Select Surname, Effort FROM tblAssessment"
        MSChart.Title = "Year 7 Effort"
        EffortAverage
        lblMessage.Caption = "The average behaviour indicator for this class is: " & Format(EffortAverage, "#.##")
    Case "Attainment Only"
        strSQL_Literal = "Select Surname, Attainment FROM tblAssessment"
        MSChart.Title = "Year 7 Attainment"
    Case "Behaviour Only"
        strSQL_Literal = "Select Surname, Behaviour FROM tblAssessment"
        MSChart.Title = "Year 7 Behaviour"
    Case "All Assessment Parameters"
        strSQL_Literal = "Select * FROM tblAssessment"
        MSChart.Title = "Year 7 Assessment Data" & Date
End Select


adoAssessment.RecordSource = strSQL_Literal
adoAssessment.Refresh
MSChart.Refresh

 

[dilettante]

The Jet 4.0 Provider can work fine autodetecting a Jet 3.5 MDB file. It can even create new ones if you specify the old Engine Type in the connection string.

 

[h4fod]

Hi
Many thanks for both posts. Following on from last post - I will now use 3.5 engine with my ADO programming.
Hafod