[AccessD] Stored Procedure as Rowsource for Chart

[David Emerson]

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/How-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