Tom,  I am making great progress with my major script. 

2 Questions:

How can I retrieve Specific Invoices based on a list of RefNumbers.  Such as:  I want to retrieve Invoice Numbers: 145, 146, 155, 167, etc.  Is there a way to select based on simple statement to retrieve select invoices?

Also, is there a way to set the optimizer to Only reload certain tables?  I am only dealing with a few tables and think it wastes it's time retrieving from all the tables.

Thanks in advance

Try:

SELECT * FROM Invoice where RefNumber in ('145','146','155','167')

The optimizer only updates tables you use automatically. See: How do I setup the QODBC Optimizer? Where are the Optimizer options? for more information.

From a previous Post, I inquired about the SLOW process of pulling data (up to hours) with the following statement for pulling multiple INvoice numbers.  Yet another post you told me the "in" statement scans the whole table.  So what is the correct way to pull, quickly, multiple invoices.

This is what i have: 

Sub test2()
'
' test2 Macro
' Macro recorded 11/14/2007
Dim invNum As String

'the following wont run
invNum = "'14570','14571'"

'MsgBox "invNum:" & invNum
    With Sheets(3).QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=QuickBooks Data;SERVER=QODBC;OptimizerOn=No;OptimizerDBFolder=%UserProfile%\QODBC Driver for QuickBooks\Optimizer;Optimizer" _
        ), Array( _
        "Currency=R;OptimizerAllowDirtyReads=D;OptimizerSyncAfterUpdate=Y;SyncFromOtherTables=N" _
        )), Destination:=Sheets(3).Range("$A$1"))
        .CommandText = Array( _
        "SELECT InvoiceLine.TxnID, InvoiceLine.TimeCreated, InvoiceLine.TimeModified, InvoiceLine.TxnNumber, InvoiceLine.CustomerRefListID, InvoiceLine.ClassRefListID, InvoiceLine.ClassRefFullName, InvoiceLine" _
        , _
        ".ARAccountRefFullName, InvoiceLine.RefNumber" & Chr(13) & "" & Chr(10) & "FROM InvoiceLine InvoiceLine" & Chr(13) & "" & Chr(10) & "WHERE ((InvoiceLine.RefNumber in (" & invNum & ")))")
        .Name = "Query from QuickBooks Data_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

How can i make it run faster by making the statement  use = instead of "in"

((InvoiceLine.RefNumber in (" & invNum & ")))")

I have no idea why you want certain invoice numbers in the first place. Normally one would extract invoices by customer and/or by transaction date like this:

SELECT * FROM Invoice
where CustomerRefFullName = 'Data Access Worldwide'
and TxnDate > {d'2007-11-01'}

but if you still want do it your way, the best SQL statement would be:

SELECT * FROM Invoice where RefNumber = '145'
or RefNumber = '146'
or RefNumber = '155'
or RefNumber = '167'

I am using it to Extract all of the Invoiceline data for particular invoices (which are being used as a Pick TIcket) to parse certain fields and convert to a CSV file to Upload to our Shipper's new Electronic system.

So i have a macro created to pull the exact RefNumbers the customer selects and then convert column headings and save and email as a CSV. 

There are about 20 or more customers of the shipper who use quickbooks and will most likely use your driver along with my macro.  So i am trying to speed up the data extraction process.  When it takes hours to pull 4 particular invoices then I will have many complaints. I have played with the Optimizer settings and it is somewhat better now.

Thanks for your assistance.

The fastest way to query the pick ticket list is:

SELECT * FROM Invoice nosync where RefNumber = '145'
or RefNumber = '146'
or RefNumber = '155'
or RefNumber = '167'

providing the invoice optimizer table has already been updated.