Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Re: Last Insert ID in MS Access

by sflitman (Hermit)
on Jul 17, 2010 at 07:03 UTC ( #850065=note: print w/ replies, xml ) Need Help??


in reply to Last Insert ID in MS Access

You may be able to run

SELECT @@IDENTITY FROM MyTable;
and pass that through to the underlying table. I found it by googling for MS Access and last insert id. One would think the DBD::ODBC documentation should help too, but I found it a bit opaque on this topic. In many advanced SQL dialects you can also say something like
INSERT INTO MyTable (x,y) VALUES (1,2) RETURNING rowid;
Then retrieve the rowid by fetching from the statement handle, if the DBI driver permits this.

HTH,
SSF


Comment on Re: Last Insert ID in MS Access
Select or Download Code
Replies are listed 'Best First'.
Re^2: Last Insert ID in MS Access
by james2vegas (Chaplain) on Jul 17, 2010 at 07:29 UTC
    This article suggests that the correct syntax is SELECT @@IDENTITY, and that you should open a second connection to get it. This is presumably problematic with multiple users, so it is as effective (and unreliable) as MAX(ID) would be. I think you could probably lock the table for update and unlock the table after getting the last id.

      I think you'll find it doesn't work if run in a "second connection". I think you just need to ensure it is run as a new statement in the same connection after any outstanding commit.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://850065]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (10)
As of 2015-07-08 06:15 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (94 votes), past polls