[AccessD] Stored Procedure as Rowsource for Chart

[David Emerson]

Thanks Stuart - that was the missing piece.  Putting the name of the query
in the Rowsource property.

Much appreciated.

David

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Stuart McLachlan
Sent: Thursday, 8 October 2015 1:38 p.m.
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Stored Procedure as Rowsource for Chart

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
> 


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com