Posted by Erland Sommarskog on 06/17/07 13:35

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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]