Access form not saving coorectly attached to MySQL

[madvalpreston]

Hi

Not sure if this an access forum question or MySQL so i willt ry and post in both.

I have a MySql databse which is linked through ODBC conncetions. I am using access forms for front end users.

I have a form with a subform. If I fill in data into the main form when I enter save (I also ahve added a save button but have same problem) it is filling all the fields up with
#Deleted. However if I close the form and go back in the record is saved. Any ideas how to stop this happening.

In the sub form when i try and add a new record it is giving me
ODBC--insert on alinkedtablesites failed. it is linked exaclty likethe main form and I have checked the table in the MySql but cannot see where the problem may be.... any ideas

Many thanks

 

[DonQuichote]

I think this is the automagic actions of MS-Access that somehow do something wrong. I cannot help you there. Better also ask in an Access forum.

However, I can give you one tip: you can enable the query log on the MySQL server and see what queries are actually sent by MS-Access and the ODBC driver. You could try to issue these queries by hand if they look right, and see what error messages you get.


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[madvalpreston]

Hi

Could you advise how I set such a log and monitor it, I am fairly new to Mysql

Many Thanks

 

[Prattaratt]

I found out that this is caused by DAO's Locking and execution modes. I found out it was easier to execute write functions with an ADO connection instead.

 

[madvalpreston]

Hi

I think I found the solution. I noticed that the text fields created in Mysql were coming through and making themselves memo fied types in access. I changed the fields to Varchar() and then re-applied them in access. It now works without and saves without the delete#......

Thanks for all your replies

 

[Hap007]

If you are editing records in the Access Frontend and the tables are MySQL tables linked through ODBC, then there are 2 important settings to be made.

1) Each table needs to have a TimeStamp field added to the table.

2) In your ODBC Connection manager, when you define the link to the MySQL DB, make sure to select the 'Details' tab and then check the option 'Return matched rows instead of affected rows'

If you do not make the number 2 choice in the ODBC Setup, you will see an error message that the data record is locked by another user when you make a change to the data. This will be a random problem and making this selection will fix the problem.

It took me a long time to find this out, since MySQL and Microsoft seem to blame each other and no one would help solve the issue.

Hope This Helps,
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