Ask Your Question

Cannot create or read records in a SQLite over ODBC database?

asked 2019-04-18 13:37:40 +0100

arjan gravatar image

updated 2019-04-19 14:11:01 +0100

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.""" (???)

edit retag flag offensive close merge delete


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.

Ratslinger gravatar imageRatslinger ( 2019-04-18 16:48:48 +0100 )edit

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.

arjan gravatar imagearjan ( 2019-04-18 17:30:23 +0100 )edit

3 Answers

Sort by » oldest newest most voted

answered 2020-02-26 11:26:56 +0100

the_watchmann gravatar image

@arjan == Yes! it works... many many thanks!

edit flag offensive delete link more

answered 2019-08-23 13:02:20 +0100

updated 2019-08-23 13:07:14 +0100

The error also shows up on LibreOffice Version: on Centos Linux 7 and is a showstopper.

The problem is within LibreOffice.


  1. DBBrowser for SQLite opens the database correctly so the issue is not within SQLite
  2. isql also opens, reads, and write to the database correctly so the issue is not within unixODBC

@arjan 's solution works. Thanks @arjan!

edit flag offensive delete link more

answered 2019-04-19 14:09:26 +0100

arjan gravatar image

updated 2019-04-28 21:30:39 +0100

Update: the problem can be worked around by unchecking "Respect the result set type from the database driver" (under Edit > Database > Advanced Settings > Special Settings). Via this bug report. Also see this one. Also note that changing this setting does not yet persist upon reopening the database.

Additionally, the character set should be set to UTF-8 under the database connection type.

edit flag offensive delete link more


Update: actually, it does not persist. I was able to write to a table after changing this setting, but upon reopening the .odb file, I'm back to having the same problem with UTF-8 configured.

arjan gravatar imagearjan ( 2019-04-19 15:18:23 +0100 )edit

The UTF-8 config seems unrelated to the main problem (though I can't explain how it worked at least one time). You should still configure it for correct handling of e.g. characters with diacritics.

arjan gravatar imagearjan ( 2019-04-28 20:09:53 +0100 )edit


Just peeked at one of my SQLite test .odb's. Again a reminder that this is on Linux. In Database Properties I use the browse button and it lists all my ODBC connections available (originates from my odbc.ini system file). I just select one & Select OK button. Then that name stays in the 'Name' box even when going to Advanced Settings and back.

Also, in my connection settings I have Character set set to 'System'.

Ratslinger gravatar imageRatslinger ( 2019-04-28 20:47:38 +0100 )edit

It's basically fixed now, I just left the second half of my answer for future reference or to create a bug report about the wizard if there isn't yet any. I would use strike-through bit it isn't available.

arjan gravatar imagearjan ( 2019-04-28 21:14:53 +0100 )edit

Strike through should work.

Ratslinger gravatar imageRatslinger ( 2019-04-28 21:19:38 +0100 )edit

What syntax did you use?

arjan gravatar imagearjan ( 2019-04-28 21:23:49 +0100 )edit

Oh just saw <del>...</del>. It looked off so I deleted it anyway.

arjan gravatar imagearjan ( 2019-04-28 21:31:16 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-04-18 13:37:40 +0100

Seen: 1,558 times

Last updated: Feb 26 '20