I am trying to run this query in both VB Demo and vba and I get "unspecified error" when the optimizer is off.  I turned it off because it was taking way too long to process new invoice creation.

SELECT ListID, Name FROM Item WHERE Name = 'MT-LINE'

MT-LINE is a valid item in my item table.  What can I check?  Suggestions?  I am running QB 2008 Enterprise and QODBC ver 8.

Turn it back on!!! The Item table is a view that needs to be processed using multiple tables or use the ItemInventory, ItemService, ItemNonInventory tables instead.

Thats fine, but how do I speed it up then?  I processed 16 invoices with maybe 10 lines each and it took over an hour!  I've seen a lot of posts about the optimizer and how slow queries process.

What are "optimal" settings for the optimizer?  Can you tell me more what you mean about using the ItemInventory, ItemService and ItemNonInventory tables instead of the Item table?

I am using an Excel spreadsheet to process roughly 100 invoices a month.  I've written a script that will loop through the 100 + customers in the spreadsheet and look for any daily "activity" to add to their invoice.  Based on the time it took to process only 16 customers, this will take over 6 hours to process !!  Suggestions?  Should I bypass the optimizer?  I guess I don't completely understand the optimizer.

Thanks

There's currently a bug using the item table without the optimizer on. So all I'm saying is to use the other item tables instead of the Item view if you want to work without the optimizer on. The QODBC optimizer is in the third year of being implemented and was added to QODBC to make it faster. Queries that took over three days ran within 20 minutes using the optimizer. There are a lot of options on how the optimizer can be setup and they are explained in detail in: How do I setup the QODBC Optimizer? Where are the Optimizer options? 

I suggest you take a sample of your insert statements and execute them using VB Demo so you can see that inserts take seconds to complete. To see what the optimizer is doing (or better still how it works) check the "Verbose Optimizer Status Panel" on for now. This causes the Optimizer panel to come up whenever optimizer of any kind happens. The normal check box only pops up the optimizer when doing full update or reload all.

I took your suggestion and used the ItemService table with the Optimizer off.  It sure did speed things up.  Now the problem I'm having is when I process multi line invoices I get this error:

"Multiple step operation generated errors. Check each status value."

I look at the invoce in QB and all the line items are there though.  I use an excel spreadsheet with customers on separate rows and the days of the month in columns containing a number to insert as a line item in the invoice.  I loop through each column and if it contains a number I do this (FQSaveToCache = True):

oRecordset2.AddNew
oRecordset2.Fields("CustomerRefListID").Value = CustListID
oRecordset2.Fields("TemplateRefListID").Value = TemplateListID
oRecordset2.Fields("TxnDate").Value = InvoiceShowDate
oRecordset2.Fields("InvoiceLineServiceDate").Value = DateServiced
oRecordset2.Fields("IsToBePrinted").Value = True
oRecordset2.Fields("InvoiceLineItemRefListID").Value = ItemServiceListID
oRecordset2.Fields("InvoiceLineQuantity").Value = LineCount
oRecordset2.Fields("FQSaveToCache").Value = True
oRecordset2.Update

When I hit the last day of the month or the last column I do this (FQSaveToCache = False):

oRecordset2.AddNew
oRecordset2.Fields("CustomerRefListID").Value = CustListID
oRecordset2.Fields("TemplateRefListID").Value = TemplateListID
oRecordset2.Fields("TxnDate").Value = InvoiceShowDate
oRecordset2.Fields("InvoiceLineServiceDate").Value = DateServiced
oRecordset2.Fields("IsToBePrinted").Value = True
oRecordset2.Fields("InvoiceLineItemRefListID").Value = ItemServiceListID
oRecordset2.Fields("InvoiceLineQuantity").Value = LineCount
oRecordset2.Fields("FQSaveToCache").Value = False
oRecordset2.Update

Like I said, all the line items are there in the invoice but the code bombs on the oRecordset2.Update with the error above.  Hence my code will not loop through all my customers (rows) in my spreadsheet.  It bombs out at the update statement for the last line item of every customer.  Please help.  This has me stumped.  I want to say that if I turn the optimizer on it will loop through just fine but take a very LONG time to process.

Where do I need to insert InvoiceLineRate?  Why is the invoice created in QB though?  The problem is that I can't loop through multiple customers.  It bombs on the last insert where FQSaveToCache is False.

I am already creating multiple line invoices from with Excel using vbscript.  You mention InvoiceLineRate.  I am using QB Premium and price levels are set within QuickBooks on a per customer basis.

Please understand, The invoice IS being created just fine using my code that I have provided.  I am getting an error on the last  Recordset.Update statement where FQSaveToCache is set to False.  The last line item IS created on the invoice, but I receive this error:

"Multiple step operation generated errors. Check each status value."

This is preventing me from looping through multiple customers in my spreadsheet and creating invoices for each of them.  Please advise.

Sorry, the error "Multiple step operation generated errors. Check each status value." is not known to me. You need to either state the InvoiceLineAmount or the InvoiceLineQuantity and InvoiceLineRate when creating invoice lines.

See: How do I apply PriceLevel Profile to Invoice Line Items? also.