<html><div style='background-color:'><DIV>
<P>I used it with the table name (SELECT @@ IDENTITY FROM TableName) and it returned the autonumber - like...automagically! If this follows my usual development cycle, I'm doing it completely wrong and I will learn the sad lesson when it bites me in the netherlands.<BR>_d<BR><FONT face="Geneva, Arial, Sans-serif" color=#6666ff size=2><EM></EM></FONT></P>
<P><FONT face="Geneva, Arial, Sans-serif" color=#6666ff size=2><EM>"Things are only free to the extent that you don't pay for them." </EM></FONT></P></DIV>
<DIV><FONT color=#33cc66></FONT></DIV>
<DIV></DIV>
<DIV></DIV>
<DIV></DIV>
<DIV></DIV>
<DIV></DIV>
<DIV></DIV>>From: "Andy Lacey" <ANDY@MINSTERSYSTEMS.CO.UK>
<DIV></DIV>>Reply-To: accessd@databaseadvisors.com
<DIV></DIV>>To: <ACCESSD@DATABASEADVISORS.COM>
<DIV></DIV>>Subject: RE: [AccessD] New ID/Autonumber value
<DIV></DIV>>Date: Thu, 8 May 2003 20:09:57 +0100
<DIV></DIV>>
<DIV></DIV>>:-) I like the "automagically"
<DIV></DIV>>But it's exactly why I can't see how it can work. How can @@Identity
<DIV></DIV>>know what field's value to return?
<DIV></DIV>>
<DIV></DIV>>Andy Lacey
<DIV></DIV>>http://www.minstersystems.co.uk
<DIV></DIV>>
<DIV></DIV>>
<DIV></DIV>>
<DIV></DIV>>
<DIV></DIV>>-----Original Message-----
<DIV></DIV>>From: accessd-bounces@databaseadvisors.com
<DIV></DIV>>[mailto:accessd-bounces@databaseadvisors.com] On Behalf Of Don Elliker
<DIV></DIV>>Sent: 08 May 2003 20:00
<DIV></DIV>>To: accessd@databaseadvisors.com
<DIV></DIV>>Subject: RE: [AccessD] New ID/Autonumber value
<DIV></DIV>>
<DIV></DIV>>
<DIV></DIV>>I have some question about this in multi-user, but I employed this
<DIV></DIV>>method in an A2K / DAO app and it works. In my Db the autonumber is the
<DIV></DIV>>PK. Do you mean automagically detect the fact that it's autonumber and
<DIV></DIV>>the PK? That's a cool idea.
<DIV></DIV>>_d
<DIV></DIV>>
<DIV></DIV>>
<DIV></DIV>>
<DIV></DIV>>
<DIV></DIV>>
<DIV></DIV>>"Things are only free to the extent that you don't pay for them."
<DIV></DIV>> >From: "Andy Lacey"
<DIV></DIV>> >Reply-To: accessd@databaseadvisors.com
<DIV></DIV>> >To:
<DIV></DIV>> >Subject: RE: [AccessD] New ID/Autonumber value
<DIV></DIV>> >Date: Thu, 8 May 2003 19:06:45 +0100
<DIV></DIV>> >
<DIV></DIV>> >Is @@Identity just an ADO thing? Or even a SQL Server thing?
<DIV></DIV>> >
<DIV></DIV>> >If I'm adding a record to a table in an MDB, and I'm doing it with an
<DIV></DIV>> >SQL statement (and there are, as we know, a few ways to do that), is
<DIV></DIV>> >there any way to retrieve the value of the autonumber field that is
<DIV></DIV>> >defined as the Primary Key? I suspect not, which is why I use DAO on
<DIV></DIV>> >many occasions because I need that value to, say, write to a related
<DIV></DIV>> >sub-record.
<DIV></DIV>> >
<DIV></DIV>> >Andy Lacey
<DIV></DIV>> >http://www.minstersystems.co.uk
<DIV></DIV>> >
<DIV></DIV>> >
<DIV></DIV>> >
<DIV></DIV>> >
<DIV></DIV>> >
<DIV></DIV>> >
<DIV></DIV>> >-----Original Message-----
<DIV></DIV>> >From: accessd-bounces@databaseadvisors.com
<DIV></DIV>> >[mailto:accessd-bounces@databaseadvisors.com] On Behalf Of Don Elliker
<DIV></DIV>> >Sent: 08 May 2003 14:12
<DIV></DIV>> >To: accessd@databaseadvisors.com
<DIV></DIV>> >Subject: RE: [AccessD] New ID/Autonumber value
<DIV></DIV>> >
<DIV></DIV>> >
<DIV></DIV>> >
<DIV></DIV>> >Forgive me being dense, but do you mean that @@Identity has to be used
<DIV></DIV>> >within a transaction (DAO) to get the right autonumber for the current
<DIV></DIV>> >record?
<DIV></DIV>> >
<DIV></DIV>> >thanks,
<DIV></DIV>> >
<DIV></DIV>> >_d
<DIV></DIV>> >
<DIV></DIV>> >
<DIV></DIV>> >
<DIV></DIV>> >
<DIV></DIV>> >
<DIV></DIV>> >
<DIV></DIV>> >"Things are only free to the extent that you don't pay for them."
<DIV></DIV>> >
<DIV></DIV>> > >From: "Haslett, Andrew"
<DIV></DIV>> > >Reply-To: accessd@databaseadvisors.com
<DIV></DIV>> > >To: "'accessd@databaseadvisors.com'"
<DIV></DIV>> > >Subject: RE: [AccessD] New ID/Autonumber value
<DIV></DIV>> > >Date: Thu, 8 May 2003 08:56:11 +0930
<DIV></DIV>> > >
<DIV></DIV>> > >It is safer as @@Identity is linked only to the connection (or
<DIV></DIV>> >transaction)
<DIV></DIV>> > >which created the record.
<DIV></DIV>> > >
<DIV></DIV>> > >Otherwise you are risking that another user will insert a record
<DIV></DIV>> >in-between
<DIV></DIV>> > >adding and retrieving the ID of the record added by the first user.
<DIV></DIV>> > >
<DIV></DIV>> > >I'm not sure if that is a risk using the other methods mentioned on
<DIV></DIV>> >this
<DIV></DIV>> > >list, however I *do* now that with @@Identity there is no risk of
<DIV></DIV>>this
<DIV></DIV>> > >happening.
<DIV></DIV>> > >
<DIV></DIV>> > >Cheers,
<DIV></DIV>> > >Andrew
<DIV></DIV>> > >
<DIV></DIV>> > >-----Original Message-----
<DIV></DIV>> > >From: Don Elliker [mailto:delliker@hotmail.com]
<DIV></DIV>> > >Sent: Thursday, 8 May 2003 5:22 AM
<DIV></DIV>> > >To: accessd@databaseadvisors.com
<DIV></DIV>> > >Subject: RE: [AccessD] New ID/Autonumber value
<DIV></DIV>> > >
<DIV></DIV>> > >
<DIV></DIV>> > >
<DIV></DIV>> > >How does this fare in a multi-user setup?
<DIV></DIV>> > >_D
<DIV></DIV>> > >
<DIV></DIV>> > >
<DIV></DIV>> > >
<DIV></DIV>> > >
<DIV></DIV>> > >
<DIV></DIV>> > >"Things are only free to the extent that you don't pay for them."
<DIV></DIV>> > >
<DIV></DIV>> > > >From: "Haslett, Andrew"
<DIV></DIV>> > > >Reply-To: accessd@databaseadvisors.com
<DIV></DIV>> > > >To: "'accessd@databaseadvisors.com'"
<DIV></DIV>> > > >Subject: RE: [AccessD] New ID/Autonumber value
<DIV></DIV>> > > >Date: Wed, 7 May 2003 10:28:09 +0930
<DIV></DIV>> > > >
<DIV></DIV>> > > >Sure.
<DIV></DIV>> > > >
<DIV></DIV>> > > >Create a table (table1) with two fields (ID: Autonumber, Field1:
<DIV></DIV>> >Text)
<DIV></DIV>> > > >
<DIV></DIV>> > > >Paste this code into a module and away you go:
<DIV></DIV>> > > >
<DIV></DIV>> > > >******************************************************************
<DIV></DIV>> > > >
<DIV></DIV>> > > >Sub TestIdentity()
<DIV></DIV>> > > >
<DIV></DIV>> > > > Dim conn As ADODB.Connection, rs As ADODB.Recordset
<DIV></DIV>> > > > Set conn = CurrentProject.Connection
<DIV></DIV>> > > >
<DIV></DIV>> > > > strSQL = "INSERT INTO table1 (field1) VALUES ('SomeValue')"
<DIV></DIV>> > > > conn.Execute strSQL
<DIV></DIV>> > > >
<DIV></DIV>> > > > strSQL = "SELECT @@Identity"
<DIV></DIV>> > > > Set rs = conn.Execute(strSQL)
<DIV></DIV>> > > >
<DIV></DIV>> > > > intID = rs.Fields.Item(0).Value
<DIV></DIV>> > > >
<DIV></DIV>> > > > Debug.Print intID
<DIV></DIV>> > > >
<DIV></DIV>> > > > rs.Close
<DIV></DIV>> > > > conn.Close
<DIV></DIV>> > > >
<DIV></DIV>> > > >End Sub
<DIV></DIV>> > > >
<DIV></DIV>> > > >******************************************************************
<DIV></DIV>> > > >
<DIV></DIV>> > > >Cheers,
<DIV></DIV>> > > >Andrew
<DIV></DIV>> > > >
<DIV></DIV>> > > >-----Original Message-----
<DIV></DIV>> > > >From: Stephen Bond [mailto:stephen@bondsoftware.co.nz]
<DIV></DIV>> > > >Sent: Wednesday, 7 May 2003 9:43 AM
<DIV></DIV>> > > >To: accessd@databaseadvisors.com
<DIV></DIV>> > > >Subject: RE: [AccessD] New ID/Autonumber value
<DIV></DIV>> > > >
<DIV></DIV>> > > >
<DIV></DIV>> > > >Andrew, just to complete the knowledge flowing freely here (great
<DIV></DIV>> >stuff),
<DIV></DIV>> > >if
<DIV></DIV>> > > >I've used a SQL statement to INSERT the new record, can I use
<DIV></DIV>> >@@Identity
<DIV></DIV>> > > >(and how)?
<DIV></DIV>> > > >
<DIV></DIV>> > > >Thanks
<DIV></DIV>> > > >Stephen Bond
<DIV></DIV>> > > >
<DIV></DIV>> > > >-----Original Message-----
<DIV></DIV>> > > >From: Haslett, Andrew [mailto:andrew.haslett@ilc.gov.au]
<DIV></DIV>> > > >Sent: Wednesday, 7 May 2003 10:43 a.m.
<DIV></DIV>> > > >To: 'accessd@databaseadvisors.com'
<DIV></DIV>> > > >Subject: RE: [AccessD] New ID/Autonumber value
<DIV></DIV>> > > >
<DIV></DIV>> > > >
<DIV></DIV>> > > >Jet 4.0 supports @@Identity. Therefore if you are inserting your
<DIV></DIV>> >record in
<DIV></DIV>> > > >code using ADO then it will work fine.
<DIV></DIV>> > > >
<DIV></DIV>> > > >Cheers,
<DIV></DIV>> > > >Andrew
<DIV></DIV>> > > >
<DIV></DIV>> > > >-----Original Message-----
<DIV></DIV>> > > >From: Arthur Fuller [mailto:artful@rogers.com]
<DIV></DIV>> > > >Sent: Wednesday, 7 May 2003 2:01 AM
<DIV></DIV>> > > >To: accessd@databaseadvisors.com
<DIV></DIV>> > > >Subject: RE: [AccessD] New ID/Autonumber value
<DIV></DIV>> > > >
<DIV></DIV>> > > >
<DIV></DIV>> > > >I don't think that works in MDBs, which I assume Stephen meant,
<DIV></DIV>>else
<DIV></DIV>> >he
<DIV></DIV>> > > >would have posted on the SQL list.
<DIV></DIV>> > > >
<DIV></DIV>> > > >Arthur
<DIV></DIV>> > > >
<DIV></DIV>> > > >-----Original Message-----
<DIV></DIV>> > > >From: accessd-bounces@databaseadvisors.com
<DIV></DIV>> > > >[mailto:accessd-bounces@databaseadvisors.com] On Behalf Of Haslett,
<DIV></DIV>>
<DIV></DIV>> >Andrew
<DIV></DIV>> > > >Sent: May 6, 2003 12:09 AM
<DIV></DIV>> > > >To: 'accessd@databaseadvisors.com'
<DIV></DIV>> > > >Subject: RE: [AccessD] New ID/Autonumber value
<DIV></DIV>> > > >
<DIV></DIV>> > > >
<DIV></DIV>> > > >SELECT @@IDENTITY
<DIV></DIV>> > > >
<DIV></DIV>> > > >-----Original Message-----
<DIV></DIV>> > > >From: Stephen Bond [mailto:stephen@bondsoftware.co.nz]
<DIV></DIV>> > > >Sent: Tuesday, 6 May 2003 12:23 PM
<DIV></DIV>> > > >To: accessd@databaseadvisors.com
<DIV></DIV>> > > >Subject: [AccessD] New ID/Autonumber value
<DIV></DIV>> > > >
<DIV></DIV>> > > >
<DIV></DIV>> > > >In this thread a couple of months ago there was a solution offered
<DIV></DIV>>to
<DIV></DIV>> >the
<DIV></DIV>> > > >problem of determining the value of an ID Autonumber of a record
<DIV></DIV>>just
<DIV></DIV>> >
<DIV></DIV>> > >added.
<DIV></DIV>> > > >
<DIV></DIV>> > > >
<DIV></DIV>> > > >It was stunning in its simplicity, so of course I didn't write it
<DIV></DIV>> >down ;-(
<DIV></DIV>> > > >
<DIV></DIV>> > > >Can anyone help?
<DIV></DIV>> > > >
<DIV></DIV>> > > >TIA
<DIV></DIV>> > > >
<DIV></DIV>> > > >Stephen Bond
<DIV></DIV>> > > >Otatara, New Zealand
<DIV></DIV>> > > >( tel 03 213 1256 fax 03 213 0123
<DIV></DIV>> > > >_______________________________________________
<DIV></DIV>> > > >AccessD mailing list
<DIV></DIV>> > > >AccessD@databaseadvisors.com
<DIV></DIV>> > > >http://databaseadvisors.com/mailman/listinfo/accessd
<DIV></DIV>> > > >Website: http://www.databaseadvisors.com
<DIV></DIV>> > > >
<DIV></DIV>> > > >IMPORTANT - PLEASE READ ********************
<DIV></DIV>> > > >This email and any files transmitted with it are confidential and
<DIV></DIV>>may
<DIV></DIV>> >
<DIV></DIV>> > > >contain information protected by law from disclosure.
<DIV></DIV>> > > >If you have received this message in error, please notify the
<DIV></DIV>>sender
<DIV></DIV>> > > >immediately and delete this email from your system.
<DIV></DIV>> > > >No warranty is given that this email or files, if attached to this
<DIV></DIV>> > > >email, are free from computer viruses or other defects. They
<DIV></DIV>> > > >are provided on the basis the user assumes all responsibility for
<DIV></DIV>> > > >loss, damage or consequence resulting directly or indirectly from
<DIV></DIV>> > > >their use, whether caused by the negligence of the sender or not.
<DIV></DIV>> > > >_______________________________________________
<DIV></DIV>> > > >AccessD mailing list
<DIV></DIV>> > > >AccessD@databaseadvisors.com
<DIV></DIV>> > > >http://databaseadvisors.com/mailman/listinfo/accessd
<DIV></DIV>> > > >Website: http://www.databaseadvisors.com
<DIV></DIV>> > > >
<DIV></DIV>> > > >_______________________________________________
<DIV></DIV>> > > >AccessD mailing list
<DIV></DIV>> > > >AccessD@databaseadvisors.com
<DIV></DIV>> > > >http://databaseadvisors.com/mailman/listinfo/accessd
<DIV></DIV>> > > >Website: http://www.databaseadvisors.com
<DIV></DIV>> > > >
<DIV></DIV>> > > >IMPORTANT - PLEASE READ ********************
<DIV></DIV>> > > >This email and any files transmitted with it are confidential and
<DIV></DIV>>may
<DIV></DIV>> >
<DIV></DIV>> > > >contain information protected by law from disclosure.
<DIV></DIV>> > > >If you have received this message in error, please notify the
<DIV></DIV>>sender
<DIV></DIV>> > > >immediately and delete this email from your system.
<DIV></DIV>> > > >No warranty is given that this email or files, if attached to this
<DIV></DIV>> > > >email, are free from computer viruses or other defects. They
<DIV></DIV>> > > >are provided on the basis the user assumes all responsibility for
<DIV></DIV>> > > >loss, damage or consequence resulting directly or indirectly from
<DIV></DIV>> > > >their use, whether caused by the negligence of the sender or not.
<DIV></DIV>> > > >_______________________________________________
<DIV></DIV>> > > >AccessD mailing list
<DIV></DIV>> > > >AccessD@databaseadvisors.com
<DIV></DIV>> > > >http://databaseadvisors.com/mailman/listinfo/accessd
<DIV></DIV>> > > >Website: http://www.databaseadvisors.com
<DIV></DIV>> > > >_______________________________________________
<DIV></DIV>> > > >AccessD mailing list
<DIV></DIV>> > > >AccessD@databaseadvisors.com
<DIV></DIV>> > > >http://databaseadvisors.com/mailman/listinfo/accessd
<DIV></DIV>> > > >Website: http://www.databaseadvisors.com
<DIV></DIV>> > > >
<DIV></DIV>> > > >IMPORTANT - PLEASE READ ********************
<DIV></DIV>> > > >This email and any files transmitted with it are confidential and
<DIV></DIV>>may
<DIV></DIV>> >
<DIV></DIV>> > > >contain information protected by law from disclosure.
<DIV></DIV>> > > >If you have received this message in error, please notify the
<DIV></DIV>>sender
<DIV></DIV>> > > >immediately and delete this email from your system.
<DIV></DIV>> > > >No warranty is given that this email or files, if attached to this
<DIV></DIV>> > > >email, are free from computer viruses or other defects. They
<DIV></DIV>> > > >are provided on the basis the user assumes all responsibility for
<DIV></DIV>> > > >loss, damage or consequence resulting directly or indirectly from
<DIV></DIV>> > > >their use, whether caused by the negligence of the sender or not.
<DIV></DIV>> > > >_______________________________________________
<DIV></DIV>> > > >AccessD mailing list
<DIV></DIV>> > > >AccessD@databaseadvisors.com
<DIV></DIV>> > > >http://databaseadvisors.com/mailman/listinfo/accessd
<DIV></DIV>> > > >Website: http://www.databaseadvisors.com
<DIV></DIV>> > >
<DIV></DIV>> > > _____
<DIV></DIV>> > >
<DIV></DIV>> > >Help STOP SPAM with the new MSN 8 and
<DIV></DIV>> > >get 2 months FREE*
<DIV></DIV>> > >
<DIV></DIV>> > >
<DIV></DIV>> > >IMPORTANT - PLEASE READ ********************
<DIV></DIV>> > >This email and any files transmitted with it are confidential and may
<DIV></DIV>>
<DIV></DIV>> > >contain information protected by law from disclosure.
<DIV></DIV>> > >If you have received this message in error, please notify the sender
<DIV></DIV>> > >immediately and delete this email from your system.
<DIV></DIV>> > >No warranty is given that this email or files, if attached to this
<DIV></DIV>> > >email, are free from computer viruses or other defects. They
<DIV></DIV>> > >are provided on the basis the user assumes all responsibility for
<DIV></DIV>> > >loss, damage or consequence resulting directly or indirectly from
<DIV></DIV>> > >their use, whether caused by the negligence of the sender or not.
<DIV></DIV>> > >_______________________________________________
<DIV></DIV>> > >AccessD mailing list
<DIV></DIV>> > >AccessD@databaseadvisors.com
<DIV></DIV>> > >http://databaseadvisors.com/mailman/listinfo/accessd
<DIV></DIV>> > >Website: http://www.databaseadvisors.com
<DIV></DIV>> >
<DIV></DIV>> > _____
<DIV></DIV>> >
<DIV></DIV>> >Tired of spam? Get advanced junk
<DIV></DIV>> >mail protection with MSN 8.
<DIV></DIV>> >
<DIV></DIV>> >_______________________________________________
<DIV></DIV>> >AccessD mailing list
<DIV></DIV>> >AccessD@databaseadvisors.com
<DIV></DIV>> >http://databaseadvisors.com/mailman/listinfo/accessd
<DIV></DIV>> >Website: http://www.databaseadvisors.com
<DIV></DIV>>
<DIV></DIV>>
<DIV></DIV>>
<DIV></DIV>>STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
<DIV></DIV>>
<DIV></DIV>>_______________________________________________
<DIV></DIV>>AccessD mailing list
<DIV></DIV>>AccessD@databaseadvisors.com
<DIV></DIV>>http://databaseadvisors.com/mailman/listinfo/accessd
<DIV></DIV>>Website: http://www.databaseadvisors.com
<DIV></DIV></div><br clear=all><hr>Add photos to your e-mail with <a href="http://g.msn.com/8HMZENUS/2746??PS=">MSN 8.</a> Get 2 months FREE*.</html>