Andy Lacey
andy at minstersystems.co.uk
Sat May 10 03:18:27 CDT 2003
I can confirm that @@IDENTITY's no use in A97/Jet 3. Tried it and it errors. Shucks. Andy Lacey http://www.minstersystems.co.uk > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of > Haslett, Andrew > Sent: 09 May 2003 22:55 > To: 'accessd at databaseadvisors.com' > Subject: RE: [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/a> ccessd > 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. _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/a> ccessd > Website: > http://www.databaseadvisors.com >