Andy Lacey
andy at minstersystems.co.uk
Fri May 9 01:53:00 CDT 2003
Thanks for the explanation Stuart.
Andy Lacey
http://www.minstersystems.co.uk
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Stuart McLachlan
> Sent: 08 May 2003 23:05
> To: accessd at databaseadvisors.com
> Subject: RE: [AccessD] New ID/Autonumber value
>
>
> @@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.
>
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>