MySQL Bugs: #36535: #Deleted occurs in Transaction in MS Access

Bug #36535	#Deleted occurs in Transaction in MS Access
Submitted:	6 May 2008 14:05	Modified:	8 May 2008 8:26
Reporter:	Vladimir Dusa	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.24,5.1.4	OS:	Windows
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	#DELETED, Microsoft Access, transaction

Description:
Hello,

I have a problem with adding new records in Transaction in MS Access. I have made a form based on instructions described on http://support.microsoft.com/kb/248011. There is described how to edit records in a transaction in order to do possible rollback all changes when closing the form.

When I try this with a form based on MS Access table, all works fine. I can add/delete/modify records and then commit/rollback them. But in a form based on MySQL linked table works only deletion/editing of records. When I add a new record, #Deleted occures instead of the record.

Thank you very much for your help.

Best regards

Vladimír Duša

How to repeat:
I add an example to this bug. This zip file contains example mysql database with one table and example MS Access database containing two forms. One shows how it works with MS Access table, the second shows how it works with MySQL table. You can test it by adding a new record in the form and then going to another one - a message occurs where you answer if the record should be saved or not. The table tbl_test_mysql in the example should be probably linked again on your machine.

Suggested fix:
Example of form based on MySQL table should work same as the form based on Access table (i.e. #Deleted shouldn't occur after adding new record in the form)

test.zip contains mysql database and test.mdb conaining example of the bug.

Attachment: test.zip (application/force-download, text), 16.25 KiB.

Thank you for the bug report. Could you please read:
http://dev.mysql.com/doc/refman/5.0/en/myodbc-errors.html#qandaitem-26-1-7-3-4.

Thank you for a problem report. What exact version of MySQL server do you use? Please, try to repeat with a newer version of Connector/ODBC, 3.51.25, and inform about the results.

Hello I have already tried it with MyODBC 5.01.04 (this information has been truncated from the field Version of this bug). My MySQL Server version is 5.0.45.

Problem still occures.

Best Regards

Vladimir Dusa

Vladimir, have you read http://dev.mysql.com/doc/refman/5.0/en/myodbc-errors.html#qandaitem-26-1-7-3-4? Have you done everything asked there to avoid this problem?

Yes - all points from http://dev.mysql.com/doc/refman/5.0/en/myodbc-errors.html#qandaitem-26-1-7-3-4? are fullfilled. If you look in the attached file test.zip, there is an example of this bug. The table is linked to the form not in a standard way as usually (in the form properties), but in the Form_Open is set the recordset and begins a transaction. This is done so, because of editing the whole form in transaction (so it is possible to rollback all changes in the form even if there is some subform (http://support.microsoft.com/kb/248011)). In test.zip you can find everything to check this problem out.

Thank you very much

Best regards

Vladimir Dusa

Vladimir, testing your problem I came to this conclusion:
 - There is no need for Access forms, problem is repeatable with linked table as well
 - There is no problem with updates as they are, the problem is with text field. This is known to us, please see bugs db.

If you do "alter table tbl_test_mysql change Text Text VARCHAR(50);" your problem will go away... If there is a bug here it should be in fact that I limited column size to signed 32bit range but test still fails.

I tested with MyODBC 5.1.4 against MySQL server 5.0.58.

3.51.25GA shows same error.

After reviewing Changelogs and BugsDB I'm setting this report to "Verified". Provided SQL script is enough for repeating the problem at least with Access 2003. At least, we should add problem with TEXT fields to our manual.

I am sorry to have to say, that it still doesn't work. I have changed the type of the 'Text' field from TEXT to VARCHAR(50). But #Deleted still appears after adding new row. I have tested it with MyODBC 3.51.18 and 5.1.4. I think that the problem is because of the transaction in the form:

Private Sub Form_Open(Cancel As Integer)
    Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset(Me.RecordSource, dbOpenDynaset)
    Set Me.Recordset = rs
    
    DBEngine.BeginTrans
End Sub

in Form_Unload and Form_Current comes a question if the changes should be commited or rollbacked. Please open my example in the attachment and try it there.

Thank you very much

Best regards

Vladimir Dusa

Vladimir, you are right. It shows #DELETED no matter what field type is used... Did you tried varying dbOpenDynaset parameter?

I ran into this same problem (inserted rows showing as #DELETED) and I fixed it by removing a default value of "Now()" that I had specified in Access for a date field in the subform.  This might not directly relate to Vladimir's problem, but might be helpful to others researching this issue.

Verified for MyODBC 3.51.27 and 5.1.8