I'm putting my application into production now.

My optimizer settings are "Start of Every Query".  Nothing else checked.

I press "Load Updated Data", it completes successfully.

I do this query and it gives me an old balance (doesn't match Qbooks reports)

Select sum(balanceremaining) as vResult from Invoice nosync  where Ispaid = false"

I do this query without NoSync and it gives correct number:

Select sum(balanceremaining) as vResult from Invoice  where Ispaid = false"

I try SP_OptimizeUpdateSync Invoice, which completes successfully.  Then again the Nosync Query does not pick up the correct balance even though I just synchronized.

I try exiting everything.  Same problem.

I don't think it worked this way for me in the test environment.  Is this because I'm in multi-user mode and other users are logged in?

You have been directed to read How do I setup the QODBC Optimizer? Where are the Optimizer options? several times now. You have been told about Dirty Reads which clearly shows the Invoice.BalanceRemaining column as a dirty read. Dirty reads are columns that QuickBooks changes without changing the timemodified stamp of the row. In all mainstream databases this simply is not acceptable (Oracle, IBM DB2 would be out of business if they did this), however Intuit has chosen not to rectify this for six years now. 

SP_OptimizeUpdateSync Invoice can't and will not detect invisable changes made in QuickBooks.

Select sum(balanceremaining) as vResult from Invoice verify where Ispaid = false

will rebuild the optimized table and will always be correct when compared to a QuickBooks reports at the same point of time.

I have read the post to which you referred me each time you sent it.  I have gone back and read/referenced it at least 10 more times.  I have spent hours experimenting with every possible combination, but I am completely stuck on one area, so I hope you can help me.

Your references to dirty read fields, the unofficial list of which is in the referenced post, is acknowledged and understood.

I then set out to use fields not on the list with no success.

In the case of the bill table I can get around the problem by doing a fullsync on the billtopay table.  That table is not too big and a fullsync doesn't take too long.  But, in the case of the invoice table, a fullsync is not practical.  There are 10,000+ invoices and it takes over an hour for that table alone.  And, as you have said, generally speaking it is "silly" to do a fullsync everyday.  The way I am building my application, it is an iterative process requiring Qbooks changes in between, so frequent fullsync of just the invoice table is not practical but I still need very fast queries.

So, I have narrowed my problem down to the invoice table, "subtotal" field.  This does not appear to be or behave like a dirty read field.  It does not turn "null" when that optimizer setting is set to null.

When I run "load updated data" on the invoice table combined with a NOSYNC query, the Invoice.Subtotal field does not see any of the updated data. 

When I load updated data on the invoice table combined with a UNOPTIMIZED query, the Invoice.Subtotal field does see the updated data, but takes a lot longer.

So, I deduce from this that NOSYNC applies to all fields, not just the dirty fields, and tells QODBC to look at the last version of the data since a fullsync (hence the speed) and ignore the "load updated data".

Therefore, there is no way for me to use the speed of NOSYNC in combination with "load updated data" on any fields.

Since frequent fullsync on the invoice table is impractical,  and the speed of SP_REPORT is also impractical,  my only option are to use the slower "UNOPTIMIZED" option even with fields like Invoice.Subtotal.  Correct?

For me, this means I will need to re-write my application to use the slower UNOPTIMIZED query one time into an array and reference the array thereafter from there (which is instant, but takes more programming).  This is several hours of programming, so before I undertake that, please confirm whether I now have an accurate understanding of NOSYNC for non-dirty fields like Invoice.Subtotal.

The following was my unsuccessful attempt to get around the balanceremaining with non-dirty fields, but maintain nearly instant query speed because I repeat the query many times while the user is waiting (custom Excel spreadsheet function).

(above gives me the original amounts)

select sum(linkedtxnamount) from invoice nosync, invoicelinkedtxn nosync where Invoice.ispaid = false and Invoice.txnid = invoicelinkedtxn .txnid and linkedTxnTxnType <> 'SalesOrder'

(above gives me the credits/payments against the original amounts)

Net of these two numbers gives me the correct number, but NOSYNC does not work, only the slower NONOPTIMIZE.

I apologize in advance if you believe the answer to this question is obvious in the post you sent me.  I couldn't see it as hard as I tried.  Thank you.

VERIFIED | VERIFY - Forces Full Resync with QuickBooks on the optimized table before Query starts
CALLDIRECT | UNOPTIMIZED - Passthru query directly to QuickBooks - use no optimizations
OPTIMIZE | OPTIMIZED | NOSYNC - Passthru query to optimized table directly for maximum speed

And from what you say, it looks like subtotal should be in the dirty read category and we should correct it.

Well, I tried doing this:

1.  Full Sync on all tables.

2.  Add an invoice, change an invoice amount, delete an invoice (all three different).

3.  Run an update sync on table invoice.

4.  Run a NOSYNC select on subtotal.

When I do it in a controlled test like this, the subtotal is correct with NoSync. 

But, when I try this on many changes after a day or two of active user change to Qbooks, the NOSYNC subtotal does not give me a correct total (but a UnOptimized does).  I guess I need to run detailed queries and try to find the difference. 

Does this give you any clues that might help me?

After an update sync, do you get random/unreliable results with dirty read fields, or just "old" results?

Why does setting the RealTime option on the dirty fields Optimizer options not change my result?

It really all boils down to how QuickBooks is used. If everyone follows true accounting/auditing procedures, like for an example, once an invoice has been issued it can't be modified, it can only be credited or additional items added to a new invoice, then everything works well. It's the fiddling of the accounts that causes the problems.

Dirty fields return unreliable results, Intuit does not state why.

The RealTime (Read direct) option is best set in your connection string as often the first connection settings are stored withn applications like MS Access etc:-

Optimizer Tab
OptimizerOn = Yes Or No
OptimizerCurrency = M (Manual), O (Per Connection), Y (Yesterday), L (Last Month), R (Per Connection with a Reload first), C (Current meaning every query)
OptimizerDBFolder =
OptimizerEncryptionOn = Yes or No
OptimizerForceDelete = Yes or No
OptimizerAllowDirtyReads = N (Nulls), D (Dirty) or R (Read direct)
OptimizerSyncAfterUpdate = Yes or No

See: How do I build a connection string with all the available QODBC parameters? for more.

Understood about how Qbooks is used. 

In this case, I am using ADO from Excel VBA using a DSN.

Before I attempt a DSN-less connect string, will the read direct option potentially help me?

From your post: "Real Time" uses the slower method to get the data directly out of QuickBooks making sure you get the most current value for the requested fields. (Real Time is the previous method used).

Does "slower method" mean it's the same as doing a query with UNOPTIMIZE as far as performance?

Premier.  US.  2007.