Re: Output parameters for Access 2000 stored procedure

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Alex Dybenko (alex_at_PLEASE.cemi.NO.rssi.SPAM.ru)
Date: 01/24/05 

Date: Mon, 24 Jan 2005 20:14:54 +0300

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
>> >
>>
>>
>>

Relevant Pages

  • Re: Deleting Records from recordset
    ... Alex Dybenko (MVP) ... Still curious to know why it didn't work through the recordset though. ... "Alex Dybenko" wrote in message ... FROM tBudgetNew INNER JOIN qBOMACodesUnfiltered ...
    (microsoft.public.access.modulesdaovba)
  • Re: Deleting Records from recordset
    ... Best regards, ... Alex Dybenko (MVP) ... "Alex Dybenko" wrote in message ... Still curious to know why it didn't work through the recordset though. ...
    (microsoft.public.access.modulesdaovba)
  • Re: adOpenStatic Recordset still changing my table
    ... Best regards, ... Alex Dybenko (MVP) ... fine but it raises errors when I try and work in the recordset because it's ... "Alex Dybenko" wrote: ...
    (microsoft.public.access.modulesdaovba)
  • Re: New Error using CopyFromRecordset
    ... the first parameter should be a recordset, and you passing a field, try: ... Alex Dybenko (MVP) ... Class does not support Automation or does not support expected interface. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Checking all subform entries to find a match?
    ... Alex Dybenko ... "Daniel" wrote in message ... you can get a recordset using recordsetclone:>> ... >>> a way to search through the actively display record of a subform? ...
    (microsoft.public.access.modulesdaovba)