Get the next ID - Access 2000

[thamilton44]

I'm sure that this topic is in here somewhere, but I couldn't find it.

What is the best way to get the next ID in a table when adding a record? I am
using a MS Access 2000 database. I need to add a record to one table and get
the autonumber ID to add to a record in another table. I've done this before
in Oracle using NEXTVAL, but this doesn't work in Access.

Thanks in advance!

[paross1]

Since you are using autonumber with Access for your ID field, one way to get
its value is to perform a query immediately after your insert (in a separate
CFQUERY) to retrieve your ID value.

SELECT MAX(ID_field)
FROM your_table....etc.

It would be a good idea to enclose both cfquiries within cftransaction tags in
order to guarantee that the ID value that you get was from your insert
statement, especially if you have a busy site.

Phil

[Mountain Lover]

In addition to what Phil said...(enclose the queries in cftransaction)
the second query can also use: SELECT @@IDENTITY AS LatestID
HTH,

--
Tim Carley
[url]www.recfusion.com[/url]
[email]info@NOSPAMINGrecfusion.com[/email]

[paross1]

I know SELECT @@IDENTITY AS LatestID works in SQL Server, but I don't think that this is valid in Access, unless something has changed since I last used Access 97.

Phil

[Mountain Lover]

It works in Access 2k+...upgrade your version of Access Phil ;-)

But don't believe me...try it! ;-)

--
Tim Carley
[url]www.recfusion.com[/url]
[email]info@NOSPAMINGrecfusion.com[/email]

[paross1]

Yeah, I used to have an application that used Access 97, but I ported it to SQL
Server 2k, so I never felt the need to upgrade to Access 2k. Now it is strictly
Oracle and SQL Server, but I do still have Access 97around to test my answers
on this forum, just to make sure that I'm not blowing smoke.... :)

Anyway, that is nice to know, and it looks like MS is making an attempt to
better align Access with SQL Server.

Phil

[thamilton44]

Thank you all for your responses. I think the cftransaction is exactly what I was looking for. I'm new to ColdFusion. :)

[OldCFer]

I know I ran some tests once to see if cftransaction actually guaranteed that
the
max(id) returned following an insert was correct. I think, however, that I
tried it on
Access 97 which probably used blunt force locking of the entire table to
handle
transaction processing, so it worked. I've subsequently tested using MS SQL
and since the locking is much more granular, I've found this is not so. The DB
has no reason to prevent two (almost) simultaneous inserts, so the returned
Max(ID) value could be wrong. It would be a good idea, if you're using Max(ID)
to
include enough criteria, that you just used in the insert, so you get your Max
ID.

[Another Phil]

Try looking at this post:


[url]http://www.macromedia.com/cfusion/webforums/forum/messageview.cfm?catid=7&thread[/url]
id=978371

There was a similar problem, though I think that was with MySQL the backend
shouldn't matter as the theory is the same.