[Solved] Problem with Sub-Forms and an ODBC DB

Discuss the database features
Post Reply
paul_in_Surrey
Posts: 5
Joined: Fri Jun 14, 2019 7:02 pm

[Solved] Problem with Sub-Forms and an ODBC DB

Post by paul_in_Surrey »

Hello Everyone, I'm afraid I'm a newbie with Base and have been trying to learn with a simple database. I've hit a stumbling block with creating sub-forms with an ODBC linked database.

As soon as my data got to any size I found Base was locking up, so I decided to use DB Browser as the back-end and Base for forms and reports. I have been trying to follow the creation of a sub-form for selection of multiple items. I followed the example of selecting a number of actors in a movie (https://www.youtube.com/watch?v=tmJG6sU0CEI). The problem I've got is Base will not allow my to create relationships between external tables. So, I created a 'Cross-Table' in DB Browser with foreign keys. However, when I try to add the selection list box in a Base sub-form I get a parameter marker count incorrect error and no drop down box.

That's the problem without overloading you with detail. I was wondering if sub-forms for muliple selection with ODBC connections is a common problem. I've searched for solutions but can't find anything relevant. Happy to overload you with more information if that would help.

Thanks
Last edited by paul_in_Surrey on Sun Jun 16, 2019 6:09 pm, edited 1 time in total.
LibreOffice 6.0.7.3 on Ubuntu 18.04
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Problem with Sub-Forms and an ODBC DB

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
paul_in_Surrey
Posts: 5
Joined: Fri Jun 14, 2019 7:02 pm

Re: Problem with Sub-Forms and an ODBC DB

Post by paul_in_Surrey »

Thank you for replying. The posts you pointed to are what I've been trying to do. The problem I have is that using an external SQL database accessed by ODBC I hit the error in the table.

To illustrate this I tried to recreate the example database you pointed to as faithfully as I could. I created the Animals, Persons, Things and P_T tables in DB Browser and then tried to create the form and subform in Base, using the exact form and SQL in your example. The result is I get the 'parameter marker count' error referred to in my question.

I was about to post the two files to illustrate the problem but this website won't permit uploading of .db files, so I can't.
Attachments
PeopleThingsExample.odb
(10.74 KiB) Downloaded 186 times
LibreOffice 6.0.7.3 on Ubuntu 18.04
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Problem with Sub-Forms and an ODBC DB

Post by robleyd »

You could rename the .db files to .db.odb and note that the file extension has been changed.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Problem with Sub-Forms and an ODBC DB

Post by Villeroy »

Whatever program is able to handle your *.db files (may be "Paradox" may be "MS Works" or something completely different), you should use that program to create all tables, relations and indices before you access the database with a frontend such as MS Access or this one.
For the simple form, it is not necessary to actually have the relation in the backend. The form in the frontend mimics the relation even if it does not exist in the backend. The difference is that you will be able to enter ID values directly into the linking table (movie-ID and/or genre-ID in that example) that do not exist in the respective table. Without the relations, the backend database can not provide referential integrity.
The form will only offer those genres and movies that actually exist in their respective table. Problems may arise when you delete some genre or movie. Then the form will show empty values in the subform where there is a referenced ID with no named item in the respective table. With referencial integrity you could not delete any item from one table that is referenced in another table (or you would have to handle this situation properly).

The database introduced in the youtube video was certainly inspired by various movie databases on this forum: download/file.php?id=2879 (remove the relations, add and remove items, add invalid IDs to "MovieGenre" and see)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
paul_in_Surrey
Posts: 5
Joined: Fri Jun 14, 2019 7:02 pm

Re: Problem with Sub-Forms and an ODBC DB

Post by paul_in_Surrey »

Thanks very much for the thought you've put into this.

Robleyd - This is the backend DB file I created (as you suggested renamed .db.odb in order to upload it here).
PeopleThingsExample.db.odb
(36 KiB) Downloaded 176 times
And this is the Base file I created to try and access it.
PeopleThingsExample.db.odb
(36 KiB) Downloaded 176 times
Villeroy - I created all the tables in DB Browser for SQLite., and then accessed it by ODBC from Base. I didn't really understand your next sentence about mimicing the relations even if they don't exist in the backend. But anyway, I've tried to duplicate the example you posted in your tutorial post but still get the error when I add the subform. It all works fine for the Animal list selection. It's just when I add the subform column list to select multiple Things that it goes pear-shaped.
Attachments
PeopleThingsExample.odb
(10.65 KiB) Downloaded 187 times
LibreOffice 6.0.7.3 on Ubuntu 18.04
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Problem with Sub-Forms and an ODBC DB

Post by Villeroy »

I don't know if and how LibreOffice can handle Firebird databases and I don't know how to connect your db file with your odb. I have never seen any non-trivial Firebird connected to Base and working, not even the embedded FB offered as an alternative to embedded HSQL. IMHO, LibreOffice is going nowhere with Firebird.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
paul_in_Surrey
Posts: 5
Joined: Fri Jun 14, 2019 7:02 pm

Re: Problem with Sub-Forms and an ODBC DB

Post by paul_in_Surrey »

This is getting weird. I've never mentioned a Firebird database, and to be honest I don't think you've understood a word I've said. Giving up on this forum and probably Base too.
LibreOffice 6.0.7.3 on Ubuntu 18.04
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Problem with Sub-Forms and an ODBC DB

Post by Villeroy »

Sorry, I confused this with another thread where Firebird was used. You uploaded an SQLite database.
I managed to create an ODBC source for your database and fixed the 'parameter marker count' issue with menu:Edit>Database>Advanced Properties>"Replace named parameters with '?'".
Next issue is that you added a primary key on the Person-ID instead of a combined primary key on Person ID and Thing ID.

menu:Tools>SQL...

Code: Select all

DROP TABLE "P_T";
CREATE TABLE "P_T" (PID INTEGER NOT NULL, TID INTEGER NOT NULL, PRIMARY KEY (PID, TID));
menu:Tools>Refresh Tables

Your form works now without modification.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
paul_in_Surrey
Posts: 5
Joined: Fri Jun 14, 2019 7:02 pm

Re: Problem with Sub-Forms and an ODBC DB

Post by paul_in_Surrey »

Thank you so much Villeroy (particularly for not being put off by my strop). I really appreciate your efforts, and yes you're right that does now work.

I'm going to have to learn about those advanced settings, and I wasn't even aware of the effect of combined primary keys. So, all a big lesson for me.
LibreOffice 6.0.7.3 on Ubuntu 18.04
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [SOLVED] Problem with Sub-Forms and an ODBC DB

Post by Villeroy »

Unfortunately, the "advanced options" are poorly documented. They vary depending on the type of connected database. I came up with this solution by mere intuition out of experience. When the subform loaded properly I saw that it violated some index. I dropped the entrire table and created the same table newly from scratch with a combined primary key. This way every thing belongs to every person only once.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply