[AccessD] New ID/Autonumber value

Sorry guys I was away yesterday.

If you are using Access as the backend and you are adding new records
through ADO code, then you can use @@Identity to return the autonumber
created with *that particular connection*.  

Try to think of it that you are actally creating a *new instance of a
connection* every time a record is entered (even though you are probably
using 'CurrentProject.Connection'). Therefore because this is a connection
on its own, in a multi-user database, where multiple users are adding
records, you are still *guaranteed* to return the autonumber of the record
created for *that specific user*.

That it the advantage of @@Idenitiy compared to other methods. It is tied in
to that particular connection (or statement) that created the record.
Whereas if you tried to use something like SELECT MAX(ID) to get the last
created record, then there is a danger that another record was created in
the meantime by another user.

Stuart, I am 99% certain that @@Idenity operates on that particulart
instance of a connection and that you will never get the autonumber from
another users' INSERT.  I'm happy for you to proove me wrong though!!  In
fact, it wouldn't be to hard to test, simply need to step through the code
just before executing the @@Identity statement and inserting a record
manually into the table, then seeing what @@IDentity returns.  Might go test
it myself!!

Quick reminder that I don't think @@Identity will work in Access 97 as it
was only introduced in Jet4.0 to satisfy us web coders!.  Not sre if this
has been posted but this is the KB article (watch wrapping):
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:
80/support/kb/articles/Q232/1/44.ASP&NoWebContent=1

Also, in an earlier post I mentioned 'transaction' which was a poor choice
of phrase. I was just trying to explain that each 'connection' was unique
for that user.

Hope this helps!
Cheers,
Andrew



-----Original Message-----
From: Stuart McLachlan [mailto:stuart at lexacorp.com.pg]
Sent: Friday, 9 May 2003 9:22 PM
To: Gustav Brock; accessd at databaseadvisors.com
Subject: Re: [AccessD] New ID/Autonumber value


In Jet 4 it's on a per connection basis. In SQL Server, it's a system 
variable. So it depends on whether you Backend is  Access or SQL 
Server.

If you are using an Acces BE with Jet 4 and only updating one table 
with the connection, it's safe - if it's an SQL Server BE or the same 
connection updates multiple tables in an Acces BE,  it's not.


On 9 May 2003 at 10:35, Gustav Brock wrote:

> Hi Andrew and Stuart et al
> 
> I'm confused!
> 
> Andrew wrote:
> 
> It is safer as  @@Identity is linked only to the connection (or
> transaction) which created the record.  
> 
> Otherwise you are risking that another user will insert a record
> in-between adding and retrieving the ID of the record added by the
> first user.
> 
> I'm not sure if that is a risk using the other methods mentioned on
> this list, however I *do* now that with @@Identity there is no risk of
> this happening.
> 
> but Stuart wrote:
> 
> > @@Identity is a system wide variable. It returns the last autonumber
> > generated by the system. You certainly can't rely on it to return a
> > particular record number in either a multi user system or in a
> > system where you are inserting into more than one table with
> > autonumbers keys in the same transaction.
> 
> So now, what to believe?
> 
> /gustav
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com


-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System 
Support.



_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

IMPORTANT - PLEASE READ ******************** 
This email and any files transmitted with it are confidential and may 
contain information protected by law from disclosure. 
If you have received this message in error, please notify the sender 
immediately and delete this email from your system. 
No warranty is given that this email or files, if attached to this 
email, are free from computer viruses or other defects. They 
are provided on the basis the user assumes all responsibility for 
loss, damage or consequence resulting directly or indirectly from 
their use, whether caused by the negligence of the sender or not.