Search code examples
mysqlms-accessodbcinner-joinvarchar

Editable MS Access Query with varchar fields in join is no more editable when backend upgraded to MySQL

I upgraded back-end MS Access 2003 to MySQL 5.1 of a database. Now I am linking the backend MYSQL 5.1 database thr' ODBC (MySQL ODBC 5.1 Driver) to MS Access using ODBC.

Almost all features worked fine when I set the indexes, primary key relationship/constraints etc.

But one below query which was editable in MS Access version before back-end upgrade, is no more allowing edits.

The join type is Left/Inner/Right Join and fields in join are varchar data type. 

SELECT tblebayinvoices.[User Id], tblebayinvoices.[Buyer Full name], tblebayinvoices.  
[Buyer Phone Number],  tblstock.stcEBayDescr, tblstock.stcEBayActive
FROM tblebayinvoices LEFT JOIN tblstock ON tblebayinvoices.[Item Title] = 
tblstock.stcEBayDescr;

My observation is other queries where the field data type is numberic, they are fine and the queries are still editable. This same query was editable when the back-end was MS Access I tried using Text data type of MySQL but MS Access treats this as Memo and does not allow using in joins.

Please let me know suggestions or changes I need to do.

Solution

  • Creating unique key on "stcEBayDescr" solved the problem. We missed the unique key index that was there in MS Access but not created in MySQL table's database. Now the query is editable in MS Access(uses MySQL linked tables thr' MySQL ODBC)