Doug Steele
dbdoug at gmail.com
Thu Aug 21 23:37:32 CDT 2008
If you have any particular hints, I'd be interested in hearing them - I
expect to be trying this in the near future.
Thanks,
Doug Steele
On Thu, Aug 21, 2008 at 6:28 PM, Darren D <darren at activebilling.com.au>wrote:
> Hi team
>
> Thanks to all who responded. I did manage to cobble something together and
> it
> works nicely - Many thanks
> Now I need to start playing with the SPROCS that return records and
> displaying
> the results
>
> Darren
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> rusty.hammond at cpiqpc.com
> Sent: Thursday, 21 August 2008 1:13 AM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] A2003: Calling an Update SPROC-Calling aSPROCthatr
> eturns
> records
>
> I've done the pass through query as described below, but when parameters
> are
> required, I just change the sql of the query via code. Sample below
>
> Function ChangePassThroughQuery()
>
> Dim db as Database
> Dim qdf as QueryDef
> Dim strParam as String
>
> strParam = "ParameterValueHere"
>
> Set db = CurrentDb
> Set qdf = db.QueryDefs("qryRunSPROC")
> qdf.SQL = "StoredProcedureName " & strParam
> qdf.Close
>
> DoCmd.OpenQuery "qryRunSPROC")
>
> db.Close
> Set db = Nothing
>
> End Function
>
> HTH
>
> Rusty
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Darren D
> Sent: Tuesday, August 19, 2008 10:20 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] A2003: Calling an Update SPROC-Calling a
> SPROCthatreturns records
>
>
> Hi Stuart
>
> Thanks I'll give this a go - Sorry I should have mentioned that all the
> SPROS
> require parameters
>
> Darren
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
> McLachlan
> Sent: Wednesday, 20 August 2008 1:04 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] A2003: Calling an Update SPROC-Calling a SPROC
> thatreturns records
>
> Simplest way is to create a "PassThrough". Query
>
> IN 2003 (other version should be similar)"
>
> 1. Create a New query but don't select any tables.
> 2. Select Query - SQL Specific - Passthrough from the main menu bar.
> 3. Select View - Properties (Or hit Alt+Enter)
> 4. Click in OBDC Connect Str, thenclick on the builder button (...) and
> select
> your ODBC
> source (or enter a connection string if you have one written)
> 5. for an update query ,set the ReturnsRecords property to No.
> 6. Enter the sp name in the build window
> 7. Save the Query
>
> Then just open the query when required.
>
> Note that you can't pass parameters through a PassThrough query, if your sp
> requires
> parameters, you'll have to do it in code with something like this:
>
> Dim db As DAO.Database
> Dim qdPTQuery As QueryDef
> Dim strSQL As String
> Dim strParam as String
>
> strParam = "123"
>
> Set db = CurrentDb
> strSQL = "myUpdateSP " & strParam
> Set qdPTQuery = db.CreateQueryDef("myPassThroughQuery")
> qdPTQuery.Connect = "ODBC;DSN=................."
> qdPTQuery.SQL = strSQL
> qdPTQuery.returnsrecords = False 'if an update query
> qdPTQuery.Close
> db.Close
> Set db = Nothing
>
>
>
> On 20 Aug 2008 at 11:21, Darren D wrote:
>
> > Hi team
> >
> >
> >
> > I need to call some Stored procedures (SPROCS) in SQL Server from my
> Access dB
> >
> > The SPROCS will be both simple update ones and some of them will return
> results
> >
> > Does anyone have examples how to interact with both types?
> >
> > IE Just run one. And with the other get the results back and display them
> in
> my
> > Access dB?
> >
> >
> >
> > I think I have asked this question before but can't find the answer
> >
> > I think I was using an ADP when I asked though - This is a plain ole' MDB
> > request
> >
> >
> >
> > Many thanks in advance
> >
> >
> >
> > Darren
> >
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
>
> --
> Stuart Mclachlan
>
>
> --
> 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
>
> **********************************************************************
> WARNING: All e-mail sent to and from this address will be received,
> scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
> corporate e-mail system and is subject to archival, monitoring or review
> by, and/or disclosure to, someone other than the recipient.
> **********************************************************************
> --
> 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
>