[Example] Queries that can edit multi-tables

[Arineckaig]

This thread arises from a recent post by DACM at:
http://user.services.openoffice.org/en/ ... 59#p216752

For some time now I have been experimenting with Queries that permit the editing of data from more than one Table source. As expalained in DACM's post the key is that the Query (or SQL) should include the PKs and linking FKs from each of the tables. In earlier versions of OOo Base this appeared not to be possible and even in OOo 3.3 there are limitations. For example, it appears to require the Query, or rather the underlying SQL, to be parsed by the Base GUI, and it only permits limited scope for adding new records.

In certain limited cases the ability to edit fields in more than one table from a single query or data form could have potentially useful benefits: for example, by comparison with the use of CONCAT for the display in a list box. I have attached a simple demo file to illustrate what can be done. I would welcome comments and be grateful to learn from the experience of others.

[Villeroy]

Thank you very much. It works well across 3 tables, JOIN syntax and HSQLDB 2.5 in server mode with LibreOffice 3.3.4 as client. I'll have to simplify some of my forms.

[Arineckaig]

FWIW I have tested it successfully with Many-to-Many links, with JOINs over four tables and with MySQL as the DMS. I do not trust it yet sufficiently, however, to justify risking its use with serious data: for example why does it not work with direct SQL? Will be interested where you find its problems.

[Villeroy]

Nothing works with direct SQL. SELECT * FROM "X" is read-only. Pairs of forms and subforms remain unlinked if either part runs direct SQL.

[Arineckaig]

Many thanks for your explanation. Until now I had quite failed to notice that direct SQL produced a Read-only result even with a single table source, whereas when GUI parsed the output can be used for updates. I learn something new (and dare I say impressive) about the Base GUI every day.

[RandyB]

Wow, how lucky I am to have found this thread. I spent hours searching for the answer to my inability to use a Base form or query to INSERT or UPDATE my Contacts table in MySQL. I am leaving Office Access DB (which will sever my last tie with Microsoft!) in favor of MySQL, to be managed through a Base front-end. My first project is my checkwriting and reporting application for 3 bank accounts (16 years records) onto my new Linux-Mint PC. My Contacts table links to a Postal Code table from which the city and state are retrieved and a Prefix table that contains courtesy titles (Mr., Doctor, Senator, etc.) I used a Right Join from the Contracts table to the two look-up tables. On the Base form, I could see the desired data from all 3 tables but could not add or edit anything. My query contained only 2 of the 3 primary keys. As for the third PK, didn't need it, didn't want it. Now that you have shown me the light, life is good. I was so frustrated that I was about to drop Base and install/learn ooRexx to accomplish what I had hoped Base could do. Now that I know how to do what I need to, in Base, I am back in the Base camp. MANY THANKS!