<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>Message</TITLE>
<META content="MSHTML 6.00.2800.1170" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=640490218-08052003>Is
@@Identity just an ADO thing? Or even a SQL Server thing?</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=640490218-08052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=640490218-08052003>If I'm
adding a record to a table in an MDB, and I'm doing it with an SQL statement
(and there are, as we know, a few ways to do that), is there any way to retrieve
the value of the autonumber field that is defined as the Primary Key? I
suspect not, which is why I use DAO on many occasions because I need that value
to, say, write to a related sub-record.</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=640490218-08052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=640490218-08052003><!-- Converted from text/plain format -->
<P><FONT size=2>Andy Lacey<BR><A
href="http://www.minstersystems.co.uk/">http://www.minstersystems.co.uk</A><BR><BR></FONT></P></SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=640490218-08052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=640490218-08052003></SPAN></FONT> </DIV>
<BLOCKQUOTE
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com] <B>On Behalf Of </B>Don
Elliker<BR><B>Sent:</B> 08 May 2003 14:12<BR><B>To:</B>
accessd@databaseadvisors.com<BR><B>Subject:</B> RE: [AccessD] New
ID/Autonumber value<BR><BR></FONT></DIV>
<DIV>
<DIV>
<P>Forgive me being dense, but do you mean that @@Identity has to be used
within a transaction (DAO) to get the right autonumber for the current
record?</P>
<P>thanks,</P>
<P>_d<BR><BR></P></DIV><BR><BR><BR>
<DIV></DIV>
<DIV><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></DIV>
<DIV><FONT color=#33cc66></FONT></DIV>
<DIV></DIV>
<DIV></DIV>
<DIV></DIV>
<DIV></DIV>
<DIV></DIV>
<DIV></DIV>>From: "Haslett, Andrew" <ANDREW.HASLETT@ILC.GOV.AU>
<DIV></DIV>>Reply-To: accessd@databaseadvisors.com
<DIV></DIV>>To: "'accessd@databaseadvisors.com'"
<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
transaction)
<DIV></DIV>>which created the record.
<DIV></DIV>>
<DIV></DIV>>Otherwise you are risking that another user will insert a
record 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 this
<DIV></DIV>>list, however I *do* now that with @@Identity there is no risk
of 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: 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 stuff),
<DIV></DIV>>if
<DIV></DIV>> >I've used a SQL statement to INSERT the new record, can I
use @@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 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, else 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, 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 to the
<DIV></DIV>> >problem of determining the value of an ID Autonumber of a
record just
<DIV></DIV>>added.
<DIV></DIV>> >
<DIV></DIV>> >
<DIV></DIV>> >It was stunning in its simplicity, so of course I didn't
write it 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 may
<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>> >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 may
<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>> >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 may
<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>>Help STOP SPAM with the new MSN <HTTP: g.msn.com 8HMIENUS
2731??PS="">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>>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><BR clear=all>
<HR>
Tired of spam? Get <A href="http://g.msn.com/8HMIENUS/2734??PS=">advanced junk
mail protection</A> with MSN 8. </BLOCKQUOTE></BODY></HTML>