ADO 2.5 INSERT Statement (VB6)

Evil Phil

Folks
I'm running an insert statement into an Access database using ADO 2.5. What I want to know is can I return values if I use the Connection classes Execute statement. For example:

I have a table, Jobs, consisting of these fields:

JobID - AutoNum, primary key
Description - Text
JobDate - Date/Time

and I want to do the following*

sqlString = "INSERT INTO Jobs (Description, JobDate) " 
sqlstring = SQLSTRING & "VALUES ('BLAH', SOMEDATE)"

with objConnection
    .BeginTrans
    .Execute sqlString '// This is the bit I'm interested in
    .CommitTrans
end with

When I execute the insert statment is there anyway I can get the value of the WorkItemID field returned from the Execute method? At present it returns a closed recordset which is no use to me.

I know I could use the AddNew method on a recordset to achieve these results, but I was just wondering.


*Example code only, probably full of errors

yop

You are doing an INSERT so you will not get back values from the recordset.
You have to do an explicit SELECT to get back the field

Evil Phil

What I was looking for the the equivalent of Oracle's RETURNING clause. Can't find one, musn't be one.

boo-boo

Not with Access, at least not Access '97 for sure.

You could include the select statement within the transaction,
& then get the select (MAX) id (if the rows are auto incrementing)
though, but this is a bit of a hack.

Evil Phil

I've no problems with a bit of a hack, I'll give it a go. Does anybody know if there is something like a RETURNING clause with MySQL? I'll look it up anyway but I'll be lazy for now ...

McGintyMcGoo

Why don't you create a recordset to return a set of data?