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

Discuss the database features

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

Postby paul_in_Surrey » Fri Jun 14, 2019 9:03 pm

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
paul_in_Surrey
 
Posts: 5
Joined: Fri Jun 14, 2019 7:02 pm

Re: Problem with Sub-Forms and an ODBC DB

Postby Villeroy » Sat Jun 15, 2019 2:34 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27222
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Problem with Sub-Forms and an ODBC DB

Postby paul_in_Surrey » Sat Jun 15, 2019 6:31 pm

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 26 times
LibreOffice 6.0.7.3 on Ubuntu 18.04
paul_in_Surrey
 
Posts: 5
Joined: Fri Jun 14, 2019 7:02 pm

Re: Problem with Sub-Forms and an ODBC DB

Postby robleyd » Sun Jun 16, 2019 2:09 am

You could rename the .db files to .db.odb and note that the file extension has been changed.
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2986
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Problem with Sub-Forms and an ODBC DB

Postby Villeroy » Sun Jun 16, 2019 9:58 am

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27222
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Problem with Sub-Forms and an ODBC DB

Postby paul_in_Surrey » Sun Jun 16, 2019 12:26 pm

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 28 times


And this is the Base file I created to try and access it.

PeopleThingsExample.db.odb
(36 KiB) Downloaded 28 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 27 times
LibreOffice 6.0.7.3 on Ubuntu 18.04
paul_in_Surrey
 
Posts: 5
Joined: Fri Jun 14, 2019 7:02 pm

Re: Problem with Sub-Forms and an ODBC DB

Postby Villeroy » Sun Jun 16, 2019 2:07 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27222
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Problem with Sub-Forms and an ODBC DB

Postby paul_in_Surrey » Sun Jun 16, 2019 2:45 pm

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
paul_in_Surrey
 
Posts: 5
Joined: Fri Jun 14, 2019 7:02 pm

Re: Problem with Sub-Forms and an ODBC DB

Postby Villeroy » Sun Jun 16, 2019 4:46 pm

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   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27222
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Problem with Sub-Forms and an ODBC DB

Postby paul_in_Surrey » Sun Jun 16, 2019 5:12 pm

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
paul_in_Surrey
 
Posts: 5
Joined: Fri Jun 14, 2019 7:02 pm

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

Postby Villeroy » Sun Jun 16, 2019 8:48 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27222
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests