Connecting to a QuickBooks database using ODBC

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER

Connecting to a QuickBooks database using ODBC

Postby lfriii » Mon Feb 28, 2011 12:54 am

I am a novice user of both OOo base and ODBC. I am trying to use the ODBC driver supplied by QuickBooks and have followed the directions as far as I could. I ran the configure ODBC in QuickBooks and was able to successfully test the connection to Quickbooks. I then followed the directions that QuickBooks gives for connecting Access to QB via ODBC. I created an OOo base file but do not know what to do to get date from QuickBooks. If anyone can help, I would appreciate it.

Thanks,
OpenOffice version 3.2.1, Operating System; Windows XP Pro, Version 2002, SP3
lfriii
 
Posts: 10
Joined: Mon Feb 28, 2011 12:43 am

Re: Connecting to a QuickBooks database using ODBC

Postby Villeroy » Mon Feb 28, 2011 2:18 am

File>New>Database...
[X] COnnect to existing database
Type: ODBC
Specify the ODBC datasource name.
Register the database
Save the database
Look at the tables.
Add queries
Create simple reports
Install http://extensions.services.openoffice.o ... portdesign
Create better reports
[Tutorial] Using registered datasources in Calc
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.4
User avatar
Villeroy
Volunteer
 
Posts: 28544
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Connecting to a QuickBooks database using ODBC

Postby lfriii » Mon Feb 28, 2011 11:42 pm

Villeroy:

Thank you for your quick response. I tried to do what you suggested, made some progress, but did get to the finish line. I have successfully connected to QB. If I go to edit>database>properties, and click the "Test Connection" on the Advanced Properties tab, I get a message that the connection was established successfully. When I use the table wizard to create a table, I get an error message that [QODBC] Create Table not supported. If I click the more button I get the following: SQL Status: 42S00 Error code: 10012. Can you steer me in the right direction?

Thanks,
OpenOffice version 3.2.1, Operating System; Windows XP Pro, Version 2002, SP3
lfriii
 
Posts: 10
Joined: Mon Feb 28, 2011 12:43 am

Re: Connecting to a QuickBooks database using ODBC

Postby Villeroy » Tue Mar 01, 2011 12:55 am

You connect to an existing QB database. Of course the ODBC driver is not made to build a new set of accounting tables. You need QB to do the work. In addition you can create your own reports, queries, spreadsheet models, serial letters, labels in OOo.
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.4
User avatar
Villeroy
Volunteer
 
Posts: 28544
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Connecting to a QuickBooks database using ODBC

Postby lfriii » Tue Mar 01, 2011 1:24 pm

I do not know what you mean. I do not see any tables, so I believe that I cannot do anything else until I create at least one table. How do I create a table to work with? Am I missing something? What work are you referring to for QB to do?
OpenOffice version 3.2.1, Operating System; Windows XP Pro, Version 2002, SP3
lfriii
 
Posts: 10
Joined: Mon Feb 28, 2011 12:43 am

Re: Connecting to a QuickBooks database using ODBC

Postby Villeroy » Tue Mar 01, 2011 1:41 pm

I can not see what you don't see. The database should reflect all the tables used by QB.
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.4
User avatar
Villeroy
Volunteer
 
Posts: 28544
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Connecting to a QuickBooks database using ODBC

Postby lfriii » Wed Mar 02, 2011 1:45 am

I must be missing something very basic. When I open the database, I get to the home screen. There is a panel on the left labeled "database" with tables, queries, forms, and reports under it. Tables is highlighted. To the right of this is an upper panel labeled "tasks" with "Create table in design view..." and "Use wizard to create table" under that. Below this panel is another one labeled "tables" that has nothing under it. Should I be seeing all the tables used by QB in this "tables" panel?
OpenOffice version 3.2.1, Operating System; Windows XP Pro, Version 2002, SP3
lfriii
 
Posts: 10
Joined: Mon Feb 28, 2011 12:43 am

Re: Connecting to a QuickBooks database using ODBC

Postby Villeroy » Wed Mar 02, 2011 2:25 am

download/file.php?id=8641 is another type of database which is not connected to something. Instead it carries the tables in the odb file. Just take it as an example.
There is a table of meals, a table of ingredients and a table which maps ingredients to meals.
There are 2 queries, one to fill a list with values, another one to calculate nutritional values.
There is a form where you can combine ingredients to get new meals.
It has no report so far.

When you connect to your QB database through the vendor's ODBC driver, I'd expect that you see lots of tables. I never used QB but the database connection to an existing database is pointless when it does not reveal the tables.
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.4
User avatar
Villeroy
Volunteer
 
Posts: 28544
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Connecting to a QuickBooks database using ODBC

Postby lfriii » Thu Mar 03, 2011 8:55 pm

Villeroy:

