[AccessD] Stored Procedure as Rowsource for Chart
Darren - Active Billing
darren at activebilling.com.au
Thu Oct 8 20:17:55 CDT 2015
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
More information about the AccessD
mailing list