Retrieve AutoNumber value of last inserted row in MySQL database from MS Access always returns 0

Question

I am trying to get the AutoNumber ID from the most recently entered record in Table X. I was originally using an MS Access database for the backend, and the following VBA code inserted the row and retrieved the correct ID:

Dim db As DAO.Database
Set db = CurrentDb
Dim qdf As DAO.QueryDef
Dim strSql As String
Dim id as long

strSql = "INSERT INTO Table_X (colA, colB, colC)" _
        & " VALUES ([colA_val], [colB_val], [colC_val])"

Set qdf = db.CreateQueryDef(vbNullString, strSql)

With qdf
    .Parameters("colA_val").Value = colAVal
    .Parameters("colB_val").Value = colBVal
    .Parameters("colC_val").Value = colCVal
    .Execute dbFailOnError
End With

id = db.OpenRecordset("SELECT @@IDENTITY")(0)

However, I moved the MS Access backend to a MySQL backend, and connected the MS Access frontend to it using ODBC. Now, this code runs without errors, but it returns 0 every time for the ID. I manually checked the entry in the Table_X autonumber ID field, and the ID is set to 800 (for example).

Why is it that id = db.OpenRecordset("SELECT @@IDENTITY")(0) no longer returns the proper ID after I switched from an MS Access backend to a MySQL backend?

I have tried this as well: id = qdf.OpenRecordset("SELECT @@IDENTITY")(0) but that gives me the following error:

3421: Data type conversion error.

In summary: how can I get the AutoNumber field for the most recently inserted record in an MS Access frontend connected to a MySQL backend through ODBC?

PS. I have tried the following link: Autonumber value of last inserted row - MS Access / VBA but the DAO method is what I am currently using and is not working.

**EDIT: **

From the link above, I have tried using ADO as well as DAO to insert the records and return the AutoNumber ID. Inserting the records works every time, which means that the connection is successful. However, retrieving the AutoNumber of the last inserted record does not work for both methods. Here is the ADO method I used:

cn.Execute "INSERT INTO TheTable.....", , adCmdText + adExecuteNoRecords
Set rs = cn.Execute("SELECT @@Identity", , adCmdText)
Debug.Print rs.Fields(0).Value

Like I said, the insert works for ADO and DAO so I know that the connection is valid.

The accepted answer in that referenced question shows ADO recordset option. Did you try it? — June7, Mar 19 '21 at 02:04
@June7 My question is more concerned with why is the current solution failing with DAO. Also, the more upvoted answer uses DAO instead of ADO which is more what I'm referring to. I will see if ADO works but that won't solve the core issue. — Michael, Mar 19 '21 at 03:22
Does this answer your question? [How can I get the last insert value from MySQL database and update a field in Access?](https://stackoverflow.com/questions/52001604/how-can-i-get-the-last-insert-value-from-mysql-database-and-update-a-field-in-ac) — June7, Mar 19 '21 at 03:42

score 0 · Answer 1 · answered Mar 19 '21 at 08:08

Don't know about MySQL, but this works for Access and SQL Server:

Dim Records As DAO.Recordset
Dim Id      As Long

Set Records = CurrentDb.OpenRecordset("Select Top 1 * From Table_X")

With Records
    .AddNew
        !colA.Value = colAVal
        !colB.Value = colBVal
        !colC.Value = colCVal
    .Update
    .Bookmark = .LastModified
    Id = !Id.Value
    .Close
End With

Retrieve AutoNumber value of last inserted row in MySQL database from MS Access always returns 0

1 Answers1