Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

Buy Support
Incidents

If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:

Click Here
If you can't login and post questions or you are having trouble viewing forum posts:
Click Here
Callback
Support

If you live in USA, UK, Canada, Australia or New Zealand, you can leave us details on your question and request us to call you back and discuss them with you personally  (charges apply).

Click Here
 
Buy Support
Incidents
If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:
Click Here

Forum : Quickbooks Online - Unable to see records in all tablesSearch Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC v9 Forum

 New Topic 
 
 Post Reply 
[1]  
 Quickbooks Online - Unable to see records in all tables 
 Author   Message 
  Jim 
  
 Group: Members 
 Posts: 6 
 Joined: 2008-10-23 
 Profile
 Posted : 2008-10-24 00:11:50

I am just starting out with the QODBC driver and intend to use it with QB Online. I have setup QB Online and had my setup reviewed by Intuit and they assure me everything with the conversion went as expected. I have setup the QODBC driver per the instructions and have tested it with QB Desktop version and the driver works fine.

I have successfully established a connection to QB Online and can see table list via the driver and data in tables such as Account, Company, and Check, but the Bill, Bill Expense Line, Invoice, and Invoice Line tables do not return any rows of data via the driver. I have tested the driver using both MS Access and your VB Demo application and had the same results. I reviewed my connection permissions on QB Online:

Status = Active, Access Rights = All Accounting, Login Security = Yes. Allow company users to log in and use this connection.

Also, when I try to query the Bill Expense Line table via MS Access, Access crashes. If I simply try to open the Bill Expense Line table in MS Access, the driver does not return any rows of data, but I see the column headings. When I try to query the same table via the VB Demo, again, no data, but I do see column headings.

Please advise on next steps I should take to troubleshoot/resolve this problem. Thanks

 

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2008-10-24 08:32:07

When over 1000 records could be returned by any given query to the QuickBooks Online Edition, the query gets terminated by QBOE at the Intuit end. Assuming that you have entered invoices since January this year, you need to query a smaller subset of records like this using VBDemo:

select * from Invoice UNOPTIMIZED WHERE Txndate > {d '2008-01-01'}

Where the date format is {d 'YYYY-MM-DD'}

You can also remove the UNOPTIMIZED tag if it works as I've only added it to bypass your local optimized invoice and invoiceline tables in case there's something wrong there. You should always use the QODBC Optimizer when using QBOE.

 

  Top 
  Jim 
  
 Group: Members 
 Posts: 6 
 Joined: 2008-10-23 
 Profile
 Posted : 2008-10-30 00:39:06

Tom, thanks for the advice. I have setup the Optimizer and I have successfully run your test using VB Demo. My problem know is MS Access (version 2007). When I attempt the same query as I did with VB Demo, I do not get any records returned. Here is the SQL that Access generates...

