[AccessD] Stored Procedure as Rowsource for Chart

[Darren - Active Billing]

Glad it’s been resolved - 
FYI - I have an MS Access/SQL Demo I can send over if you like. 
It employs the passthrough method Stuart is advocating.
Lemme know off-list
Any me too’s off list - thanks
Darren
__________________________________________________________________________________________________________________________________

> On 8 Oct 2015, at 5:47 pm, David Emerson <newsgrps at dalyn.co.nz> wrote:
> 
> Hi Jim,
> 
> Thanks for your response.  Stuart solved the problem for me.
> 
> Regards
> 
> David
> 
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim
> Lawrence
> Sent: Thursday, 8 October 2015 5:41 p.m.
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Stored Procedure as Rowsource for Chart
> 
> Hi David:
> 
> Here is an overly simplified Youtube vid of connecting MS Access to a MySQL
> DB: https://www.youtube.com/watch?v=F06hvR6ksh4
> 
> Here is a link to some comprehensive lists of database to database
> connection string: http://www.connectionstrings.com and
> http://www.dofactory.com/reference/connection-strings and
> http://www.carlprothman.net/Default.aspx?tabid=90
> 
> Aside: If you use the standard method of generating an ODBC connect, the
> file created can be stripped down to a basic string by changing the file's
> extension to ".txt" and using the resultant connection text string directly
> within your Access connection module.
> 
> Do you have direct root access to the MySQL DB that you will be using? 
> 
> HTH
> Jim 
> 
> ----- Original Message -----
> From: "David Emerson" <newsgrps at dalyn.co.nz>
> To: "Access Developers discussion and problem solving"
> <accessd at databaseadvisors.com>
> Sent: Wednesday, October 7, 2015 5:24:09 PM
> Subject: Re: [AccessD] Stored Procedure as Rowsource for Chart
> 
> 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
> 
> 
> --
> 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
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com