How to Last Inserted rows AutoNumber value

[jack123]

Hi All,

I am having a table with one of the Column as SerialNo whose datatype is Autonumber...

whenever I Insert a row I want to get the SerialNo of that row..

In Sql Server , there is @@Identity so if we want last inserted row Serialno we write like this...

Select @@Identity

I want to ask What is the Equivalent of @@Identity in MS ACCESS 2007.

Please If any one knows the solution ..help me out

Thanks in Advance..

 

[Galaxiom]

Assuming you use Increment Autonumbers the last serial number will be:
DMax("IDField", "YourTable")

 

[pbaldy]

I wouldn't count on DMax in a multiuser environment. Good info here and in the other link I posted:

http://www.access-programmers.co.uk/forums/showthread.php?t=169609

 

[LPurvis]

Not to circumvent Paul's links (I'm sure they're worth reading anyway...)
But when you say:
>> "I want to ask What is the Equivalent of @@Identity in MS ACCESS 2007"

Have you tried using just that in a Jet query? ;-)
(If necessity is the mother of invention then surely so is experimentation to discovery ;-)

Cheers.

 

[LPurvis]

Just wondering...
Perhaps, if you've already attempted it, you've misinterpreted the error message you're getting.
Jet is a single statement query engine.
You can't perform more than one action in any query.
So while, in SQL Server, you might have performed

INSERT INTO TableName (FKID, FieldA) VALUES (1,'Alpha')
SELECT @@Identity

that will fial in Jet (and ACE as you're using), not because @@Identity isn't supported but because it's a separate statement.
You'd need to issue the @@Identity lookup after the insert - in a separate query.
The database/connection scope is the same - so the value is persisted.

Jet/ACE doesn't support other alternatives though - such as Scope_Identity().
That's uniquely T-SQL syntax.

Cheers.