The main issue with using MS Access is that users tend to use datasheet views instead of using pass through queries and forms which run a whole lot faster. See: How do I create a Pass-Through Report using Microsoft Access 2003? for more.
By using pass through queries you can call QuickBooks directly and you can also override your QODBC Optimizer configuration screen settings to suit what you are actually doing by using the following tags:
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
Examples:
select * from InvoiceLine UNOPTIMIZED select * from InvoiceLine NOSYNC select * from InvoiceLine VERIFIED
To read all the InvoiceLines directly out of QuickBooks use:
select * from InvoiceLine UNOPTIMIZED
Or you can resync your optimized InvoiceLine table by first doing:
sp_optimizefullsync InvoiceLine
then read directly out of the optimized table by doing:
select * from InvoiceLine NOSYNC
This is very f...a.......s.................t.
Another good example of using NOSYNC would be to get for example all invoicelines before 2003:
SELECT * from InvoiceLine NOSYNC WHERE Txndate < {d '2004-01-01'}
as invocies before 2004 don't ever change, you can read them directly from the QODBC Optimizer. You just need to use unoptimized for crtical operations. |