Haslett, Andrew
andrew.haslett at ilc.gov.au
Wed May 7 18:26:11 CDT 2003
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.
Cheers,
Andrew
-----Original Message-----
From: Don Elliker [mailto:delliker at hotmail.com]
Sent: Thursday, 8 May 2003 5:22 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] New ID/Autonumber value
How does this fare in a multi-user setup?
_D
"Things are only free to the extent that you don't pay for them."
>From: "Haslett, Andrew"
>Reply-To: accessd at databaseadvisors.com
>To: "'accessd at databaseadvisors.com'"
>Subject: RE: [AccessD] New ID/Autonumber value
>Date: Wed, 7 May 2003 10:28:09 +0930
>
>Sure.
>
>Create a table (table1) with two fields (ID: Autonumber, Field1: Text)
>
>Paste this code into a module and away you go:
>
>******************************************************************
>
>Sub TestIdentity()
>
> Dim conn As ADODB.Connection, rs As ADODB.Recordset
> Set conn = CurrentProject.Connection
>
> strSQL = "INSERT INTO table1 (field1) VALUES ('SomeValue')"
> conn.Execute strSQL
>
> strSQL = "SELECT @@Identity"
> Set rs = conn.Execute(strSQL)
>
> intID = rs.Fields.Item(0).Value
>
> Debug.Print intID
>
> rs.Close
> conn.Close
>
>End Sub
>
>******************************************************************
>
>Cheers,
>Andrew
>
>-----Original Message-----
>From: Stephen Bond [mailto:stephen at bondsoftware.co.nz]
>Sent: Wednesday, 7 May 2003 9:43 AM
>To: accessd at databaseadvisors.com
>Subject: RE: [AccessD] New ID/Autonumber value
>
>
>Andrew, just to complete the knowledge flowing freely here (great stuff),
if
>I've used a SQL statement to INSERT the new record, can I use @@Identity
>(and how)?
>
>Thanks
>Stephen Bond
>
>-----Original Message-----
>From: Haslett, Andrew [mailto:andrew.haslett at ilc.gov.au]
>Sent: Wednesday, 7 May 2003 10:43 a.m.
>To: 'accessd at databaseadvisors.com'
>Subject: RE: [AccessD] New ID/Autonumber value
>
>
>Jet 4.0 supports @@Identity. Therefore if you are inserting your record in
>code using ADO then it will work fine.
>
>Cheers,
>Andrew
>
>-----Original Message-----
>From: Arthur Fuller [mailto:artful at rogers.com]
>Sent: Wednesday, 7 May 2003 2:01 AM
>To: accessd at databaseadvisors.com
>Subject: RE: [AccessD] New ID/Autonumber value
>
>
>I don't think that works in MDBs, which I assume Stephen meant, else he
>would have posted on the SQL list.
>
>Arthur
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Haslett, Andrew
>Sent: May 6, 2003 12:09 AM
>To: 'accessd at databaseadvisors.com'
>Subject: RE: [AccessD] New ID/Autonumber value
>
>
>SELECT @@IDENTITY
>
>-----Original Message-----
>From: Stephen Bond [mailto:stephen at bondsoftware.co.nz]
>Sent: Tuesday, 6 May 2003 12:23 PM
>To: accessd at databaseadvisors.com
>Subject: [AccessD] New ID/Autonumber value
>
>
>In this thread a couple of months ago there was a solution offered to the
>problem of determining the value of an ID Autonumber of a record just
added.
>
>
>It was stunning in its simplicity, so of course I didn't write it down ;-(
>
>Can anyone help?
>
>TIA
>
>Stephen Bond
>Otatara, New Zealand
>( tel 03 213 1256 fax 03 213 0123
>_______________________________________________
>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.
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>_______________________________________________
>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.
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>_______________________________________________
>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.
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
_____
Help STOP SPAM with the new MSN <http://g.msn.com/8HMIENUS/2731??PS=> 8 and
get 2 months FREE*
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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030508/41196d88/attachment-0001.html>