Andy Lacey
andy at minstersystems.co.uk
Thu May 8 14:09:57 CDT 2003
:-) I like the "automagically"
But it's exactly why I can't see how it can work. How can @@Identity
know what field's value to return?
Andy Lacey
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 20:00
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] New ID/Autonumber value
I have some question about this in multi-user, but I employed this
method in an A2K / DAO app and it works. In my Db the autonumber is the
PK. Do you mean automagically detect the fact that it's autonumber and
the PK? That's a cool idea.
_d
"Things are only free to the extent that you don't pay for them."
>From: "Andy Lacey"
>Reply-To: accessd at databaseadvisors.com
>To:
>Subject: RE: [AccessD] New ID/Autonumber value
>Date: Thu, 8 May 2003 19:06:45 +0100
>
>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
>
>
>
>
>
>
>-----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
>mail protection with MSN 8.
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*