Run-time error '-2147352567 (80020009)' No Current Record MySQL back-end DAO recordset

Question

I upgraded back-end MS Access 2003 to MySQL 5.1 of a database. I am linking the backend MYSQL 5.1 database thr' ODBC (MySQL ODBC 5.1 Driver) to MS Access front-end .mdb.

I am using DAO recordset. I add new record by using .AddNew method and update by using

.Update method; after update statement I fetch autonumber field into variable which gives

"Run-time error '-2147352567 (80020009)' No Current Record " error.

But same code works in previous version which has MS-Access 2003 back end.

'new 
if bNew  = true then
    lngInvoiceID = 0
else 'edit , 
    lngInvoiceID = Forms("frmInvoice").[tbInvoiceID].value
end if


Set rstAux = dbsLocal.OpenRecordset("Select * from tblElectronicInvoices where
eipID = " & lngInvoiceID, dbOpenDynaset, dbSeeChanges)

rstAux.AddNew

rstAux.Update
lngInvoiceID = Nz(rstAux.Fields("eipID"), 0)

'Getting No current record error when I try to get back eipID which is autonumber field.

Previous MS Access code has access back-end linked table to front-end. The option of dbSeeChanges was not specified and before the update statement, I could get new ID of autonumber field.

Answer 1

I encountered the same situation several years ago after moving a back-end database from Access to MySQL (keeping the front-end in Access). I wound up using the following workaround:

Dim cdb As DAO.Database, qdf As DAO.QueryDef, rst As DAO.Recordset
Dim lngCustomerID As Long
Set cdb = CurrentDb

' create pass-through query to insert new row and then retrieve the IDENTITY value
Set qdf = cdb.CreateQueryDef("")
qdf.Connect = cdb.TableDefs("customers").Connect  ' get .Connect from existing linked table
qdf.ReturnsRecords = False
qdf.SQL = "INSERT INTO customers (customerName) VALUES ('GordCo')"
qdf.Execute
qdf.ReturnsRecords = True
qdf.SQL = "SELECT LAST_INSERT_ID()"
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
lngCustomerID = rst(0).Value
Debug.Print "LAST_INSERT_ID() returned " & lngCustomerID
rst.Close
Set rst = Nothing
Set qdf = Nothing