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

0
votes

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.

2
mysqlms-accessodbcinner-joinvarchar
Are the primary keys defined for both of the tables - Linger
@Linger , yes both the tables have primary respective. and other indexed fields as those were in MS Access. - Sham Yemul

2 Answers

1
votes

It does not appear that your joined fields are the primary keys. I am assuming this since those fields are text and don't appear to be typical primary key fields.

The join will only be editable if the join is done from a primary key on the main table to the foreign key in the child table.

1
votes

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)