Returning Row id of newly inserted record ???

Hi all,

i have an application with an Informix database backend, and in my app i allow the user to save an address record.

like so:

VB Code:

SQL = "INSERT INTO Addresses (add_addr,add_postcode,add_country,add_tel,add_fax,add_dx1,add_dx2,add_desc,Add_type_id)" & _
 "Values('" & Addr & "','" & Postcode & "','" & Country & "','" & Phone & "','" & Fax & "','" & DX1 & "','" & DX2 & "','" & AddDesc & "','" & adtyp & "')"

this works fine, but then i need to link my newly inserted address record with another table and so for this i have a LINK table in which i insert the address_id and the other table id.

Obviously at this point i do not know the address_id of the new record, and i need to return it from the database.

This is what is causing me a problem !!!!!!!

i have spoken to the informix guy at my work and he says that you can run a command similar to SQL SERVER's '@@Identity' and get the row id this way, which in turn will allow me to select the address_id.

The command is - 'sqlca.sqlerrd[2]'

now to the bit i am having difficulty with, i can't open a recordset to get the row id as says i have a syntax error, i suspect that this is because i am not using the SELECT Keyword.

Is thier a way to just run a command against the database through ADO , and make a variable equal to it or something like that. Or does anybody know of a better way to go about this ???

I am well and trully stumped !!!

I have never used Informix but did you try to run

strSQL = "Select sqlca.sqlerrd[2]"