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
|
|
|
|
Tom |
|
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.
|
|
|
|
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.
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-10-30 08:26:15 |
|
|
|
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?
|
|
|
|
Tom |
|
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. |
|
|
|
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 |
|
|
|
Tom |
|
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'} |
|
|
|
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...
|
|
|
|
Tom |
|
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. |
|
|
|
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). |
|
|
|
Tom |
|
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. |
|
|
|