QODBC for MS Access doesn't support QSqlQuery::lastInsertId

[BrokenVoodooDoll]

I work with MS Access database (.accdb) and I need to insert some data and then get the last inserted ID. When I use QSqlQuery::lastInsertId method I see the following error:
"QODBCResult::lastInsertId: not implemented for this DBMS" Error: ""
I tried to use SELECT @@IDENTITY and similar expressions (SCOPE_IDENTITY and IDENTITY_CURRENT), but I get the same error.
Some time ago I used SELECT @@IDENTITY in VBA and that worked. So it's not about MS Access, but the driver?
Of course, I can write SELECT MAX(ID) FROM table, but I'm not sure it's safe.
Is there a way to get the last inserted ID working with MS Access database?

[Christian Ehrlicher]

@BrokenVoodooDoll said in QODBC for MS Access doesn't support QSqlQuery::lastInsertId:

So it's not about MS Access, but the driver?

Yes, it's the odbc driver which does not support it

Of course, I can write SELECT MAX(ID) FROM table, but I'm not sure it's safe.

Correct, but if you're the only one working on this 'database' then it's ok

Is there a way to get the last inserted ID working with MS Access database?

only with the solution you posted above

[JonB]

@BrokenVoodooDoll
I would expect you to use SELECT @@IDENTITY as you said. You say "but I get the same error", yet the error message QODBCResult::lastInsertId is from QSqlQuery::lastInsertId so how can that relate to SELECT @@IDENTITY?

[Kent-Dorfman]

ODBC has always about supporting the most common functionality of SQL in a platform independent manner. It is not surprising that it won't handle an M$ specific extention. Do it in the most transportable way, as is the intention of ODBC...and it will be safe if you follow transaction isolation rules.

[Christian Ehrlicher]

@Kent-Dorfman said in QODBC for MS Access doesn't support QSqlQuery::lastInsertId:

and it will be safe if you follow transaction isolation rules.

No, it won't. It will only work if there is only one connection and if the query does not update insert more than one record.

[JonB]

@Christian-Ehrlicher said in QODBC for MS Access doesn't support QSqlQuery::lastInsertId:

if the query does not update more than one record

update -> insert ?

[Christian Ehrlicher]

@JonB Thx, fixed

[Kent-Dorfman]

@Christian-Ehrlicher

Given the example, he's asking if the single SQL is atomic, and yes it is. Furthermore, if it exists within an isolated transaction block
BEGIN
SQL
SQL
SQL
END
then it's protected, depending upon the rules in effect for transaction isolation.

[Christian Ehrlicher]

@Kent-Dorfman No. If there is a stored procedure or something else which inserts something else somewhere it won't work.
I did not say that it does not work at all - I say that there a subtle cases where it does not work and therefore the recommendation to simply use it is plain wrong.

[BrokenVoodooDoll]

Finally, it turned out, that a separate query with SELECT @@IDENTITY works (which is quite strange for me as I thought, that lastInserdId executes exactly the same query). I don't know how come I haven't noticed that. So one can do query.exec("INSERT ..."); and in the next line query.exec("SELECT @@IDENTITY");. But also I found out that SCOPE_IDENTITY and CURRENT_IDENTITY don't work. Thank you all for your help!