Access 2010/MySQL backend using ADO - recordset of query is not updateable

4
votes

I am building a MS Access 2010 application with a MySQL backend using ADO. Thus far, I have been successfully binding and updating my ADO recordsets to forms. However, I have just created the first query that contains two tables with an INNER JOIN and I am unable to update the returned recordset when fields are returned from both tables. This first query I am able to successfully bind and update.

SELECT table_A.a, table_A.b
FROM table_A INNER JOIN table_B ON table_B.c = table_A.c;

However, the moment I add a field from table_B, the recordset can no longer be updated via the bound form. The new MySQL statement looks as follows.

SELECT table_A.a, table_A.b, table_B.a
FROM table_A INNER JOIN table_B ON table_B.c = table_A.c;

I have read the forums, and my query does not appear to be subject to the usual problems that would prevent the recordset from being updateable (i.e., a lack of primary keys, aggregate functions, SELECT DISTINCT, …). Some forums have suggested that my problem may be related to ambiguity between the recordsets, but I have not been able to confirm this and it seems like this should work. Any help is much appreciated.

2
mysqlms-accessado
That is correct - at least not through the bound form. – user1880641
After a bit of further analysis, I am able to update records from table_B only if the form remains unbound. If I set the form's recordset property, I cannot update the records in the form. Ideally, I need to set this property because this form is viewed in datasheet mode and using unbound forms demands additional time. Does anyone know whether the 2nd SQL query above can be bound to and updated from an Access form. Thanks. – user1880641
In the end, [Form].UniqueTable resolved my issue. – user1880641

2 Answers

1
votes

Try this:

SELECT table_A.a, table_A.b, CONCAT(table_B.a, '') FROM table_A LEFT JOIN table_B ON table_B.c = table_A.c;
0
votes

in the first query, the record set displayed / retrieved belongs to a single table, so it can be updated. however as in the second query, the recordset retrieved as a result of join in two tables (columns belong to 2 tables) therefore it can't be directly updated.

in order to update some data based on the data from another table, you can use the following query

Update Table_A,Table_B set Table_A.a=Table_B.a where Table_A.b=Table_B.b