[AccessD] A2003: Calling an Update SPROC-Calling aSPROCthatr eturns records

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