Selecting last record ID in Access

[Rottwild_One]

Good day everyone.

I'm having a problem selecting the ID (autonumbered primary key in table) from
the last record inserted in MS Access. I've searched the forums looking for
ways to do this and attempted the following:

SELECT LAST_INSERT_ID() AS strOrderID
This returns a "Undefined function" error as I believe this function is for
MYSQL

I've also found and tried:
SELECT @@identity AS strOrderID

This does not return an error but returns 0 as the ID. This tells me this is
on the right track, but I'm missing something.

Thanks in advance.....

[TSB]

<cfquery ...>
Insert blah blah blah
</cfquery>

<cfquery ...>
select max(myIDCol) from myTable
</cfquery>

Trevor
[url]http://www.burnette.us[/url]

[Rottwild_One]

Trevor,

Thanks for the quick response. This option does work but I've read several
places it is possible to return the wrong ID if multiple users are adding
records (as unlikely it would seem), which is why I did not try this approach.

After reading my subject line again though, your post is EXACTLY what I asked
for. I guess I should have asked how do I return the ID of a record being
inserted?

I can use this method for development at this point, but I'll need to figure
out how to get the ID when inserting the record. Is there a SELECT NEXT or
SELECT NEW that would allow me to return the ID?

Thanks again Trevor.......

[philh]

There are a number of approaches for non-identity database engines. The one I
lived with for a while (until we thankfully upgraded to SQL Server) was to add
a GUID field to the table, stuff it with CF's CreateUUID() function, and then
SELECT the ID number for the record with that GUID value.

HTH,

[Rottwild_One]

Phil,

Thanks for the response. I've thought about generating a UID and using that
for the ID field itself instead of using Access autonumbering (I'd have to
check the implications of doing such), or like you suggested, using it to
retrieve the ID. That may be the only way to do it if we can't retrieve the ID
during the insert.

Thanks again everyone.

[Donnettacb98]

I use both the max ID and compare the fields that make it unique otherwise (if
there are any).
For example, I might have a product table and I don't allow duplicate Product
Titles. So I would do the max(ID) where product_title = '#Product_Title#'. The
GUID is probably the most reliable way though if you don't have anything other
than the ID that might make it unique.

[lifeinkorea]

Use TSB's example inside <cftransaction> </cftransaction>.

[Dan Bracuk]

cftransaction won't help. If you are limited to using select max(), make sure
you make your where clause as extensive as possible.

If you think you still might bring back the wrong record because of high
activity, store something about who entered the record (the IP address for
example) and include that in your where clause.