EasyCFM.COM ColdFusion Forums / MS Access Related Issues / @@IDENTITY in MS ACCESS

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: @@IDENTITY in MS ACCESS -- page: 1 2

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

Cugel
11-26-2004 @ 5:09 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 12
Joined: Aug 2004

Hello everybody,

I've read the tutorial from pablo about retrieving the number of the last inserted record.  My question is : is @@IDENTITY working if my database is under ms access 2000 ?

lutra
11-26-2004 @ 7:38 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 55
Joined: Aug 2003

Hi,
I guess you have the answer in the comment section of the same tutorial. In any case this work just fine for me under Access:


SELECT MAX(id_something) AS new_id FROM table

Cugel
11-26-2004 @ 7:48 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 12
Joined: Aug 2004

Thx,

I will try your solution...

mcsolas
02-02-2005 @ 11:48 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 138
Joined: Sep 2003

Unfortunately @@IDENTITY is only something you can call using SQL server, not access hosted DB's.

Too bad, I was really happy to read that until I tried it and saw the notes.

CJ
02-03-2005 @ 9:21 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4262
Joined: Oct 2002

i've not tried it, but i've been told (on the cf-talk mailing list) that Access 2000 -does- support the @@identity variable.

can anyone give it a shot and confirm?



-CJ-
@ #coldfusion/DALNet
http://charlie.griefer.com

http://www.freemacmini.com/?r=154208  <-- show CJ some love! Smile  

Lola
02-23-2005 @ 1:11 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 363
Joined: Feb 2005

mysql now supports the @@identity value. It's been confirmed.

Mysql's last_insert_id() and @@identity, both return the last incremented value of a INSERT or UPDATE.

mcsolas
02-23-2005 @ 9:36 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 138
Joined: Sep 2003

I can confirm that I tried and tried then reread the tutorial and realized that it wasn't working in MS access 2000!

I then had posted * somewhere * and confirmed that it wasn't working. ( at least for me )

With computers, results may vary Wink

Scarecrow
04-16-2005 @ 12:48 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 109
Joined: Oct 2004

You can definately use @@Identity in ms access 2000 and above.

Ken

www.krcaldwell.com

mcsolas
04-20-2005 @ 11:14 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 138
Joined: Sep 2003

If you say so but I couldn't make it work ..

I ended up wrapping the 2 queries: insert and then the select max(id) in a cftransaction tag.

Later I realized that this is a superior coding methology in some ways as it makes that block of code platform independent.

Lola
04-24-2005 @ 1:11 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 363
Joined: Feb 2005

But now you will be dealing with performance loss, mcsolas. If 2 people submit that form at the same time, the second person has to wait until the first person finishes executing both of those queries.

@@identity is user-independent. Meaning, it doesn't go back to the database to look for the last inserted id. That means there is no chance for you to get somebody else's id using @@identity. And it is alot faster, you are using 1 query instead of 2, and you don't have to wait in line for somebody else to finish what they are doing.

Coldfusion optimization strategies and benchmarks:
http://cfsource.blogspot.com/

PAGE: 1 2

Website Designed and Developed by Pablo Varando.