Select @@Identity returning 0 for linked table in Access

Question

I am using Access 2007 and have some linked tables to a mySQL database. I am using DAO to insert a record into a mySQL linked table and trying to retrieve the inserted PK using Select @@identity, but that select is returning 0.

  Dim sql As String

  Dim oDB As Database
  Set oDB = CurrentDb 

  sql = "INSERT INTO Quotes ( CustomerID ) SELECT 1 AS Expr1;" 

  oDB.Execute sql 

  If oDB.RecordsAffected <> 1 Then 
    MsgBox "cannot create new quote"
    Exit Function
  End If

  Dim rsNewID As DAO.Recordset
  Set rsNewID = oDB.OpenRecordset("SELECT @@IDENTITY")  ' Create a recordset and SELECT the new Identity

  Dim intNewID As Long
  intNewID = rsNewID(0).Value ' Store the value of the new identity in variable intNewID
  'This value is 0, why?

I've seen another question like this, that has not been satisfactorily answered for me

Answer 1

I have not used mysql. So, translate what I say for mysql.

Is CustomerID an identity column (i.e. does it generate ID on its own)?
If so, use the function that returns the last generated ID.

@@Identity function is what people use in SQL Server. I don''t know of equivalent in mysql.
See this

Looking at the code above, you need not open the 2nd recordset. The rsNewID1 should help you get the last inserted ID.

Hope this helps.

Answer 2

How do I get the IDENTITY / AUTONUMBER value for the row I inserted?

Answer 3

Hi,
In MySQL its:

SELECT LAST_INSERT_ID()

Answer 4

fredrik gets partial credit, for the mySQL statement. It's important to note that I am using DAO, so statements are processed by the JET engine and it does not support this statement. This needs to be run as a pass through query in Access in order to work though. After I made a pass through query with fredrik's select statement, that did the trick. I called this Access passthrough query from my DAO code and it worked.