CODEWITHSUNDEEP
mysqlms-accessado
user1880641
user1880641

Reputation: 41

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

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.

Upvotes: 4

Views: 710

Answers (2)

Francisco Flores
Francisco Flores

Reputation: 11

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;

Upvotes: 1

adeeliqbal
adeeliqbal

Reputation: 29

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

Upvotes: 0

Related Questions