CODEWITHSUNDEEP
sqlms-accesscsvodbcquickbooks
Daniel
Daniel

Reputation: 73

MS Access error inserting QuickBooks InvoiceLine for single invoice

I am using QODBC connector to enter and alter data in my QuickBooks Company File.

I am trying to create a MS Access script to pull all data out of a csv file and add the csv data as invoices in the table.

I can successfully add new invoices using this code:

    INSERT INTO InvoiceLine (CustomerRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineSalesTaxCodeRefListID) VALUES ('80000078-1311176211','99990','800007D6-1369617126','Description', 2.00, 9.09, '80000009-1311174909')

I am extremely interested in adding to the field TxnNumber as this seems to be the field that allows for multiple items to be added to the same invoice (ie. if 4 entries in InvoiceLine with same TxnNumber and RefNumber, they will show as one invoice.

My attempt at the query is below.

INSERT INTO InvoiceLine (CustomerRefListID, TxnNumber, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineSalesTaxCodeRefListID) VALUES ('80000078-1311176211', '8177', '99990','800007D6-1369617126','Description', 2.00, 9.09, '80000009-1311174909')

Below in the error message that I am seeing:

ODBC--insert on a linked table 'InvoiceLine' failed.
[QODBC] Field not allowed in insert. (#10046)

Upvotes: 0

Views: 365

Answers (1)

Keith Palmer Jr.
Keith Palmer Jr.

Reputation: 27962

The TxnNumber column has nothing to do with tying things back to an Invoice in the QODBC schema.

The field you want is TxnID, not TxnNumber. Use TxnID instead.

Here's a link to the QODBC docs showing the relations:

Upvotes: 1

Related Questions