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 faireByB (email@.email.com) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx