MySQL Forums
Forum List  »  Microsoft Access

Re: DAO/Access2k/MySQL5 rs.addnew #DELETED#
Posted by: Jake Yazici
Date: July 14, 2005 06:19PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: DAO/Access2k/MySQL5 rs.addnew #DELETED#
2548
July 14, 2005 06:19PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.