I've purchased QODBC for my client's environment now and I'm using it on U.S. Premier 2007.  You indicated in another post that I should be able to access the transaction table.

First, when I choose Transaction on the Quickbooks Schema page I get the following error, but I think that's because a screen capture is irrelevant to this table and the information I need is in the detail.

 Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'table_category_id = and tn_us = TRUE'.

/docs/html/qodbc/config/include/ado.runtime6.asp, line 190

Then, when I do any query on transaction through the VB Demo 32 is hangs both the demo as well as Quickbooks.  I have to exit both via task manager.  I tried right-clicking on the QODBC driver and choosing stop query, but even after waiting patiently, nothing changes.

As far as the driver status, there are two windows.  The first says Transaction Waiting on Quickbooks 0 of 0, and never changes.  The second says Transaction Optimizing Table 0 of 0 and never changes.

I can probably get what I want from other tables or SP's, but a lot of what I am doing requires access to many different transaction types and it would be nice to have one place to reference this.

Thanks

The Transaction table tends to be very big, try:

SELECT * from Transaction UNOPTIMIZED where TnxDate > {d'2008-09-30'} and TnxDate < {d'2009-01-01'}

Thanks.  This works.

But I want to use the transaction table and I need it to be fast.

Is it better to do the selects off the various tables such as bill, invoice or, where needed, unions of those rather than transaction table?  When is it appropriate to use the transaction table?

So, do I optimize first with sp_optimizefullsync transaction?

When I do that, it says "Busy Optimizing" but I cannot tell if it is doing anything.

The driver status says  Transaction "Waiting on Quickbooks" 0 of 0.  Is this normal?

When do you use the Nosync keyword to speed up queries?  What is the downside of using NOSYNC in SELECT queries?

sp_report CustomTxnDetail show Date, Account, AccountType, Amount, Class
parameters DateFrom = {d'2005-01-01'}, DateTo = {d'2005-12-31'},
SummarizeRowsBy = 'Account'

sp_report CustomTxnDetail show Date, Account, AccountType, Amount, Class
parameters DateFrom = {d'2006-01-01'}, DateTo = {d'2006-12-31'},
SummarizeRowsBy = 'Account'

sp_report CustomTxnDetail show Date, Account, AccountType, Amount, Class
parameters DateFrom = {d'2007-01-01'}, DateTo = {d'2007-12-31'},
SummarizeRowsBy = 'Account'

sp_report CustomTxnDetail show Date, Account, AccountType, Amount, Class
parameters DateFrom = {d'2008-01-01'}, DateTo = {d'2008-12-31'},
SummarizeRowsBy = 'Account'

and if you're interested in just one account do:

sp_report CustomTxnDetail show Date, Account, AccountType, Amount, Class
parameters DateFrom = {d'2008-01-01'}, DateTo = {d'2008-12-31'},
SummarizeRowsBy = 'Account' where Account = 'AccountName'