Ugh. I found a minimally impacting solution way, but it is rather kludgy.
I read the last suggestion in this URL:
http://dev.mysql.com/doc/mysql/en/odbc-and-last-insert-id.html
And gave it a whirl... and... viola. It seems that the commit (via recordset.update method) doesn't refresh some relevant local variable with a auto_inc PK value. Instead, and totally counter intuitively, the server must be asked to return the record with "NULL" for its PK. The recordset object will then point to the newly inserted record, and magically, its PK value is not NULL, but rather the last insertion Id. That was no fun.
I wonder though, if this will work properly in a multi-user environment with multiple insert operations occuring concurrently. Whereas the Last_Inserted_Id() function may be specific to a open session, the SQL statement "SELECT * from tblFOO where PK IS NULL" cannot have such scope, and may return more than one tuple...
I'll test this and post my findings. In the meanwhile, here's a VBA snippet:
Set rstTarget = New ADODB.Recordset
rstTarget.CursorLocation = adUseClient
strSQLTarget = "Select * from " & linkedTableName & " Where 1 = 2"
rstTarget.Open strSQLTarget, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
' Do insert operation
rstTarget.AddNew
.... update some field values ....
rstTarget.Update
rstTarget.Close
' Retrieve last inserted auto_inc PK
strSQLTarget = "Select * from " & linkedTableName & " Where " & PK_Field_Name & " IS NULL"
rstTarget.Open strSQLTarget, CurrentProject.Connection, adOpenStatic, adLockReadOnly
last_inserted_id = rstTarget(PK_Field_Name)
.... crack open a cold one ....
Does anyone know if Connector/ODBC 3.53 (aka ODBC 5.0) will address these chunky bits?
-Jake
"I conclude that there are two ways of constructing a software design: One way is to make it so simple that there are obviously no deficiencies and the other way is to make it so complicated that there are no obvious deficiencies." - C. A. R. Hoare