Examples of Creating Invoice using QODBC

First, please create a new MS Access database & link ALL tables.
Please refer to the below-mentioned article for How to Use QODBC with Microsoft Access:

How to Use QODBC with Microsoft Access

After linking the table, we need to execute the query in the MS Access query editor:
MS Access>> create tab >> Query Design >> It should pop up for choosing a table. Please click the "Close" button & click on "SQL" from the left top corner below the File menu.

Note: For multi-line invoices, some may find it easier to NOT include the header information with each line item, but rather to have separate smaller commands for the Invoice Lines and a final  INSERT for the Invoice Header.

Create an Invoice with 3 Lines

     Here we create an invoice with three lines by using 3 InvoiceLine INSERT commands (with the FQSaveToCache caching flag set true) followed by the Invoice table (header) insert, which is automatically designed to pull in all cached InvoiceLine records and save immediately.

First Line

INSERT INTO InvoiceLine (InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('320000-1071525597', 'Building permit 1', 1.00000, 1.00, '20000-999022286', 1)

Second Line

INSERT INTO InvoiceLine (InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('320000-1071525597', 'Building permit 2', 2.00000, 2.00, '20000-999022286', 1)

Third Line

INSERT INTO InvoiceLine (InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('320000-1071525597', 'Building permit 3', 3.00000, 3.00, '20000-999022286', 1)

Header Information

INSERT INTO Invoice (CustomerRefListID, ARAccountRefListID, TxnDate, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, IsPending, TermsRefListID, DueDate, ShipDate, ItemSalesTaxRefListID, Memo, IsToBePrinted, CustomerSalesTaxCodeRefListID) VALUES ('620000-1071526513', '40000-933270541', #9/23/2005#, '1', 'Brad Lamb,' '1921 Appleseed Lane', 'Bayshore,' 'CA,' '94326', 'USA,' 0, '10000-933272658', #10/31/2005#, #10/01/2005#, '360000-1071521681', 'Memo Test,' 0, '10000-999022286') 

Results in QuickBooks 

Related Data Location

InvoiceLineItemRefListID

InvoiceLineItemRefListID is '320000-1071525597' and found like this:

     Select ListId, FullName from Item where FullName like 'C%'

InvoiceLineSalesTaxCodeRefListID

InvoiceLineSalesTaxCodeRefListID is '20000-999022286' and found like this:

     Select ListId, Name from SalesTaxCode where Name like 'N%'

CustomerRefListID

CustomerRefListID is '620000-1071526513' and found like this:

    Select ListId, FullName from Customer where FullName like 'Smi%'

ARAccountRefListID

ARAccountRefListID is '40000-933270541' and found like this:

     Select ListId, Name from Account where Name like 'Account%'

ItemSalesTaxRefListID

ItemSalesTaxRefListID field is related to ItemSalesTax, ItemSalesTaxGroup, and ItemSalesTaxGroup,Line depending on InvoiceLineType. If InvoiceLineType is GroupItem, then it is found in the ItemSalesTaxGroupLine table.

ItemSalesTaxRefListID is '360000-1071521681' and found like this:

     Select ListId, Name from ItemSalesTaxGroup where Name like 'E%'

CustomerSalesTaxCodeRefListID

CustomerSalesTaxCodeRefListID is '10000-999022286' and found like this:

     Select ListId, Name from SalesTaxCode where Name like 'T%'