retrieving an id of newly created record

[microimage]

When a new record is inserted into access db it creates an id, how can I return this value for a record I have just created?

[Biffy]

Try:

set rs=Server.CreateObject("ADODB.Recordset")
rs=objconn.execute("SELECT @@IDENTITY")
recordID=rs(0)
set rs=nothing

[microimage]

SO where would I put the code? below is what I have already and my record ID is called recID

var MM_Q1B = String(Session("q1b"));
var MM_Q2B = String(Session("q2b"));
var MM_Q3B = String(Session("q3b"));
var MM_Q4B = String(Session("q4b"));
var MM_Q5B = String(Session("q5b"));
var rsActivity = Server.CreateObject("ADODB.Command");
rsActivity.ActiveConnection = MM_quickpoll_STRING;
rsActivity.CommandText = "Insert INTO tblWeekOne(Q1B,Q2B,Q3B,Q4B,Q5B) Values ('"+MM_Q1B+"','"+MM_Q2B+"','"+MM_Q3B+"','"+MM_Q4B+"','"+MM_Q5B+"')";
rsActivity.Execute();

[afrika]

you could use the scope_identity() function, as well, which you really cant go wrong with, because it works in the scope of the last inserted transaction

unlike the @@identity

[Nawaz Ijaz]

But if some one else insert another row before the execution of my particular code that fetches my id say your fuction afrika ??

What I do i query again with the inserted parameters and thats works fine at the cost of speed. Any better and reliable way to do that. Please share.

[afrika]

i really dont know about the inner workings of the computer, but i know there is somethign like message queueing. Such that it would pick each insert as its done. Thats why its called a mega hertz processor. processes info in milli seconds.

identity_scope() works in each scope and shoudl be able to cater for your transactions

[Biffy]

The @@identity would go straight after your execute statement:

rsActivity.Execute();

I've always used this and have never had problems.

[afrika]

SQL Books online

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.

@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

[afrika]

saw this similar post
http://www.sitepoint.com/forums/showthread.php?t=238815