asked on

Access VBA - SELECT @@IDENTITY question

I have the following code that is fired from an Access form's button's click event:

---------------------------
strSQL = "INSERT INTO tblProductLineRules (ProductLineID, RuleSeq) VALUES (" & _
lngProductLineId & "," & _
lngRuleSeq & "); SELECT @@IDENTITY"

ProductLineRuleID = doCmd.RunSQL(strSQL)
---------------------------

this code produces an error - "Expected Function or Variable". Apparently, doCmd.RunSQL cannot return a value...

So I changed the code as follows:

---------------------------
Set conn = New ADODB.Connection
conn.ConnectionString = CurrentProject.BaseConnectionString
conn.Open
cconn.Execute strSQL
lngProductLineRuleId = conn.Execute("SELECT @@Identity")
conn.Close
Set conn = Nothing
---------------------------

But conn.Open errors out. The message is "The database has been placed in a state by user 'Admin' on machine [machinename] that prevents it from being opened or locked."

Any ideas?

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

axiomllc

ASKER

Thanks for the quick response. That appears to work. However, I just found out that the following code works great:

Dim daoDB As DAO.Database
Dim daoRS As DAO.Recordset

strSQL = "INSERT INTO tblProductLineRules (ProductLineID, RuleSeq) VALUES (" & _
lngProductLineId & "," & _
lngRuleSeq & ")"

Set daoDB = DBEngine(0)(0)
daoDB.Execute strSQL

Set daoRS = daoDB.OpenRecordset("SELECT @@IDENTITY")
lngProductLineRuleId = daoRS(0)
daoRS.Close

Richard Quadling

If you intend to port your code to work with an MS SQL server, then @@IDENTITY will work, but SCOPE_IDENTITY() is better as you MAY have insert triggers which insert other rows and with @@IDENTITY, it would be the id of the rows inserted by the trigger that you got, not YOUR row.