getting latest id from insert - ado 2

[RiazKhanmohamed]

Hi,

I want to (in code) get my connection to insert a row into table 'capture', and then add rows into my many to many table 'capture_species' using the id created in capture just now.

I've seen references to methods like dlast, but what i want to know is: within the session, is there no way to get the last insert id? kinda like mysql's mysql_insert_id() is what i'm looking for if at all

if dlast is my best/only bet then fine, but i'd have thought there'd be a better solution to it than that, especially within a single transaction

not sure posting the code i'm using will help, but here it is:

    If IsNull(capture_id) Then
        SQL = "INSERT INTO Capture (site_id, male, female, unspecific) VALUES (" & site_id & "," & male & "," & female & "," & unspecific & ")"
    Else
        SQL = "UPDATE Capture SET male=" & male & ", female=" & female & ", unspecific=" & unspecific & " WHERE id = " & capture_id
    End If
    
    Dim pcnn As ADODB.Connection
    Set pcnn = CurrentProject.Connection

On Error GoTo SQL_Err
    pcnn.BeginTrans

    pcnn.Execute (SQL)
    'want capture id from insert for next statement 
    save_species (capture_id)

thank in advance, annoyed it's not as simple as i thought, will try dlast in the meantime

 

[Remou]

How about:

<...>
    SQL = "Select @@identity as NewID from Capture"
    
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open SQL, pcnn, 3, 3
    
    capture_id = rs.Fields("NewID")

http://lessthandot.com

 

[RiazKhanmohamed]

man i should have spotted that one! thanks a million. now onto images maps :s

 

[RoyVidar]

Or even

[tt]Set rs = pcnn.execute("SELECT @@identity",,adcmdtext)
capture_id = rs.fields(0).value[/tt]

Roy-Vidar

 

[RiazKhanmohamed]

similar, i didn't use creatobject, the sql twigged me. i used:

    If IsNull(capture_id) Then
       Dim rs As ADODB.Recordset
       Set rs = pcnn.Execute("Select @@identity as NewID from Capture")
       capture_id = rs.Fields("NewID")
    End If