Insert statement returning a value

[HebieBug]

Is there a way of inserting a row using the insert SQL statement and then returning the value.
Other then using a Stored procedure in SQL or opening a recordset and moving to the last record?

 

[jonscott8]

I'm not quite sure I understand what you mean by "and then returning the value", but if by that you meant return the autonumber(Access) or identity(SQLServer) value generated by the database, then you can query the @@Identity variable with SQLS and Access2K.

oConn.Open myConnString
oConn.Execute sMyInsert, nCount
IF nCount > 0 THEN
Set oRS = oConn.Execute("SELECT @@IDENTITY&quot
MsgBox "Value is " & oRS.Fields(0).value
ELSE
MsgBox "Wasnt able to Insert"
END IF Jon Hawkins

 

[andrey]

You can get any values you want, if you write stored procedure and use Command object with one or more parameters defined as Returned or Output.

 

[HebieBug]

Found quite a nice way of doing it

Dim ncount As Integer
Dim SQLInsertString As String
Dim lors As Variant
Dim llID As Integer
dim connection as new adodb.connection
' set up the connection
''inserts new Record based on users selection on form
SQLInsertString = "SET NOCOUNT ON;INSERT INTO TABLENAME (FIELDNAME1, FIELDNAME2, FIELDNAME3) VALUES ('" & Me.TxtAddress.Text & "','" & Me.LstPostSearch & "','0');SELECT @@IDENTITY AS NewID;"

' Execute the SQL statement
Set lors = Connection.Execute(SQLInsertString)

' Get the inserted ID
llID = lors.Fields("NewID&quot.Value
MsgBox llID
' Close the connection
Set loConn = Nothing