Re: Output parameters for Access 2000 stored procedure

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Ollie (Ollie_at_discussions.microsoft.com)
Date: 01/27/05 

Date: Thu, 27 Jan 2005 09:57:04 -0800

Hi Alex,

Thanks for the clarification

"Alex Dybenko" wrote:

> Hi,
> something like this:
> SP sp_InsertIntoTable1 :
>
> Insert into table1 (field1) Values (1)
>
> select @@Identity
>
> and code:
>
> Set qryd = dbs.CreateQueryDef("")
> qryd.Connect = "<con string>"
> qryd.SQL = "sp_InsertIntoTable1"
> qryd.ReturnsRecords = true
> Set rst = qryd.OpenRecordset(dbOpenForwardOnly)
> If Not rst.EOF Then
> debug.print = rst(0).Value
> End If
>
> HTH
> --
> Alex Dybenko (MVP)
> http://Alex.Dybenko.com
> http://www.PointLtd.com
>
>
>
> "Ollie" <Ollie@discussions.microsoft.com> wrote in message
> news:BE3EDF67-F4CA-4A44-9F24-002DE262F24F@microsoft.com...
> > Hi Alex,
> >
> > thanks for the response.
> >
> > Could you clarify what you mean when you say:
> > " I am returning value from SP in your case as using select
> > @@identity at the end of proc, and in access I get it by opening recordset
> > on "exec sp" and retrieving Fields(0)"
> >
> > If you could give an example of how I would use your method to return the
> > primary key of a newly inserted record, that would great.
> >
> > Cheers,
> > Ollie
> >
> > "Alex Dybenko" wrote:
> >
> >> Hi Ollie,
> >> output parameter - don't remember exactly, but I believe these is a way
> >> doing this using ADO parameter
> >>
> >> personally - I am returning value from SP in your case as using select
> >> @@identity at the end of proc, and in access I get it by opening
> >> recordset
> >> on "exec sp" and retrieving Fields(0)
> >>
> >> --
> >> Alex Dybenko (MVP)
> >> http://Alex.Dybenko.com
> >> http://www.PointLtd.com
> >>
> >>
> >> "Ollie" <Ollie@discussions.microsoft.com> wrote in message
> >> news:F2EBB8CF-01E1-41E5-91D4-170C7A2D8F8B@microsoft.com...
> >> > Is it possible to create a stored procedure for Access 2000 that has an
> >> > output parameter? I know this is possible with SQL Server but have not
> >> > been
> >> > able to track down an equivilent example for Access.
> >> >
> >> > If it is not possible, can anyone suggest a way of returning a value
> >> > from
> >> > an
> >> > insert query (e.g. the primary key of a newly created record) from
> >> > Access
> >> > 2000 to an calling application (e.g. ASP application).
> >> >
> >> > Cheers,
> >> > Ollie
> >> >
> >>
> >>
> >>
>
>
>