Now you are getting to the crux of my problem. I can connect to QB using ODBC with Access. All you have to do is create a database, choose ODBC as the source and Access offers you the option of creating link tables. You say yes and it shows you all of the tables available in QB. You select the ones that you want to link to and boom, I have a hundred plus tables in Access to work with.

OObase works similarlly up until you want to create tables, at which time is offers business or personal table templates for manual data input. The question is...Can OObase create it's version of link tables to the QB files so that I have something to work with? So far, I am unable to find a way. If you know of one, please tell me. If OObase is really unable to do this, let me know and I will continue to use Access.
OpenOffice version 3.2.1, Operating System; Windows XP Pro, Version 2002, SP3
lfriii
 
Posts: 10
Joined: Mon Feb 28, 2011 12:43 am

Re: Connecting to a QuickBooks database using ODBC

Postby rudolfo » Fri Mar 04, 2011 3:08 am

lfriii wrote: I can connect to QB using ODBC with Access. All you have to do is create a database, choose ODBC as the source and Access offers you the option of creating link tables. You say yes and it shows you all of the tables available in QB.


OpenOffice Base can do the same thing, except for that you don't have linked tables in Base. Base will simply make all tables from the QuickBooks ODBC database available for you. You just have to do it the right way.

lfriii wrote: OObase works similarlly up until you want to create tables, at which time is offers business or personal table templates for manual data input.


If it asks for or suggests buisness or personal tables you are on the wrong track already. The phrase "create a database" is used with two different meanings throughout the Base Wizard. This often leads to irritation. The wizard will always create a new Base document (a .odb file with the settings on how to connect to a database backend).

Connect2existingDB.png
Connecting to a existing database with an ODBC driver
Connect2existingDB.png (7.75 KiB) Viewed 57687 times


The first option of the above first page of the wizard does actually both it will create the .odb file with the configuration and it will also create the datbase backend (the internal embedded HSQLDB). Unfortunately both steps are called "create database". Not always, but often enough to cause a lot of irritation and frustration.

