Get newly created ID in SQL Server

Hi

I am building an MS Access/SQL Server app.  If I create a new record  in the SQL Server backend using VBA behind an Access form how can I get the newly created ID and return it to Access?

Cheers
kenabbottAsked:
Who is Participating?
 
dportasConnect With a Mentor Commented:
Use SCOPE_IDENTITY() wherever possible rather than @@IDENTITY.

SCOPE_IDENTITY() returns the last inserted IDENTITY value in the current scope, which means its behaviour won't be affected by any current or future triggers on the table. If you are using SQL stored procs for data access then SCOPE_IDENTITY() should normally be preferred over @@IDENTITY.
0
 
Muhammad KashifDevelopment ManagerCommented:
Use
Select @@IDENTITY


You can use like this with your insert query

Insert into Table values(1,2,.....)
Select @@IDENTITY
0
 
kenabbottAuthor Commented:
So is the only way of doing this via a stored procedure??  Its not by getting a returned value with a pass thru query for example
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How are you creating the new record? Show the relevant portion of the code.

Does the Jet/OLEDB provider support SCOPE_IDENTITY? I'm not sure ... I know it supports IDENTITY:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;232144

But I've not found an article stating it supports SCOPE_IDENTITY. That said, you could of course get this via a Stored Proc or something ...
0
 
kenabbottAuthor Commented:
At the moment I'm still working on this but basically what I need to achieve is as follows...

1.  User enters data onto an access form
2.  Data saved in SQL as new record
3.  New ID returned
4.  New ID saved in a seperate SQL table (along with other data)

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Again: How are you creating the new record? Are you using bound forms? If you are then your Form's Recordset will contain the new value after saving ...
0
 
kenabbottAuthor Commented:
Sorry - no it is an unbound form so I will be using a SQL statement of some sort
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'd certainly agree with dportas as to using SCOPE_IDENTITY, but as I said earlier I'm not sure it's supported. You could do this:

Dim rst As ADODB.Recordset
Dim lNewID As Long

<Insert your record>

Set rst =New ADODB.Recordset
rst.Open "SELECT @@IDENTITY AS NewID", YourConnectionObject
lNewID = rst("NewID")

This could run into issues if you have a lot of users banging away on this, since as others have said @@IDENTITY gets the last inserted value - so if you insert your record, and then I insert a record immediately after that BEFORE YOUR CALL TO @@IDENTITY then you'll get the ID of the record I inserted. If you use a Stored Proc, you can use SCOPE_IDENTITY, which insures that you get the correct value.

0
 
dportasCommented:
@@IDENTITY is scoped to the connection so you will never get the identity value of a row inserted by someone else unless you are connection pooling.

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Good to know ...
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So why did you accept the SCOPEIDENTITY answer? Is that what you ended up using?
0
 
kenabbottAuthor Commented:
yes
0
All Courses

From novice to tech pro — start learning today.