By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,995 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

last id of auto-increment field

P: n/a
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 :)
May 24 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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 :)


May 24 '06 #2

P: n/a
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

May 25 '06 #3

P: n/a
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


May 25 '06 #4

P: n/a
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.

May 25 '06 #5

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

Thanks :)
May 25 '06 #6

P: n/a
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.

May 25 '06 #7

P: n/a
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?
May 25 '06 #8

P: n/a
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
May 25 '06 #9

P: n/a
Lyle is correct.

May 25 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.