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>