SELECT Bill.*
FROM Bill
WHERE (((Bill.DueDate)>#9/1/2008#));

MS Access handles date formats differently, but I believe ODBC should convert.

I also tested a SELECT for a specific RefNumber (38682) and did not get any results either. I know this RefNumber exists (see screen shot below), but am I perhaps searching the wrong field? I followed the data map. Please advise on additional steps I can take to resolve this issue. Thanks again.


 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2008-10-30 08:26:15

Try running this as a pass-through query in MS Access:

select * from Bill UNOPTIMIZED WHERE DueDate > {d '2008-09-01'}

See: How do I create a Pass-Through Report using Microsoft Access 2003? and How do I use prompted date ranges in MS Access 2007 using Vista? for where the options are in MS Access 2007.

 

  Top 
  Jim 
  
 Group: Members 
 Posts: 6 
 Joined: 2008-10-23 
 Profile
 Posted : 2008-10-30 09:03:08
Hate to sound like a broken record, but still nothing. I am pretty experienced using MS Access and ODBC for quering SQL databases and have never run into this one before.

I attempted the pass-through query using the syntax you suggested and I received an ODBC call failure. See screen shot below. I know the pass-through query works and my connection is active because I tried a pass-through query using the Account table without a WHERE clause (table has < 1000 rows of data) and got a result. It appears the issue is with the WHERE clause possibly. I attempted a pass-through query on the Bill table using the RefNumber = 38682 in the WHERE clause and did not get a call failure, but did not get any results either. By the way, I am running XP, not Vista.

Thoughts?

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2008-10-30 10:13:54

Change the Pass-Through Query properties for: "ODBC Connect Str" to call QODBC like this:

ODBC;DSN=QuickBooks Data;SERVER=QODBC

and run the queries in VB Demo first to see if there are any results.

 

  Top 
  Jim 
  
 Group: Members 
 Posts: 6 
 Joined: 2008-10-23 
 Profile
 Posted : 2008-10-30 10:29:30
I edited the connection string properties and attempted the same query in VB Demo and got no records returned. I know there are bills with due dates > 2008-09-01 ( I can see them in QB Online). I also tried the query in MS Access with the revised connection string and instead of a call failure, I got no records returned. Hmmm 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2008-10-30 11:10:35

Ok, DueDate doesn't have an index (or jump-in), so your query has to do a table scan and that's when you hit the greater than 1000 rows barrier. So you're going to have to filter a small subset of data like this:-

select * from Bill UNOPTIMIZED WHERE TxnDate > {d '2008-06-01'} and DueDate > {d '2008-09-01'}

 

  Top 
  Jim 
  
 Group: Members 
 Posts: 6 
 Joined: 2008-10-23 
 Profile
 Posted : 2008-10-30 12:39:25
No luck. I tried select * from Bill UNOPTIMIZED WHERE TxnDate > {d '2008-06-01'} and DueDate > {d '2008-09-01'} in VB Demo and it did not return any records. I am confused by two things. First, in your last response you seemed to indicate that if a field is not indexed and a full table scan is required, then the 1000 records restriction is going to cause no records to be returned. Is that correct? If so, that places a serious restriction on the usability of the driver with QB Online. Does this also explain why I cannot select a record with a specifc (an unique) reference number? Second, I am using Optimizer within the driver. My config is set to the last option ("The Last time I pressed one of the Load Data buttons". I would think this feature would allow me to get a result returned from the local cache. Doesn't Optimizer download table data and store locally?

By the way, I thought it would be useful to show a screen shot QB Online to show that there are in fact bills in the system that should be returned according to the criteria...


 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2008-10-30 15:25:47

What happens if you do:-

select * from Bill UNOPTIMIZED WHERE TxnDate > {d '2008-09-01'}  and TxnDate < {d '2008-11-01'}

using VBDemo? Also check the bottom of the QODBC and SDK Messages Log in the "Messages" tab in the QODBC Setup Screen for any errors.

 

  Top 
  Jim 
  
 Group: Members 
 Posts: 6 
 Joined: 2008-10-23 
 Profile
 Posted : 2008-10-30 23:44:02
That works in VB Demo and MS Access. I get 122 records returned. Questions:

When I remove the Unoptimized statement the query does not return any records via VB Demo or Access. Why is this? Should I turn off Optimizer? Doesn't seem to be doing anything for me. Looks like every SELECT makes a trip to the QB db server anyways.

In VB Demo, once I establish a current connection, I can make multiple query requests without having to continually log-in to QB Online and get a Log-in Key. In MS Access, even though the driver is configured with a Connection Key and Log-in Key, every time I execute a query I have to select the driver to use and enter a new Log-in Key. Why is this?

When I start MS Access, I have to refresh the links to my QB Online tables (again by getting a new log-in key). If I do not do this, all my queries will result in an ODBC call failure. The configuration seems pretty simple with few options available. I have the QODBC driver which I have used to Link tables into MS Access from QB Online. Seems strange that the connection is not held for the duration of my session (which should be equal to the time the Access db is open).
 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2008-11-03 08:46:22

As per: How do I use QODBC with QuickBooks Online?, The Connection Key will expire after a period of non-use. If your application makes several connections each time you access QODBC you might want to return to this screen and press the "..." and login once, saving the new Login Key. Then you will not be prompted again until the non-use timeout expires.

MS Access creates another connection instance each time it executes a query. You can turn off the optimizer for now.

 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to