Last week an invoice was created on 4/3 using a future date of 4/7 in error.  I have a link to the invoiceline table which I pull information from for sales reporting and noticed the error.  I went into qbooks and fixed the date on the invoice.  However, when I refreshed my report the date had not changed.  I looked into the invoiceline table and found it did not catch the update - as if the table was holding static - not dynamic information.  To correct this problem, I unlinked and re-linked to the table.  After refreshing my report, I found the same 4/7 date on that invoice.  I looked again at the invoiceline table to find this date had still not updated with the invoice.

Is this information static ?  When / how does it update?  How frequently should I expect this? 

My concern is that I am moving forward on developing commissions based on invoice date and items sold.  This is crutial to our moving forward.

Please advise.

Carol Crowley

By the sounds of things you're not using QODBC v6.00.00.155 (or later) yet. You should download the latest version of QODBC as we have made big improvements to the QODBC Optimizer. See: How do I get the latest updated version of QODBC? 

The second issue here is that any critical operation, such as Sales commissions must be done by a forced query statement to call QuickBooks. This is done by using the unoptimized tag after the table, for example:

select * from InvoiceLine unoptimized

or you can force a resync of the InvoiceLine table by doing a:

sp_optimizefullsync InvoiceLine

This is done by doing a pass through query in Access. See: How do I create a Pass-Through Report using Microsoft Access 2003?  The job of the QODBC Optimizer is to allow fast access to lookup and historical information, however it's easy to trick the optimizer, like for example restoring a QuickBooks company file backup from 3 hours ago.