I am using Access 2003 and QODBC to query the invoice, invoiceline, receivePayment and receivePaymentLine and on my machine my VBA code works flawlessly. However, when I put it on the clients machine with the QODBC driver located on the client and the QuickBooks database working on the server then the behavior was very erractic with periodic crashes in Access and times when the program would just hang. I tried several trouble shooting steps and the last one i tried was running a full optimization using the program QQODBCUPD.EXE. After this it seems to be working fine for the times I have tested since running this. However, in reading another post I found a statement from an Admin that said:

Nightly updates are silly, your application should do a resync when it first runs against each table it uses and then only use NOSYNC.I just setup this process to run nightly and i am wondering if this is even necessary. I don't want to do this unless it is a recommended best practice for the queries that I am performing. Each month my client needs to generate salesman commision reports and this report is generated using the tables that I mentioned above. I am wondering if this daily optimization would be helpful or if you would recommend it. Also I join the invoice table to the salesRep table with a left outer join and I am wondering if i were to use the nosync keyword in my query where it would fit. Here's my query:

  sql = "SELECT SalesRep.SalesRepEntityRefFullName, Customer.FullName, Invoice.RefNumber, Invoice.TxnDate, Invoice.Subtotal, Invoice.TxnID" _
& " FROM Customer INNER JOIN (Invoice INNER JOIN SalesRep ON Invoice.SalesRepRefListID = SalesRep.ListID) ON Customer.ListID = Invoice.CustomerRefListID" _
& " WHERE (((Invoice.TxnDate) Between" & startDate & " And " & endDate & "))" _
& " ORDER BY SalesRep.SalesRepEntityRefFullName, Customer.FullName, Invoice.TxnDate;"

would this be correct:

Customer nosync INNER JOIN (Invoice nosync INNER JOIN SalesRep nosync  ON Invoice.SalesRepRefListID = SalesRep.ListID) ON Customer.ListID = Invoice.CustomerRefListID

i know that I have several questions mixed into this post but, if you could give me some guidance and point me to some recommended best practices on this stuff it would be very helpful.

For MS Access 2003 or 2007 to work correctly you must first check that you are running QODBC v9.0.0.253 or higher (see: How can I get the latest version of QODBC? ) and then change your optimization settings to use the The start of every new connection (with "Load Updated Data" first) option and confirm that the Multi-Table Sync option has been disabled. If not, uncheck the Multi-Table Sync option as this is often defaulted on (checked) with QuickBooks Enterprise and the QODBC Enterprise Desktop Edition.

Once this has been done, unlink all the QODBC tables in your existing MS Access .mdb(s) and then relink them to use these new connection settings.

Just use simple joins instead of INNER JOINs, see also: Differentiate between a payment and a discount to pay commissions 

The reason for the complex joins is because sometimes you have salesman and sometimes you don't and if i use simple joins and there's no salesman then I don't get the invoice record so with left outer joins i can get all the invoices and only the salemans that are populated. can you suggest another way to achieve this would it be better to have a separate query to fill in the salesman? this would just add more processing time to an already very slow process.