my database (Access 2000) having two tables "table 1" and "table 2" both 
having AutoNumber fields. When I am using "Select @@Identity" fo find out 
ID of newly inserted record, How does sytem know which table (it refers to?

****"Select @@idetity from table1" giving me an error;

I know I can try this "Select MAX(ID) From table 1" Where ID is a number 
field (integer)

You use @@identity immediately after an insert.  I
assume that's how it knows.

So if you were inserting in to 2 tables, you would
need to use @@identity twice if you wanted both ID's.

Mark
--- Chirag Shah <chiragiit@y...> wrote:
> my database (Access 2000) having two tables "table
> 1" and "table 2" both 
> having AutoNumber fields. When I am using "Select
> @@Identity" fo find out 
> ID of newly inserted record, How does sytem know
> which table (it refers to?
> 
> ****"Select @@idetity from table1" giving me an
> error;
> 
> I know I can try this "Select MAX(ID) From table 1"
> Where ID is a number 
> field (integer)
> 
> 


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

*******************************************************
You use @@identity immediately after an insert.  I
assume that's how it knows.

So if you were inserting in to 2 tables, you would
need to use @@identity twice if you wanted both ID's.

Mark
*********************************************************

OK..Folks! does any one know best way to find ID of last inserted record? 
There are scores of articles out there eg. 4guys and adopenstatic.com

but I want to know ID (which is an Access Auto Number field) of the last 
inserted record so that I can increment new record ID.  (Not necessarily 
*immediately* after inserting, may be after two days)

I am having two table and both having an auto number field.

"Select @@ Identity from mytable1" Does not work..problem could be "From 
mytable 1" part
 
Does "Select MAX(ID) AS MAXID From Table" is only way to go..?

Regards,
*********************************************************

@@IDENTITY returns the last inserted ID field on a CONNECTION _NOT_ a
table.

Cheers,
Owen

-----Original Message-----
From: Chirag Shah [mailto:chiragiit@y...] 
Sent: Wednesday, February 05, 2003 7:23 PM
To: ASP Databases
Subject: [asp_databases] Select @@ Identity


my database (Access 2000) having two tables "table 1" and "table 2" both

having AutoNumber fields. When I am using "Select @@Identity" fo find
out 
ID of newly inserted record, How does sytem know which table (it refers
to?

****"Select @@idetity from table1" giving me an error;

I know I can try this "Select MAX(ID) From table 1" Where ID is a number

field (integer)

If you want to get the most recent ID, but not
immediately after the insert, you cannot use
@@identity.

Mark.
--- Chirag Shah <chiragiit@y...> wrote:
>
*******************************************************
> You use @@identity immediately after an insert.  I
> assume that's how it knows.
> 
> So if you were inserting in to 2 tables, you would
> need to use @@identity twice if you wanted both
> ID's.
> 
> Mark
>
*********************************************************
> 
> OK..Folks! does any one know best way to find ID of
> last inserted record? 
> There are scores of articles out there eg. 4guys and
> adopenstatic.com
> 
> but I want to know ID (which is an Access Auto
> Number field) of the last 
> inserted record so that I can increment new record
> ID.  (Not necessarily 
> *immediately* after inserting, may be after two
> days)
> 
> I am having two table and both having an auto number
> field.
> 
> "Select @@ Identity from mytable1" Does not
> work..problem could be "From 
> mytable 1" part
>  
> Does "Select MAX(ID) AS MAXID From Table" is only
> way to go..?
> 
> Regards,
>
*********************************************************


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

Please read the documentation:

@@IDENTITY returns the last created autonumber value on the current
connection.
It does not return the highest autonumber value in your table.

SELECT MAX(myAutonumberField) will return the highest value. However, if you
have just inserted a record, and you want to know what autonumber value was
just generated, then you use

' Example using 2 tables
'
strSQL = "INSERT INTO Table1 (Field1) VALUES ('myValue')"
objConn.Execute strSQL,,adCmdText+adExecuteNoRecords
'
strSQL = "SELECT @@IDENTITY"
intTable1ID = objConn.Execute(strSQL,,adCmdText).Fields(0).Value
'
strSQL = "INSERT INTO Table2 (Field1) VALUES ('myValue')"
objConn.Execute strSQL,,adCmdText+adExecuteNoRecords
'
strSQL = "SELECT @@IDENTITY"
intTable2ID = objConn.Execute(strSQL,,adCmdText).Fields(0).Value
'
objConn.Close
Set objConn = Nothing
'
Response.Write("The autonumber for table 1 is: " & intTable1ID & "<br>")
Response.Write("The autonumber for table 2 is: " & intTable2ID & "<br>")

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Chirag Shah" <chiragiit@y...>
Subject: [asp_databases] Re: Select @@ Identity


: *******************************************************
: You use @@identity immediately after an insert.  I
: assume that's how it knows.
:
: So if you were inserting in to 2 tables, you would
: need to use @@identity twice if you wanted both ID's.
:
: Mark
: *********************************************************
:
: OK..Folks! does any one know best way to find ID of last inserted record?
: There are scores of articles out there eg. 4guys and adopenstatic.com
:
: but I want to know ID (which is an Access Auto Number field) of the last
: inserted record so that I can increment new record ID.  (Not necessarily
: *immediately* after inserting, may be after two days)
:
: I am having two table and both having an auto number field.
:
: "Select @@ Identity from mytable1" Does not work..problem could be "From
: mytable 1" part
:
: Does "Select MAX(ID) AS MAXID From Table" is only way to go..?
:
: Regards,
: *********************************************************
.

Ken Schafer, Thank You.