how to get the autonumber value after inserting a record into an access db

[Aussie Rules]

Hi,

I have an access 2007 table that has an autonumber.

Is it possible to get that value back as apart of a insert command ?

Dim OleCommand As New OleDb.OleDbCommand

OleCommand.CommandType = CommandType.Text
OleCommand.Connection = dbConnection

OleCommand.CommandText = "Insert into projects (ProjectName, Months,
Description, etc
OleCommand.ExecuteNonQuery()

 

[zacks]

Hi,

I have an access 2007 table that has an autonumber.

Is it possible to get that value back as apart of a insert command ?

        Dim OleCommand As New OleDb.OleDbCommand

        OleCommand.CommandType = CommandType.Text
        OleCommand.Connection = dbConnection

        OleCommand.CommandText = "Insert into projects (ProjectName, Months,
Description,   etc
        OleCommand.ExecuteNonQuery()

As far as I am aware, Access has no means to get that value directly.
You will have to get it by runnng a select query on the table after
the insert, ordering in descending order by the identity column and
get the value of the identity column from the first row in the
resultset.

 

[rowe_newsgroups]

Hi,

I have an access 2007 table that has an autonumber.

Is it possible to get that value back as apart of a insert command ?

Dim OleCommand As New OleDb.OleDbCommand

OleCommand.CommandType = CommandType.Text
OleCommand.Connection = dbConnection

OleCommand.CommandText = "Insert into projects (ProjectName, Months,
Description, etc
OleCommand.ExecuteNonQuery()

This article mentions that @@Identity is supported by JET like it is
with SQL Server.

http://support.microsoft.com/kb/815629

P.S. I found that link using the first stop for finding answers,
Google.



Thanks,

Seth Rowe [MVP]