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.