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

Bug #36535	#Deleted occurs in Transaction in MS Access
Submitted:	6 May 2008 16:05	Modified:	8 May 2008 10:26
Reporter:	Vladimir Dusa
Status:	Verified
Category:	Connector/ODBC	Severity:	S3 (Non-critical)
Version:	3.51.24,5.1.4	OS:	Microsoft Windows
Assigned to:		Target Version:
Tags:	Microsoft Access, transaction, #DELETED
Triage:	D3 (Medium)

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.