Access and MySQL - Lock Issue

[Hap007]

Hi,

I have a strange problem.

I have an MS Access db link to a MySQL backend using ODBC link. Seems to work fine.

If I open a table and add or change a record in a table, all works fine.

If I open a table and edit a field and move off the record, the data saves fine.

If I open a table and edit a field and then remove the text I entered, when I move off the record, Access reports that Me and another user have edited the same data and I can not save.

Seems that editing and then removing the edit before moving off the record causes this error. I am guessing since the data is not different, something is causing this message. In VBA code, I retrieve a record, .edit, move space to a text field that is already blank, then on the .update (get error) the message error number = 3197.

Anybody have any info on this issue?

I will post on tha Access forum also.

Thanks,
Hap...



Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[Hap007]

Hi,

I finally figured this out, so I am sharing the solution in case anyone else is having the same problem.

To solve this issue, you must change a flag in the MySQL ODBC DNS connection property.

In OBDC Configure: Pick Details>Flags 1 > and check
'Return Matched rows instead of affected rows' to true.

This solves the Write Error where record appears changed by another user but it really is not.

Hope This Helps somebody,
Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121