I am creating invoices,  doing it according to your help and examples by caching the invoice lines one by one, and then inserting the header last.

The final program is deployed on 4 clients, with the QBdata on a server (Ent 7.0).  The first time I tried it 2 of the clients were like lightning fast, creating the whole invoice in under 10 seconds,  then the other 2 were slow (like 30-40 seconds per invoice line,  and a minute for the header).  Now they are all slow. 

I have the QODBC set identically on all machines ("The start of every new connection (with Load Updated...) and Optimize after insert or update and Dirty reads.

I thought that I read that the ADO open connection string bypasses the JET in Access.  I have merely linked the tables (Invoiceline, Invoice, ItemService, and Customer) in the tables container.  Should I do away with the linking method and use ADO connection instead?

Thanks

Coincidentally I had tried just what you said.   ie switch off the "Optimize after insert or update"  It did not help the speed any.  Check the following code lines and see if you see something that I am missing that would speed things up.

Repeat step one and step 2 for each invoice line

Step one

Private Sub InsertInvoiceLineUsingCustID()
Dim qInsertInvoiceLineUsingCustID_SQL As String
Me.lblMessageBox.Caption = "Writing " & OrderDetails(lineitem, 5) & " " & OrderDetails(lineitem, 3) & " to QuickBooks"
' Inserts into the invoice line table
 'now write the first line item to the invoice line table
 'see qInsertInvoiceLineUsingCustID in query container for editing
 qInsertInvoiceLineUsingCustID_SQL = "INSERT INTO InvoiceLine(CustomerRefFullName," _
                    & "InvoiceLineItemRefFullName, InvoiceLineDesc, Invoice" _
                    & "LineQuantity, InvoiceLineRate, FQSaveToCache )" _
                    & "Values('" & OrderDetails(lineitem, 2) & "', '" & OrderDetails(lineitem, 3) & "', '" & OrderDetails(lineitem, 4) & "', '" _
                    & OrderDetails(lineitem, 5) & "', '" & OrderDetails(lineitem, 6) & "',1)"

   DoCmd.RunSQL qInsertInvoiceLineUsingCustID_SQL <--Is there a faster / better way to do this?  Should I use the customer ID instead of the name??  (Maybe I need the jumpin)

End Sub

Step 2

Private Sub FindNewestLineItemTxnIDByCustomer()
Dim qFindNewestLineItemTxnIDByCustomerSQL As String
Dim y As Integer
  'now find out what TxnID, ainvoicelineitemreflistid, and RefNum  that quickbooks
  'assigned to the first invoiceline by using the customer name and looking for last txn.
  qFindNewestLineItemTxnIDByCustomerSQL = "SELECT TOP 1 InvoiceLineTxnLineID from InvoiceLine " _
                                & "UNOPTIMIZED where CustomerRefFullName=(('" & OrderDetails(lineitem, 2) & "'))" _
                                & "ORDER BY TimeCreated DESC;"

Set rec = db.OpenRecordset(qFindNewestLineItemTxnIDByCustomerSQL)
rec.MoveFirst
OrderDetails(lineitem, 7) = rec(0) 'the InvoiceLineTxnID
rec.Close
aQBRefNum = RefNum
End Sub

Step 3

Private Sub InsertHeaderIntoInvoiceTable()
Dim InsertHeaderSQL As String
Me.lblMessageBox.Caption = "Creating Invoice Header"
InsertHeaderSQL = "INSERT INTO " & "Invoice" & " (" & "CustomerRefListID" _
                 & "," & " CustomerRefFullName" & ", " & "TxnDate" & ", " _
                 & "PONumber" & ", " & "ShipDate" & ", " & "CustomFieldOther" & ", " & "CustomFieldDeliveryTime" & ", " _
                 & "CustomFieldFOBorDEL" _
                 & ") VALUES ('" & aCustID & "','" & aCustFullName _
                 & "','" & aTxnDate & "','" & aPONumber & "','" & aShipDate & "','" & aDelDate & "','" & aDelTime & "','" & aFOBorDEL & "')"
  
 
DoCmd.RunSQL InsertHeaderSQL  <--Is there a faster / better way to do this?
End Sub

Another thing that is weird (to me at least),  is that I decided to make 'copies' of the customer list and the item list within the Access application,  this way I don't need to query the tables from QODBC.  So I provided the users with a cmd button that they can click to update the copies if they have changed the customers or items in quickbooks.  This utilizes a make table query and is surprisingly pretty fast compared to writing the invoice lines.

Using QODBC to write  an invoice to quick books for 4 sales   lineitems should not take 5 minutes should it???  If it does I may have to find an alternate solution. 

Ok, I have no idea what you mean by slow here. For starters, invoicelines with FQSaveToCache set to True (1) are not written until the whole transaction is committed. I've been writing transactions for years now in three versions of QuickBooks and haven't seen a new invoice take any longer that saving a new invoice using the QuickBooks User Interface. They all happen within seconds within QODBC.

Some customers have systems that have to write more than 6,000 invoice lines from external systems within a day. I think you are loading QuickBooks into the background using unattended mode for every executed SQL command. QuickBooks should only be loaded once, and you need to ensure your General Setup Tab in the QODBC Setup Screen has the company file set as "Use the company file that's now open in QuickBooks".