Access front end - stored procedure result

Hello,
I have a SQL Server 2000 database with an Access 97 front end.
I want to run stored procedures, (not nessessarily ones which return
records either - action type queries for adding new records etc), from
access and retreive some result from the procedure which would be used
programatically in VBA so that users don't get nasty looking ODBC
errors. Could anyone suggest what I need to do? I'm aware I could use
a pass through query to run my stored procedure all the time if i need
a result returned, but this seems overkill for the sake of getting
some form of return value from the procedure, surely there must be a
more sensible way? Or is there a good website which tutors this kind
of stuff? I've trawelled the net and I can't see anything which will
help me so far and feel sure that this is such a normal thing to need
to do...
Also, I'm a bit scared of ADO (probably irrationally) - just used DAO
so far and feel nervy and unsure about how (or why) I should be using
ADO, so DAO stuff would be nice! (or some nice pointers or examples on
ADO to calm my nerves!)
Cheers,
Neil
Pass-through queries *are* the most efficient way of working with
stored procedures. You can manipulate them in code via the DAO
QueryDef object, supplying a string for the .SQL property that
contains the execute statement plus any parameter values:
qdf.SQL = "EXEC mysproc 'paramvalue1'", etc.
If the stored procedure returns a result set it will be read only,
which is ideal for reports, which can be based on the pass-through
query.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On 13 Apr 2004 04:54:33 -0700, hey_its_neil@.yahoo.co.uk (Neil) wrote:

>Hello,
>I have a SQL Server 2000 database with an Access 97 front end.
>I want to run stored procedures, (not nessessarily ones which return
>records either - action type queries for adding new records etc), from
>access and retreive some result from the procedure which would be used
>programatically in VBA so that users don't get nasty looking ODBC
>errors. Could anyone suggest what I need to do? I'm aware I could use
>a pass through query to run my stored procedure all the time if i need
>a result returned, but this seems overkill for the sake of getting
>some form of return value from the procedure, surely there must be a
>more sensible way? Or is there a good website which tutors this kind
>of stuff? I've trawelled the net and I can't see anything which will
>help me so far and feel sure that this is such a normal thing to need
>to do...
>Also, I'm a bit scared of ADO (probably irrationally) - just used DAO
>so far and feel nervy and unsure about how (or why) I should be using
>ADO, so DAO stuff would be nice! (or some nice pointers or examples on
>ADO to calm my nerves!)
>Cheers,
>Neil