Can I link a Quickbooks customer database to a LibO Base table?

[LitlJay]

We have a customer database saved in Base format that has been accumulating for about 8 years and has grown quite large. I’ve grown very accustomed to manipulating it and really don’t want to change.

We are in the process of setting our books up in Quickbooks, and would like to link it to that database so that changes in one will be reflected in the other.

I would imagine that an ODBC link would be the way to do this, but it really isn’t my wheelhouse (not that I’m afraid to roll up my sleeves and get my hands dirty figuring it out).

The ODB file lives on a Windows Server 2008 R2 file share and Quickbooks is installed on a dedicated Win7 Pro Hyper-V VM with extremely tight user, network, and internet restrictions. I’ll open up the doors that I need to in order to make the link work.

Any help is appreciated. TIA!

J

[Regina]

You first need detailed information about the abilities of Quickbooks, before you can look how to use your .odb file. Do they provide an ODBC driver? Can Quickbooks work as front end of any external database engine? Has Quickbooks the ability to link to an external csv file and sync it with the internal database? Which file formats can Quickbooks import, which export?

I did a quick search about Quickbooks and it seems a very closed software to me. So I fear, that you will not have much luck to keep the .odb file, when moving to Quickbooks. Even transferring the pure data from the .odb file to Quickbooks might be not straight forward.

[LitlJay]

I have actually found a lot of information about people linking ODB files to existing QuickBooks company files and manipulating all of the tables via ODBC. I just need to know if I can go the other way (link a new QuickBooks company file to an existing ODB file, then maybe access the rest of the tables for external manipulation some kind of way later).

[Regina]

No, you cannot use an existing .odb file. An existing .odb file has already a connection and cannot take a second one. Each .odb file can only connect to exact one database engine. What file format has the “QuickBooks company file”? If it is a plain csv file and if you use the embedded HSQL engine, than there might be a way to use the file with direct SQL commands.

[LitlJay]

So I can’t modify these instructions to fit my needs? I thought for sure there would be some kind of way to make it happen.

[Regina]

That instructions are to get a new .odb file, which connects to the tables in QuickBooks, but you have an existing .odb file, which has already a connection to a database.

In case yours is the embedded HSQL database engine, it is possible to get access to an external csv file using CREATE TEXT TABLE and SET TABLE … SOURCE. From Connecting to a QuickBooks database using ODBC (View topic) • Apache OpenOffice Community Forum I guess it might be possible to export such file from QuickBooks. It would be readonly use.

[LitlJay]

Well, with a little nudging in the right direction from Regina, I have found that, while such a connection is POSSIBLE, what I want to do ISN’T. Quickbooks has a built-in ODBC driver that pretty much ANY application (like LibO) can connect via, but it is a READ-ONLY connection.

LibO can be used to generate custom reports and create invoices or other documents if the QuickBooks templates aren’t flexible enough for your needs, but LibO and other applications can not be used to modify the QuickBooks data.

In the end, I had to import (not link) our existing customer database to QuickBooks, then create a new ODB file that reads and updates itself automatically from QuickBooks. I can not modify/update or add customer data from LibO, but I can quickly open the new LibO file to get customer information when I need it without having to log in to the QuickBooks workstation, and that will be handy for our Base CRM software to use on mine and our employees’ tablets/cell phones on the road.