Trying to execute the following passthrough query.  When I execute it the ODBC connection dialog box pops up.  Not sure whether to specific the ODBC connection to the MS SQL db that the access db is connected to or the ODBC driver to Quickbooks.  Here is the passthrough query.  tb_invoicesummary is ms sql table linked via ODBC, QS_CustomerRefListId_ClientId is a access query, Invoice is QB table via QODBC.  Maybe there is a better way to do this???  Thanks,  Paul

INSERT INTO Invoice ( RefNumber, TxnDate, ARAccountRefListID, TemplateRefListID, TermsRefListID, CustomerRefListID, CustomerRefFullName, IsToBePrinted )
SELECT tb_InvoiceSummary.InvoiceNumber AS RefNumber, tb_InvoiceSummary.InvoiceDate AS TxnDate, '500000-1010789941' AS ARAccountRefListID, '30000-1010773564' AS TemplateRefListID, '70000-1010795880' AS TermsRefListID, QS_CustomerRefListId_ClientID.ListID AS CustomerRefListID, QS_CustomerRefListId_ClientID.FullName AS CustomerRefFullName, 0 AS IsToBePrinted
FROM (tb_InvoiceSummary INNER JOIN QS_CustomerRefListId_ClientID ON tb_InvoiceSummary.ClientID = QS_CustomerRefListId_ClientID.ClientID) INNER JOIN tb_Client ON tb_InvoiceSummary.ClientID = tb_Client.ClientID
WHERE (((tb_InvoiceSummary.InvoiceNumber)='13078'))

Not a problem, change the "ODBC Connect Str" to call QODBC like this:

ODBC;DSN=QuickBooks Data;SERVER=QODBC

see: How do I create a Pass-Through Report using Microsoft Access 2003? for more.

OK, I fixed the connection string problem.  I tried a new query that uses a non-linked access table (tt_InvoiceSummarQB) to append data to QuickBooks (Invoice).  I'm no longer getting the "select data source" dialog box, but now it cannot find the local access table (tt_InvoiceSummarQB).  I get the message "Invalid table name:tt_InvoiceSummarQB".  Do I need define a second connection string to point to the access db?  If so, how do I do that?   Here is the SQL that I'm trying to run.

INSERT INTO Invoice ( RefNumber, TxnDate, ARAccountRefListID, TemplateRefListID, TermsRefListID, CustomerRefListID, CustomerRefFullName, IsToBePrinted )
SELECT tt_InvoiceSummarQB.RefNumber, tt_InvoiceSummarQB.TxnDate, tt_InvoiceSummarQB.ARAccountRefListID, tt_InvoiceSummarQB.TemplateRefListID, tt_InvoiceSummarQB.TermsRefListID, tt_InvoiceSummarQB.CustomerRefListID, tt_InvoiceSummarQB.FullName, tt_InvoiceSummarQB.IsToBePrinted
FROM tt_InvoiceSummarQB

Thanks,  Paul

OK  Changed the query type.  Actually this is where I started.  When I test the query it works fine (albiet slow).  When I try to execute the query I get the following error.  "ODBC--insert on a linked table 'Invoice' failed.  [QODBC] Not supported (#10003)"  All the required fields are there.  Here is the SQL view of the "Append Query".  Thanks,  Paul

INSERT INTO Invoice ( RefNumber, TxnDate, ARAccountRefListID, TemplateRefListID, TermsRefListID, CustomerRefListID, CustomerRefFullName, IsToBePrinted )
SELECT tb_InvoiceSummary.InvoiceNumber AS RefNumber, Format([InvoiceDate],"Short Date") AS TxnDate, "500000-1010789941" AS ARAccountRefListID, "30000-1010773564" AS TemplateRefListID, "70000-1010795880" AS TermsRefListID, QS_CustomerRefListId_ClientID.ListID AS CustomerRefListID, QS_CustomerRefListId_ClientID.FullName AS CustomerRefFullName, 0 AS IsToBePrinted
FROM tb_InvoiceSummary INNER JOIN QS_CustomerRefListId_ClientID ON tb_InvoiceSummary.ClientID = QS_CustomerRefListId_ClientID.ClientID
WHERE (((tb_InvoiceSummary.InvoiceNumber)>=[Enter First Invoice]))
ORDER BY tb_InvoiceSummary.InvoiceNumber;

Sorry, you can't insert directly into the Invoice table, the Invoice lines need to be inserted into the InvoiceLine table. And the columns names need to be in the same order as the table.

To create a invoice you just need three things:

1: The name of the customer you what to invoice (from the Customer table);
2: The name of the part or service to invoice (from the Item table);
3: The name of the sales tax code to use (from the SalesTaxCode table).

Providing all these things already exist in your QuickBooks company file, you don't need to read anything out of QuickBooks. You simply do one insert statement using the three things above. For example:

INSERT INTO "InvoiceLine" ("CustomerRefFullName", "RefNumber",
"InvoiceLineItemRefFullname", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefFullName", "FQSaveToCache")
VALUES ('Data Access Worldwide', 'QODBCINV1', 'Repairs', 'Repair NoteBook Computer',
200.00000, 200.00, 'NON', 0) 

In MS Access this can also be run using DoCmd.RunSQL:

DoCmd.RunSQL "INSERT INTO InvoiceLine (CustomerRefFullName, RefNumber, " & _
    "InvoiceLineItemRefFullname, InvoiceLineDesc, InvoiceLineRate, " & _
    "InvoiceLineAmount, InvoiceLineSalesTaxCodeRefFullName, FQSaveToCache)" & _
    "VALUES ('My.CustomerRefFullName', 'My.RefNumber', " & _
    "'My.InvoiceLineItemRefFullname', 'My.InvoiceLineDesc', 'My.InvoiceLineRate', " & _
    "'My.InvoiceLineAmount', 'My.InvoiceLineSalesTaxCodeRefFullName', 0)"

and the following invoice was created in QuickBooks with all the customer details "auto-populated":

OK  My logic was that I was going to add the parent invoice record to the invoice table, store the foreign key and then add the children (individual line items) to the invoicelineitem table.  So do I do the same thing, but insert everything to the invoicelineitem table?  Do you have an example of adding more than one line item to a isingle invoice?  Thanks,  Paul

PS  If I had the server edition of QODBC, could I do all this in a stored proceedure?

Sure, see: How do I create Invoices? 

Regardless of what edition of QODBC, the procedure is the same. You can't create you own stored procedures in QODBC, the ones we have are hardcoded for QODBC users to use.