Forum : Insert Invoices into MS Access ==> QBSearch Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC v7 Independent Self Help Forum

 New Topic 
 
 Post Reply 
[1]  
 Insert Invoices into MS Access ==> QB 
 Author   Message 
  Paul B 
  
 Group: Members 
 Posts: 5 
 Joined: 2007-01-24 
 Profile
 Posted : 2007-05-02 07:09:16

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'))

 

 

  Top 
  Tom Aczel 
  dd4bf_dawworld.jpg
 Group: Administrator 
 Posts: 6554 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-05-02 12:02:03
Sorry you haven't described what dialog? Try looking at: How do I supress Append query messages in MS Access?  

  Top 
  Paul B 
  
 Group: Members 
 Posts: 5 
 Joined: 2007-01-24 
 Profile
 Posted : 2007-05-03 02:17:08
The dialog box that pops up when executing the passthrough query is the ODBC connection "select data source".  The problem is that two connections are required to execute the query... the connection to QB via QODBC and the connection to SQL via access.  Not sure how to establish both connections.  Not even sure that my passthrough query is the correct syntax.  PB 

  Top 
  Tom Aczel 
  dd4bf_dawworld.jpg
 Group: Administrator 
 Posts: 6554 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-05-03 08:21:49

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.

 

  Top 
  Paul B 
  
 Group: Members 
 Posts: 5 
 Joined: 2007-01-24 
 Profile
 Posted : 2007-05-03 10:55:00

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

 

  Top 
  Tom Aczel 
  dd4bf_dawworld.jpg
 Group: Administrator 
 Posts: 6554 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-05-03 11:09:55
I believe the query needs to be a "Append" type query and not a pass-through query in MS Access. Pass-through ignores local tables, try changing the query type. 

  Top 
  Paul B 
  
 Group: Members 
 Posts: 5 
 Joined: 2007-01-24 
 Profile
 Posted : 2007-05-04 03:28:33

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;

 

 

  Top 
  Tom Aczel 
  dd4bf_dawworld.jpg
 Group: Administrator 
 Posts: 6554 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-05-04 09:06:38

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":

 

  Top 
  Paul B 
  
 Group: Members 
 Posts: 5 
 Joined: 2007-01-24 
 Profile
 Posted : 2007-05-04 14:35:07

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?

 

 

  Top 
  Tom Aczel 
  dd4bf_dawworld.jpg
 Group: Administrator 
 Posts: 6554 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-05-04 14:44:45

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.

 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to