MS Access Multiuser - Please clarify...

Author

Please clarify...

Richard

2005-04-27, 3:24 am

Hi,

I'm new to Access and was trying to find out how to retrieve the record
identity after an insert...

Some postings I read suggested using MAX() to retrieve the inserted record
id, but that's not reliable, because another user or session could have
inserted a record in the same table too...

Fo example, in MS SQL after an insert, there are 3 ways to retrieve the
newly generated identity of the inserted record:

1) IDENT_CURRENT - returns the most recently inserted record id in a table
regardless of which process or session inserted it.

2) @@IDENTITY - returns the most recently inserted record id in a table
regardless of which process inserted it, but inserted in the current session.

3) SCOPE_IDENTITY - returns the most recently inserted record id in a table
by the current process in the current session.

How can this be done in Access? Has Access an equivalent functionality for
each of these 3 SQL features?

Thank you very much in advance,

Rick

Alex Dybenko

2005-04-27, 7:24 am

actually answer depends on where is your data. if you data on SQL server -
you can use any way you mentioned. if your data in mdb file - then you can
use either Select @@IDENTITY or (if you use recordset to add record) use
bookmark property to come back to last modified record and read it
autonumber value

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

"Richard" <Richard@discussions.microsoft.com> wrote in message
news:B01811C4-AEBC-43A1-B14E- 2E6EB48F1A77@microso
ft.com...
> Hi,
>
> I'm new to Access and was trying to find out how to retrieve the record
> identity after an insert...
>
> Some postings I read suggested using MAX() to retrieve the inserted record
> id, but that's not reliable, because another user or session could have
> inserted a record in the same table too...
>
> Fo example, in MS SQL after an insert, there are 3 ways to retrieve the
> newly generated identity of the inserted record:
>
> 1) IDENT_CURRENT - returns the most recently inserted record id in a table
> regardless of which process or session inserted it.
>
> 2) @@IDENTITY - returns the most recently inserted record id in a table
> regardless of which process inserted it, but inserted in the current
> session.
>
> 3) SCOPE_IDENTITY - returns the most recently inserted record id in a
> table
> by the current process in the current session.
>
> How can this be done in Access? Has Access an equivalent functionality for
> each of these 3 SQL features?
>
> Thank you very much in advance,
>
> Rick

Graham R Seach

2005-04-27, 11:24 am

Also if using a recordset, you can access the primary key before the Update
method is executed.

I didn't know that you could use @@IDENTITY against a Jet database. It's
obviously unreliable, but interesting to learn nonetheless.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

"Alex Dybenko" <alexdyb@PLEASE.cemi.NO.rssi.SPAM.ru> wrote in message
news:OkSgjdxSFHA.3184@TK2MSFTNGP09.phx.gbl...
> actually answer depends on where is your data. if you data on SQL server -
> you can use any way you mentioned. if your data in mdb file - then you can
> use either Select @@IDENTITY or (if you use recordset to add record) use
> bookmark property to come back to last modified record and read it
> autonumber value
>
> --
> Alex Dybenko (MVP)
> http://Alex.Dybenko.com
> http://www.PointLtd.com
>
>
> "Richard" <Richard@discussions.microsoft.com> wrote in message
> news:B01811C4-AEBC-43A1-B14E- 2E6EB48F1A77@microso
ft.com...
>
>

Alex Dybenko

2005-04-27, 11:24 am

Hi Graham,

> I didn't know that you could use @@IDENTITY against a Jet database. It's
> obviously unreliable, but interesting to learn nonetheless.

AFAIR - this is a feature of Jet 4.0

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Sponsored Links