get the last inserted record number

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

emmim44 - 29 Sep 2004 19:15 GMT

Any one know how to get the last inserted record number in access 2000.
In database table I have a column called UN number which is automatically
generated by access.
My statement is as follow:

<cfquery name="in" datasource="#vector#">

INSERT INTO EO127()
VALUES( some values)

</cfquery>

Reply to this Message

RayT - 29 Sep 2004 19:19 GMT

Select the max value after the insert is accomplished.

<cfquery name="in" datasource="#vector#">
select max(yourfield)
from yourtable
</cfquery>

Reply to this Message

blewis - 29 Sep 2004 19:30 GMT

Just be careful with MAX(). If you have a high transaction rate, then you
have a good chance of getting a bad ID back. If you do an insert, and then
someone else does an insert before you are able to run your SELECT MAX() query,
then you get back the wrong ID.

In Access 2000, I have been told that if you use the Jet 4.0 ODBC or higher it
will support the @@IDENTITY syntax like MS SQl Server does. May want to look
into that.

Bryan

Reply to this Message

Webmaster Forum / ColdFusion / Advanced Techniques / September 2004

get the last inserted record number