[AccessD] A2003: Calling an Update SPROC-Calling a SPROC thatreturns 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