Can you do "INSERT .... RETURNING...."

[UnhandledException]

Does Access allow the use of a RETURNING clause?

I am trying to set up a Parent Child relationship.
When a new record is inserted into the Parent table, I now have to run a SELECT to fetch the newly created sequence number. Then a 2nd Insert is done to the Child table.

This would be 3 SQL statements, but if I could do an INSERT into the Parent table with a RETURNing clause to return the primary key field then I can simply do a 2nd INSERT into the Child table without the extra SELECT.

Is this possible with Access 2007?

 

[Banana]

Where did you pick up that RETURNING clause? That has to be a dialect from a certain vendor, a one I've never seen before.

A close approximate would be "SELECT @@AUTO_IDENTITY;", which is supported in Jet 4.0 (3.5? It's quite recent, as I recall).

 

[UnhandledException]

Yeah, I wasn't expecting the RETURNing to have global usage for all database but was hoping Access had something similar, but it appears an extra SELECT will have to be done.

 

[Banana]

Well, yes, but I do believe it won't incur another round-trip, so there's that.

I'm not quite sure how reliable it is, though in context of concurrency. There should be a KB article discussing this if you google for it.

I'm still curious where did you pick up the 'RETURN' clause from?

 

[UnhandledException]

I'm still curious where did you pick up the 'RETURN' clause from?

I have used it within an Oracle Database.