Insert query to return values? 2

[Crowley16]

I'm guessing this is not possible but wanted to check and perhaps someone has a good work around...

basically, have a table with a autonumber pk and using generated sql insert statements to insert records, what I'd like to do is to extract the automatically created autonumber made by the insert statement, is it possible to make the query itself return it?

--------------------
Procrastinate Now!

 

[lupins46]

No you can't do that.

If you run your insert query in code then one option is to use DMax() to find the highest id in the table as the next statement.
It isn't foolproof if you have several users entering records at the same time.

 

[RoyVidar]

With Jet 4.0, I'd go for select @@identity on Access backends, i e execute the select on the same connection as the insert

[tt]dim rs as adodb.recordset
dim cn as adodb.connection

set cn=currentproject.connection
cn.execute "<your insert>",,adcmdtext+adexecutenorecords
set rs=cn.execute("select @@identity",,adcmdtext)
debug.print rs.fields(0).value[/tt]

- the (d)max thingies has a possibility of bombing if more than one user inserts at the same time...

Roy-Vidar

 

[Crowley16]

the dmax thing definitely isn't secure enough, I had considered searching for the record with the values used in the insert statement, but too many things could go wrong with that...

hmm, that is very interesting roy..., never thought about using the connecton.execute...

but even with this method, if I'm running a loop of inserts would it be possible for the return to go wrong?

--------------------
Procrastinate Now!

 

[RoyVidar]

If you look it up in what is so nicely referred to as BOL in the SQL server fora, the @@identity is supposed to give the last identity (SQL server term - here autonumber) created on the connection - so it shouldn't go wrong ;-)

I haven't used it with multiple inserts, so I wouldn't know. Best advice I can give, is to test. Would you then be inserting first in the parent table, then in a child table through a loop? I think it should work...

Just a note, if you're using SQL server/MSDE - consider scope_identity in stead.

If you're more famililar with DAO, there's surely methods of using it there too, since it's not an ADO feature.

Roy-Vidar

 

[Crowley16]

that seems to get the job done...

I had a look at dao but there isn't anything like that for the execute command, or at least none that I can find...

--------------------
Procrastinate Now!