I am designing a new database that uses a pass-through query that accesses the invoiceline table. It works fine,  but it often wants to start reloading data from Quickbooks for even the smallest reason. For example, I'm designing the report for this, and it will start trying to access Quickbooks even when I just grab a column from the field list. This is according to the driver status box for QODBC. In the QODBC status box, it appears that after it says "Waiting for Quickbooks," an operation occurs that includes what looks like the word "XML". Maybe that's normal, but I don't think my first database does that. I am using an added date column from Quickbooks called service date and wondering if that might have something to do with it. The other database I previously designed using the same method works great and doesn't try to access Quickbooks so frequently like this one does. Here is the SQL of my pass-through query:

Select Customerreffullname, ShipAddressCity, shipdate, invoicelineservicedate, InvoiceLineItemRefFullName, TemplateRefFullName from invoiceLine unoptimized WHERE Txndate >= {d '2006-02-01'}

And just in case it might help, the SQL for my select query:

SELECT qrybininvoiceline.ShipAddressCity, Max(qrybininvoiceline.shipdate) AS MaxOfshipdate, tblbins.Customer, Max(qrybininvoiceline.invoicelineservicedate) AS MaxOfinvoicelineservicedate, qrybininvoiceline.InvoiceLineItemRefFullName, qrybininvoiceline.TemplateRefFullName, IIf([maxofinvoicelineservicedate] Is Null,Date()-[maxofshipdate],Date()-[maxofinvoicelineservicedate]) AS DaysSinceLastPickup, tblbins.NumberofBinsOut
FROM tblbins LEFT JOIN qrybininvoiceline ON tblbins.Customer = qrybininvoiceline.Customerreffullname
GROUP BY qrybininvoiceline.ShipAddressCity, tblbins.Customer, qrybininvoiceline.InvoiceLineItemRefFullName, qrybininvoiceline.TemplateRefFullName, tblbins.NumberofBinsOut
HAVING (((qrybininvoiceline.InvoiceLineItemRefFullName) Like "WW:Bin"));

Thanks!

QODBC isn't calling QuickBooks all the time here, whatever you are using is! For example when using MS Query we turn off the auto-query mode for the exact same reason, because everytime you use a control it will re-query the connection or worst still re-run the query!

Ok, I'm using Access. I searched for a similar option as in MS Query but not really seeing any way to to turn off auto query. There is an ODBC refresh interval time in Tools>Options, but it's set at 1500 seconds, so that's not the prob. I mssed around with the DDE options, but that doesn't do anything (and I don't really know what that does anyway) Any idea how to turn off Auto Query in Access?

I had mentioned earlier that my first database wasn't doing that, but I realized that was because I was running my reports off of a union query which was being run off of tables created by make table queries. So, in that case, it's not directly linked to a Pass-Through Query to Quickbooks.

Try switching the pass-through query to use the QODBC cached local optimized tables while you are in design mode:-

Select Customerreffullname, ShipAddressCity, shipdate, invoicelineservicedate, InvoiceLineItemRefFullName, TemplateRefFullName from invoiceLine NOSYNC WHERE Txndate >= {d '2006-02-01'}

When I do that, it's wanting to run through all of the 20,000+ records again. Do I need to optimize it again, then switch to No Sync?

No, you just need to let it run, but then when you do design changes it will use the local cached version which is faster than calling QuickBooks.

Or to prehaps put it more bluntly, prehaps you should use a smaller company file to do the design work with.

Yes, I agree about the smaller company file. Unfortunately, we've never been able to get our company file to condense since we started using it in 1997 and Quickbooks support hasn't been able to tell us why it won't work. Fun stuff...

Thanks again for the help!