This is probably a simple question, but I’m not having much luck finding an answer. Using ODBC how do you get the last record ID of a record that was inserted?
You have to call the db-engine-specific function to return that value. Eg., for sqlite,
rs= db.sqlselect("select last_insert_rowid") id = rs.idxfield(1).integervalue
Okay. This is SQL Server so I know how to do that.
A little late but it can serve With Sql Server 2008 >=
dim r as RecordSet = mdb.SQLSelect(“Insert Into dbo.Item (Description, Unit_Id) OUTPUT INSERTED.iItemId Values(‘Item’, 1)”)
I am not certain that they can be used with ODBC, but on SQL Server, SELECT @@IDENTITY and SELECT SCOPE_IDENTITY exist for the purpose that you describe. SCOPE_IDENTITY is limited to the context of the current transaction, and @@identity is connexion wide if I remember correctly.
That’s right, the definition of @@IDENTITY and SCOPE_IDENTITY,
Can be done in several ways, this is shorter.