We need to read all QB transactions for our GL.  To do this we use the report query Txn Detail By Account.  I break down the report into a series of months (periods), so that I can get back a timely response from QODBC.  My problem is tryng to determine the date of the first transaction.  My only method is to work forward from some fixed year, but that very time consuming.  My alternate plan was to read a series of transaction tables and examine the first record.  For that method to work, I’d like to read only a single record that has the earliest date.  Is there a way to tell QODBC to return a single data record from query?

Ok, you're going to have to work it back from either each of the subledgers.

For the oldest invoice:
SELECT Top 1 TxnDate FROM Invoice order by TxnDate ASC
Oldest Payment:
SELECT Top 1 TxnDate FROM ReceivePayment order by TxnDate ACS
Journal Entry:
SELECT Top 1 TxnDate FROM JournalEntry order by TxnDate ACS
Bill:
SELECT Top 1 TxnDate FROM Bill order by TxnDate ACS

Or the easy way by each Account name:
SELECT Top 1 TxnDate FROM Transaction where AccountRefFullName='Checking' order by TxnDate ACS

But if you're just trying to find the oldest transaction date, then just do:
SELECT Top 1 TxnDate FROM Transaction order by TxnDate ACS

Note: The Transaction table is only available in USA versions of QuickBooks 2006 and 2007 or higher.

Thanks, we used

SELECT TOP 1 TxnDate FROM Transaction ORDER BY TxnDate ASC

but quit after 40 minutes because the query had not yet finished. We are dealing with large tables with the data on our local drive.

What we are really trying to determine, quickly, is the earliest transaction so that subsequent queries can be seeded with that date. It might be enough if we could find out when the data was created or what was the first fiscal year for which there is data.

Ok, try fully optimizing the Transaction table by running:

sp_optimizefullsync Transaction

first overnight and then when the optimization is complete run:

SELECT TOP 1 TxnDate FROM Transaction NOSYNC ORDER BY TxnDate ASC

Use the nosync tag to read the optimized SQL Lite tables directly.

Thanks for the advise...

However our product, Adagio FX, can not rely on the fact that the data has been optimized. Mostly, this is because the data may have been sent from a "client" site to their "accountant" for Financial Statement preparation.

Also, our queries are mostly "report queries" that, from my understanding, do not take advantage of the optimized data.

For example:

sp_report TxnDetailByAccount show AccountListID, AccountNumber, AccountType, AccountSpecialType, Class, RowType, TxnType, Date, RefNumber, Memo, Amount parameters DateFrom = {d'2007-01-01'}, DateTo = {d'2007-12-31'}

In our testing we are able to import 120,000 "Transaction" details into MS Access in about 10 minutes using QODBC, which is acceptable.

But running the query:

SELECT TOP 1 TxnDate FROM Transaction ORDER BY TxnDate ASC

took 40+ minutes when running the VBDemo.

I expected the timing numbers to be much more similiar. Is this because MS Access is using the "optimized SQL Lite" tables? 

The execise here is to ping for the oldest transaction date, so the better solution would be to guess from the oldest likely year forward like:

SELECT TOP 1 TxnDate FROM Transaction where TxnDate < {d'1998-01-01'}  ORDER BY TxnDate ASC

and if no value try:

SELECT TOP 1 TxnDate FROM Transaction where TxnDate < {d'1999-01-01'}  ORDER BY TxnDate ASC

and so on, until a value is returned so you can determine the first year block to use in:

sp_report TxnDetailByAccount show AccountListID, AccountNumber, AccountType, AccountSpecialType, Class, RowType, TxnType, Date, RefNumber, Memo, Amount parameters DateFrom = {d'1998-01-01'}, DateTo = {d'1998-12-31'}

Using the where TxnDate < {d'1998-01-01'}  allows QODBC to jump into the right set of transactions in QuickBooks so the returned XML document isn't a mountain.