> OK, I see that it is possible to return @@IDENTITY from 

> Access, but how reliable is this? From

> http://support.microsoft.com/support/kb/articles/Q232/1/44.ASP

>  I see that you run another qry to get the value, 

... 

> Using @@IDENTITY with Access appears to me to be

> mildly better than "SELECT MAX(tblID) from tbl". 



Assuming  (and hoping against hope) that @@IDENTITY is scoped to 

the  open connection, it should be infinitely better (i.e. Right 

as opposed to Wrong :-) )



Only an utterly braindead db engine design would make @@IDENTITY 

global  to  all db users, but I've seen worse travesties made by 

a certain large software company.



> It still appears to me that getting the ID after AddNew would 

> be more reliable. 



It's certainly tidier, and is 'Right' too!

@@IDENTITY relies on Jet OLEDB 4.0, it cannot be used with any other

provider, unless it is explicitely implemented there (like most DB servers).

It is scoped to the database connection, so that pretty much guarantees that

you  will get the ID of the last record YOU insterted (and not somebody

else).



The question is why would you use this instead of rs.Addnew, rs.Update and

ID = rs("ID"). For one because that is slower, but more importantly less

scalabale. See http://www.adopenstatic.com/experiments/fastestautonumber.asp

about the performance.





---

Michiel van Otegem

ASPNL.com    - ASP/ASP.NET/XML Teacher

ASPLists.com - Moderator



http://www.aspnl.com

http://www.aspalliance.com/michiel

off-list email: michiel@a...

---

----- Original Message -----

From: "Steve Carter" <Steve.Carter@t...>

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, July 18, 2001 10:41 AM

Subject: [asp_databases] @@IDENTITY in access (RE: Inserting, but returning

one field)





> > OK, I see that it is possible to return @@IDENTITY from

> > Access, but how reliable is this? From

> > http://support.microsoft.com/support/kb/articles/Q232/1/44.ASP

> >  I see that you run another qry to get the value,

> ...

> > Using @@IDENTITY with Access appears to me to be

> > mildly better than "SELECT MAX(tblID) from tbl".

>

> Assuming  (and hoping against hope) that @@IDENTITY is scoped to

> the  open connection, it should be infinitely better (i.e. Right

> as opposed to Wrong :-) )

>

> Only an utterly braindead db engine design would make @@IDENTITY

> global  to  all db users, but I've seen worse travesties made by

> a certain large software company.

>

> > It still appears to me that getting the ID after AddNew would

> > be more reliable.

>

> It's certainly tidier, and is 'Right' too!

>

>

Wow thanks for all the responses.  Just one note on a suggestion a few of 

you had.  You suggested updating then getting the max record.  Well if 

someone inserts a record between the update and max call, you will get the 

wrong record number.  I'm sure most of you caught that, but just thought I 

should point it out for those who hadn't.