CODEWITHSUNDEEP
sqlms-accessadodb
bugmagnet
bugmagnet

Reputation: 7769

How to retrieve autonumbered field via ADODB

Context: MS Access and JScript

I have a table into which I insert one field's worth of data so that the autonumber will fire and give me that record's unique id, viz

oConn.Execute( "INSERT INTO tbl (DateTimeStamp) VALUES (" + newNow + ");");

in this case, newNow is a Double value which is automagically coerced into a Date.

Once that's done, I would like to retrieve the autonumbered ID field which should have been filled at the point of INSERT. Up until now I've been using ADODB.Recordset for this stuff, but have been experimenting with ADODB.Connection's Execute() in the hope that it might be faster.

Upvotes: 1

Views: 190

Answers (2)

HansUp
HansUp

Reputation: 97101

With oConn as an ADODB.Connection, in VBA the following is convenient to retrieve the last autonumber value inserted from that connection. See if that translates to JScript.

oConn.Execute("SELECT @@Identity")(0)

Rather than automagically casting the double value to Date/Time, you can ask the db engine to do it explicitly.

"INSERT INTO tbl (DateTimeStamp) VALUES (CDate(" + newNow + "));"

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91326

You can use @@Identity with MS Access against your connection.

SELECT @@identity

Upvotes: 0

Related Questions