Cannot create or read records in a SQLite over ODBC database?
I want to make a SQLite search front-end using LibreOffice Base, but it appears Base can't read or create data in SQLite tables over ODBC. I have installed the ODBC driver (on Windows 10) and configured a database "Test", starting with an empty SQLite database Test.db.
I can access the database and create a table from Base. I create a table MyTable, with columns "id" (INTEGER PRIMARY KEY) and "test" (VARCHAR). The table created in Base shows up in other SQLite managers.
But when I try to enter a record from Base, I get this:
Error inserting the new record
SQL Status: HY000
Error code: 1
no such table: D:\Temp\Test.db.MyTable (1)
If I enter a record from another manager and then open the database again from LO and open the table, it shows as empty.
Any clues what's going on?
Update: I also found the following, could be relevant?
Edit > Database > Connection type > Test connection: """ The connection was established successfully."""
Edit > Database > Database properties > Test connection: """ A driver is not registered for the URL ~sdbc:odbc:Test.""" (???)
Hello @arjan,
Unfortunately will not be able to help you much as Windows is not available to me. I currently use Linux only. Have gotten SQLite to work in Linux. See these posts:
LibreBase and SQLite/Spatilite
Base: how to connect to an SQLite database? [closed]
I post this to let you know that Base will work with SQLite and ODBC in Linux so it would seem to me this should also work in Windows. There is possibly some incorrect path somewhere within your set up.
What I find puzzling is that the SQLite ODBC drivers must be loaded, and the registered database is also found, because I can open a database as expected, and create a table. It seems odbc .ini files are not meant to be edited by hand on Windows -- I guess it uses the register.