Multi access in SQL Server

Hello,

I am working on an Access VBA application, working in client/server
mode, with a shared SQL Server base.

I have to INSERT some data in the base, and the know what is the ID
that SQL Server associated with my data in the table. So I need to do
the following

INSERT data INTO TABLE
SELECT MAX ID FROM TABLE (to get the ID)

but since I am working in client/server, if some DATA has been inserted
by another client after my INSERT, then MAX ID is not the convenient
ID of my data ...

Is there a way for me to get the ID when I do the INSERT, or to
lock/unlock the table before the INSERT and after the SELECT ?

Thank you for any hint about that.

--
L'ordinateur peut faire plus de calculs que le cerveau de l'homme car
il n'a que ça à faire

Re: Multi access in SQL Server

ByB (email@email.com) writes:
> I am working on an Access VBA application, working in client/server
> mode, with a shared SQL Server base.
>
> I have to INSERT some data in the base, and the know what is the ID
> that SQL Server associated with my data in the table. So I need to do
> the following
>
> INSERT data INTO TABLE
> SELECT MAX ID FROM TABLE (to get the ID)
>
> but since I am working in client/server, if some DATA has been inserted
> by another client after my INSERT, then MAX ID is not the convenient
> ID of my data ...
>
> Is there a way for me to get the ID when I do the INSERT, or to
> lock/unlock the table before the INSERT and after the SELECT ?

It sounds like your table has the IDENTITY property. In such case, use
the function scope_identity() to retrieve last generated ID value.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx