last id of auto-increment field

[Eitan M]

Hello,
How can I find the last indentity of auto-increment field ?
(If I inserted a record, then the auto-increment field is build
automatically,
and I want to find its value).

Thanks :)

[Tom van Stiphout]

On Wed, 24 May 2006 16:17:36 +0200, "Eitan M"
<no_spam_please@nospam_please.com> wrote:

Depends on how you are inserting. How?

-Tom.

Hello,
How can I find the last indentity of auto-increment field ?
(If I inserted a record, then the auto-increment field is build
automatically,
and I want to find its value).

Thanks :)

[arthurjr07]

Let say this is the structure of your table
ID,Field1,Field2,Field3 and ID is the
auto incremented field.
INSERT INTO
TABLENAME(Field1,Field2,Field3)
VALUES(xxx,yyy,zzz)

after passing insert statement above,
you can query the ID of that record
by
SELECT ID FROM TABLE
WHERE
Field1 = xxx and
Field2 = yyy and
Field3 = zzz

HTH

[Tom van Stiphout]

On 24 May 2006 20:21:17 -0700, ar********@gmail.com wrote:

Only if those 3 fields refer to a unique record. That's why I asked
for details. OP could be doing a rs.AddNew, or enter a record
interactively, or ...

-Tom.

Let say this is the structure of your table
ID,Field1,Field2,Field3 and ID is the
auto incremented field.
INSERT INTO
TABLENAME(Field1,Field2,Field3)
VALUES(xxx,yyy,zzz)

after passing insert statement above,
you can query the ID of that record
by
SELECT ID FROM TABLE
WHERE
Field1 = xxx and
Field2 = yyy and
Field3 = zzz

HTH

[arthurjr07]

what if those 3 fields didnt refer to a unique record?

If that would b the case then he use this instead

SELECT MAX(ID) FROM TABLE
WHERE
Field1 = xxx and
Field2 = yyy and
Field3 = zzz

but what if 2 users inserted same record at the same time,
in that case i give up. I dont have anymore idea how to get that
ID.

if he is using Recordset, after he pass an update method he can sort
that
recordset DESC with respect to that autoincremented field. He is very
sure
that the last record is the last added record.

[Eitan M]

Isn't any global field, such as last_identity (something simmiliar is in
sql-server) ?

Thanks :)

[arthurjr07]

in sql server you can retrieve the ID of the
newly added record using
@@IDENTITY,
SCOPE_IDENTITY or
IDENT_CURRENT

but those function dont have
equivalent in Access.

[rkc]

Tom van Stiphout wrote:

On 24 May 2006 20:21:17 -0700, ar********@gmail.com wrote:

Only if those 3 fields refer to a unique record. That's why I asked
for details. OP could be doing a rs.AddNew, or enter a record
interactively, or ...

If all the fields in a record do not make it unique then what
difference would it make which record with those three values was retrieved?

[Lyle Fairfield]

ar********@gmail.com wrote in news:1148550848.023918.189660
@g10g2000cwb.googlegroups.com:

in sql server you can retrieve the ID of the
newly added record using
@@IDENTITY,
SCOPE_IDENTITY or
IDENT_CURRENT

but those function dont have
equivalent in Access.

Jet OLE DB Provider Version 4.0 Supports SELECT @@Identity

A simple way to use this is to INSERT with a distinct ADODB Connection.

Sub temp()
Dim c As ADODB.Connection
Set c = New ADODB.Connection
With c
..ConnectionString = CurrentProject.BaseConnectionString
..Open
..Execute "INSERT INTO [Order Details] " _
& "(OrderID, ProductID, UnitPrice, Quantity, Discount) " _
& "Values (12, 23, 15.5, 12, 3/1000)"
Debug.Print .Execute("SELECT @@Identity").Collect(0)
' prints the autonumber of the newly inserted record
..Close
End With
Set c = Nothing
End Sub

--
Lyle Fairfield

[arthurjr07]

Lyle is correct.