Cannot add rows to a INNER JOIN query on two linked tables in a MySQL backend

[Mark Grimes]

We have a MS Access frontend connected to a MySQL backend via ODBC. We are trying to move two tables, with a one-to-one relationship between them, into the backend. We then have an Access query to combine the tables with an INNER JOIN and display the query as a datasheet on a form.




When the tables are in Access (not linked), you can add a row in the query datasheet view, and Access creates a row in both tables with the same primary key. When the tables are moved to the MySQL backend, attempting to insert a new record into the query is prevented with an error in the statusbar:

    Cannot add record(s); primary key for table `Extended` not in recordset.

Here are the simplified table and query definitions:

    -- MySQL table definitions:
    CREATE TABLE Basic (
      ID int(11) NOT NULL AUTO_INCREMENT,
      Model varchar(255) DEFAULT NULL,
      PRIMARY KEY (ID)
    );


    CREATE TABLE Extended (
      ID int(11) NOT NULL DEFAULT '0',
      ModelNum int(11) DEFAULT NULL,
      PRIMARY KEY (ID),
      CONSTRAINT fk_Extended_Basic FOREIGN KEY (ID) REFERENCES Basic (ID)
    );


    -- MS Access Query:
    SELECT Basic.ID, Basic.Model, Extended.ModleNum
    FROM Basic INNER JOIN Extended ON Basic.ID = Extended.ID;

Access will let you add a row to the query if you only enter data for the Basic table (ie, Model). This of course only creates a row in the Basic table. When you try to type anything in the ModelNum field, Access prevents it and displays the above error message in the statusbar.


I've tried creating the relationship in the Access Relationships window. I've added TIMESTAMP columns to both tables. I've also used wireshark to sniff the MySQL connection, and Access doesn't appear to send anything when it shows the error.


Is it possible to get Access to do the right thing? It should send an `INSERT INTO Basic`, get the ID then `INSERT INTO Extended` with that key. I can add a button and do it in VBA, but in our real app there are many related tables and places where the functionality would have to be duplicated. We can't use a trigger to automatically create the Extended row as there are times when that is not appropriate.


We are using MS Access 2007 and MariaDB 5.5.37. Thanks!

[ItsMe]

I did not read the whole post but it sounds like there is a referential integrity issue. You can create rules in Access, by ticking options, to enforce referential integrity of Relationships created in the Relationships Window. Then, adding a record to a parent table will automate an append action to the child table to maintain referential integrity.

It seems like there is a Relationship in your MySQL tables that is maintaining constraints. I don't use relationships and, instead, depend on JOINS and code to maintain constraints and avoid issues during development. Perhaps you can remove the Relationship and avoid the warning. AFAIK newer versions of Windows SQL are deprecating the Relationships wizard. I do not know anything about MySQL.

[Mark Grimes]

Thanks for the quick response. When you say "adding a record to a parent table will automate an append action to the child table" that is exactly what I'm hoping to achieve. This works great if the tables are in Access, but once the tables are moved into a MySQL backend it fails. I can somewhat work around this problem with triggers in MySQL, but completely.

[ItsMe]

Yah, I am not an SQL backend expert but I believe this is where your problem is. Access will throw a warning and halt the creation of a new record if the action is not acceptable to the Relationship Window.

The intrinsic features for managing constraints are nice but not always scalable.