gnucash, sql, and libre office

Wm tcnw81 at tarrcity.demon.co.uk
Sun Feb 21 09:38:43 EST 2016


On 20/02/2016 01:34, Tommy Trussell wrote:
> On Thu, Feb 18, 2016 at 2:25 PM, Kai Truempler <truempler at gmail.com> wrote:
> 
>> a couple of years ago I was using gnucash with the gnuc2ooo macro (
>> http://www.alice-dsl.net/gnuc2ooo/save/).
>>
>> After a break from Gnucash, I was wondering now where I can find some hints
>> how to use the now functioning sql backend in a similar way. Is there a
>> tutorial how to set Libre Office (Base and Calc) up with the actual gnucash
>> sqlite file in a way that was possible with the database created by the
>> gnuc2ooo macro (like in this
>> http://www.alice-dsl.net/gnuc2ooo/save/gnuc2ooo_en/Example1.html example?
>>
>> Many apologies if that question has been solved before, but I had a hard
>> time finding an answer.
>>
>>
> As you probably know, the macro described at that link is no longer
> available, and its documentation stated it would no longer function for
> GnuCash after 2.4.x versions. The macro's author felt it would be
> superfluous since GnuCash 2.6.x can use Sqlite3 databases.
> 
> With a small amount of searching, I found some documentation here:
> 
> https://wiki.openoffice.org/wiki/Documentation/How_Tos/Using_SQLite_With_OpenOffice.org
> 
> I have made it partway through the setup (not yet successfully) but I
> thought I would report back here with a big hint. Using that page's
> examples on my Ubuntu 14.04 LTS installation, it turns out the ODBCConfig
> utllity in unixodbc-bin is now  ODBCManageDataSourcesQ4
> 
> There's also an ODBCCreateDataSourceQ4 which launches a wizard.
> 
> However I haven't yet successfully created a working link from LibreOffice
> to a GnuCash sqlite3 database file I created.
> 
> If you do develop a procedure that works, let's plan to put the information
> on the GnuCash wiki.

General, checked on Win8.1 with LibreOffice5.1

LibreOffice can open PostgreSQL databases directly and is the simplest
to connect to:

File / New / Database / Connect to an existing database /
choose PostgreSQL from the drop down /
configure e.g. postgresql://localhost/my_gnucash_db
and you're off.

MySQL also needs needs connectors so I'll skip it for now but could fill
in later if it gets to doing a Wiki entry.

SQLite needs either an ODBC or JDBC driver.  LO currently suggest
Devart's ODBC driver for all OS's
http://extensions.libreoffice.org/extension-center/libreoffice-sqlite-odbc-driver
but it didn't work for me and I don't like the license.  I have used
http://www.ch-werner.de/sqliteodbc/
for some years and found it reliable, it also works for Linux and Mac
though I haven't tried them.  Check the site out for more.

Once you have installed your ODBC driver set the connection to your
GnuCash sqlite3 file up through your OS then in LO

File / New / Database / Connect to an existing database /
choose ODBC from the drop down /
browse will show you the connection you set up just above and you can
just click through to the end.

JDBC works too but I've generally found ODBC less fiddly and faster to use.

Fill in gaps / ask if stuck, I'm sure we'll be able to sort it out.

-- 
Wm


More information about the gnucash-user mailing list