Stuart McLachlan
stuart at lexacorp.com.pg
Thu May 8 17:05:21 CDT 2003
@@Identity is a system wide variable. It returns the last autonumber
generated by the system. You certainly can't rely on it to return a
particular record number in either a multi user system or in a system
where you are inserting into more than one table with autonumbers
keys in the same transaction.
On 8 May 2003 at 9:11, Don Elliker wrote:
>
> 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 mail protection with MSN 8.
--
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System
Support.