Connecting to a QuickBooks database using ODBC

[lfriii]

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,

[Villeroy]

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

[lfriii]

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,

[Villeroy]

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.

[lfriii]

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?

[Villeroy]

I can not see what you don't see. The database should reflect all the tables used by QB.

[lfriii]

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?

[Villeroy]

http://user.services.openoffice.org/en/ ... hp?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.

[lfriii]

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.

[rudolfo]

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.

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).

Connecting to a existing database with an ODBC driver

Connect2existingDB.png (7.75 KiB) Viewed 64638 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):

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.

[lfriii]

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.

[Villeroy]

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)?

[rudolfo]

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

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

           +-------------------+
           | soffice           |
           +-------------------+
           | 14                |
           |    [__OK__]       |
           +-------------------+

[rudolfo]

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".

[mikestaps]

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

[mikestaps]

OK, forget that idea! QB Pro does not have the ODBC driver included, I would have to purchase a $200 add-on.

[YorkLana]

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. =)

[simdol]

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,