What you need is the third option "Connect to an existing database". Change the default JDBC to ODBC and in the next wizard step select your ODBC connector (this step uses the System's ODBC configurator tool and is the same as for MS Access). The final step "Saving the database" is actually only creating/saving the .odb file (and you have to think of a name for your .odb file). The actual database (behind the ODBC driver) is left untouched at that point.
When you open this .odb file after the wizard has saved it, you will see all the tables (in my case I used an ODBC driver to a sqlite database):

open-odbc-db.png
Base with ODBC connection to Database backend


 Edit: I did look at this thread as a whole again ... only to realize that I am only saying again, what Villeroy described step by ste in his first post above. But if you can't see any tables it is very likely that did not follow exactly the described process. 
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Connecting to a QuickBooks database using ODBC

Postby lfriii » Fri Mar 04, 2011 3:59 pm

Rudolfo:

I have done what you and Villeroy have described a number of times. I had already done it before I even posted my first question above. Everything works as you say with the exception that no tables show up in the tables screen that you show above. Unless you or sombody else has a way to diagnose and fix this problem, I am going to give up on OObase and use Access.

Thanks anyway.
OpenOffice version 3.2.1, Operating System; Windows XP Pro, Version 2002, SP3
lfriii
 
Posts: 10
Joined: Mon Feb 28, 2011 12:43 am

Re: Connecting to a QuickBooks database using ODBC

Postby Villeroy » Fri Mar 04, 2011 4:13 pm

For me it is interesting to know that Base fails when somebody actually follows my frequently given advice to use Base with ODBC in order to import data from QB. I never used QB so I naively trusted that Base would somehow deal with data coming from the standardized ODBC interface.
Please let us know how it works with MS Access.
Can you add/remove tables or table fields?
Can you add/remove/edit table data?
Or do you have strict read-only access (which would still suffice to get better user-defined reports)?
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.4
User avatar
Villeroy
Volunteer
 
Posts: 28544
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Connecting to a QuickBooks database using ODBC

Postby rudolfo » Fri Mar 04, 2011 7:09 pm

Okay, a few questions and thoughts to inspect your problem a bit closer ...
  • When you created your .odb file with the ODBC connection did you use the [Test Connection] button to see if the basic functionaltiy works? Was the test successful?
  • If you did not run the test you can repeat the test if you have opened the .odb file and choose Edit -> Database -> Propteries ...
  • Did you work with other versions of OpenOffice Base? 3.2.x is the one with the most bugs ... Try 3.3.0 or 3.1.1
  • Maybe Base can't retrieve the Metadata of the Quickbooks. Metadata is also a standardized interface of the ODBC specs, but more likely to have quirks then common insert,update,select statements. The metadata (list of available tables and colums) is needed to show the tables in a database.
  • Can you give us an example name of a table to that you can link with MS Access ... Maybe it has some strange naming convention ... schema3.dollar$scope.tablename? Common is schemaN.tableA.
  • If you have a table name in the Quickbook database (from MS Access), please modify the following macro code (change the line with "sUrl" and with "prepareStatement": aTable needs to be changed to the name that you see in Access. Any table name should do it, even if the table is empty, count will return 0.

Code: Select all   Expand viewCollapse view
Sub ConnectThroughODBC()
  Dim sURL As String
  Dim oManager As Object, oCon As Object
  Dim oStmt As Object, oResult As Object
  Dim oParms(1) As New com.sun.star.beans.PropertyValue

  oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")

  oParms(0).Name = "user"
  oParms(0).Value = "your User"     '<== Change if password is required
  oParms(1).Name = "password"
  oParms(1).Value = "your Password" '<== Change if password is required

  sURL  = "sdbc:odbc:SQLite3 DB"    '<== Change 'SQLite3 DB' to your Quickbook ODBC name
  oCon = oManager.getConnectionWithInfo( sURL, oParms )
  If IsNull(oCon) Then
    MsgBox "Connection not established"
  End If
  oStmt = oCon.prepareStatement("select count(*) from aTable") '<== change aTable
  oResult = oStmt.executeQuery()
  oResult.next()
  MsgBox oResult.getString(1)

  oResult.close()
  oStmt.close()
  oCon.close()
End Sub


Save the macro in the Standard macro library of your profile or of your base file.
Maybe the easiest way to achieve this is to Record a dummy macro, as this will create a default module for you. Once you have saved the macro start it with Tools -> Macro -> Organize Macros.. -> OpenOffice.org Basic
It should give you a message box with something something like:
Code: Select all   Expand viewCollapse view
           +-------------------+
           | soffice           |
           +-------------------+
           | 14                |
           |    [__OK__]       |
           +-------------------+
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Connecting to a QuickBooks database using ODBC

Postby rudolfo » Fri Mar 04, 2011 7:26 pm

Can you also double check, that you see ODBC in the status line of the base document. Compare with my screenshot. It displays ODBC and the name for the ODBC DSN, in my case "SQLite3 CallManager".
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Connecting to a QuickBooks database using ODBC

Postby mikestaps » Tue May 10, 2011 8:34 pm

I am trying to use the ODBC driver supplied by QuickBooks


Where did you get the driver? I would love to get my hands on this!
I am hoping to use it to set up shipping integration with read/write back abilities...
Thanks
OpenOffice 3.2 on Win7 Home Premium
mikestaps
 
Posts: 4
Joined: Tue May 10, 2011 8:30 pm

Re: Connecting to a QuickBooks database using ODBC

Postby mikestaps » Tue May 10, 2011 8:51 pm

OK, forget that idea! QB Pro does not have the ODBC driver included, I would have to purchase a $200 add-on.
OpenOffice 3.2 on Win7 Home Premium
mikestaps
 
Posts: 4
Joined: Tue May 10, 2011 8:30 pm

Re: Connecting to a QuickBooks database using ODBC

Postby YorkLana » Thu Jul 14, 2011 3:52 am

Hiya, folks! Sorry for the late response, but I wanted to compliment and thank Villeroy for the assistance. I was experiencing the same Quickbooks database issues as lfii and was able to connect the database beautifully. And just in the nick of time too! I'm in charge of payroll (for my company) and need to use Quickbooks to manage pay-outs.

Thanks, guys! Hope everyone else is able to get their issues resolved. =)
OpenOffice 3.1 on Windows XP
YorkLana
 
Posts: 1
Joined: Tue Jul 12, 2011 5:23 am

Re: Connecting to a QuickBooks database using ODBC

Postby simdol » Sat Feb 18, 2012 6:07 am

Hello All,

I am a Software, and Database Admin professional. I am also a QuickBooks Accountant. I develop programs for integrating QuickBooks with other applications, for Data Export from QuickBooks, and for Data import into QuickBooks (Web or EDI based orders, Credit Card payments etc...)

The QODBC driver that is coming with QuickBooks Enterprise Edition is for accessing data out of QuickBooks. But you cannot create new tables within the QuickBooks database using the QODBC. You can also buy the full version of the QODBC product form its makers.

I beleive that the QuickBooks uses a protected/proprietary version of the Sybase as the database. But you cannot access the database using normal ODBC drivers. The recommended way to access the QuickBooks data is to use the Intuit QB-SDK which use XML for communication. I beleive that the QODBC uses QB-SDK behind the scene and hence cannot expose the QB tables and structure like other normal ODBC drivers do.

If you require help with import/export of QuickBooks data, please feel free to contact me at msimon at simdol dot com. I do it for a living. But for simple things I can help without a price.

Regards,
OpenOffice 3.2 on Windows Vista
simdol
 
Posts: 1
Joined: Sat Feb 18, 2012 4:01 am


Return to External Data Sources

Who is online

Users browsing this forum: No registered users and 1 guest