[AccessD] New ID/Autonumber value

Andy Lacey andy at minstersystems.co.uk
Thu May 8 13:06:45 CDT 2003


Is @@Identity just an ADO thing? Or even a SQL Server thing?
 
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.
 
Andy Lacey
http://www.minstersystems.co.uk <http://www.minstersystems.co.uk/> 



 
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Don Elliker
Sent: 08 May 2003 14:12
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] New ID/Autonumber value



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?

thanks,

_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: Thu, 8 May 2003 08:56:11 +0930 
> 
>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 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. 
>_______________________________________________ 
>AccessD mailing list 
>AccessD at databaseadvisors.com 
>http://databaseadvisors.com/mailman/listinfo/accessd 
>Website: http://www.databaseadvisors.com 

  _____  

Tired of spam? Get advanced junk  <http://g.msn.com/8HMIENUS/2734??PS=>
mail protection with MSN 8. 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030508/8bbe0f54/attachment-0001.html>


More information about the AccessD mailing list