[AccessD] Stored Procedure as Rowsource for Chart

[Stuart McLachlan]

Don't use a temp query.  Used a stored query.

Create a Passthrough query as qryChartData which calls your SP with some date.
Save that query.
Set that query as the row source.

Assuming that you open the report from a button click somewhere and you have the desired 
date available then in your code modify and save the  qryDef.SQL of qryChartData  before 
opening the report.



On 8 Oct 2015 at 13:24, David Emerson wrote:

> Thanks Stuart,  I looked at the article but I don't think it quite
> fits my problem.
> 
> I already have code to get the results from a passthrough query.  My
> problem is how and where I can set the Chart rowsource so that the
> data from the stored procedure is used.
> 
> Here is the code (simplified) for the passthrough query:
> 
> Public grst1 As DAO.Recordset
> 
> Public Sub basReportPassThroughQuery(strSproc As String)
> ' Sample Call (Report and 1 sub report)
> 'strSproc = "EXEC dbo.spsrpClientQualitySurvey " & Nz(Me!txtID, 0)
> 'Call basReportPassThroughQuery(strSproc)
> 
>     Dim strTempQueryName As String, qdefTemp As DAO.QueryDef
> 
>     strTempQueryName = "qryTempQuery" 
>     CurrentDb.QueryDefs.Delete strTempQueryName 'In case exists
>     Set qdefTemp = CurrentDb.CreateQueryDef(strTempQueryName)
>     qdefTemp.ReturnsRecords = True
>     qdefTemp.ODBCTimeout = 120
>     qdefTemp.Connect = "ODBC;DRIVER=SQL
> Server;SERVER=Servername;DATABASE=database;Trusted_Connection=Yes"
> 
>     qdefTemp.SQL = strSproc
> 
>     Set grst1 = qdefTemp.OpenRecordset
> 
> End Sub
> 
> Now that I have the recordset how do I get the chart to use it?
> 
> Regards
> 
> David
> 
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> Of Stuart McLachlan Sent: Thursday, 8 October 2015 12:51 p.m. To:
> Access Developers discussion and problem solving Subject: Re:
> [AccessD] Stored Procedure as Rowsource for Chart
> 
> Use a  Passthrough query as the source.
> 
> You can generate the querydef for the PT query programmatically if 
> you need to pass parameters to your procedure. 
> 
> A good howto with appropriate examples is here:
> 
> http://www.databasejournal.com/features/msaccess/article.php/3407531/H
> ow-to- Execute-SQL-Stored-Procedures-from-Microsoft-Access.htm
> 
> --
> Stuart
> 
> On 8 Oct 2015 at 12:34, David Emerson wrote:
> 
> > Hi Listers,
> >  
> > I have a simpler report with a single chart.  I want to use a stored
> > procedure as the rowsource.  How do I do this?  I have tried putting
> > the procedure name in the rowsource field but this doesn't work
> > (which doesn't surprise me).
> > 
> > Here is the procedure call: EXEC dbo.sprptReferralTypeValueMonth
> > '2015-06-01'
> > 
> > I am guessing that I need to set a ADODB.Recordset when the report
> > is loaded but I am not sure what to do with the recordset once I
> > have it.
> > 
> > Regards
> > 
> > David Emerson
> > Dalyn Software Ltd
> > Wellington, New Zealand
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>