[AccessD] Stored Procedure as Rowsource for Chart

[David Emerson]

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