Solved

Updating invoices in Quickbooks using QODBC

Posted on 2014-12-28
9
320 Views
Last Modified: 2015-01-01
I have about 400 invoices for which I need to update the InvoiceLineRate and Memo columns in the InvoiceLine table. I have the data in an Excel spreadsheet with the new information, but am not sure how to update all of the respective invoices in one operation. I have experimented with using the VB Demo, MS Query, MS Access to run a SQL statement, for example

UPDATE InvoiceLine SET InvoiceLineRate = 63.36, Memo = 'Includes Adjustment of $66.64' WHERE RefNumber = '2013157'

It works fine, but I need to know how to change them all at one time, if possible. I have created a linked table in MS Access, and it appears that my best bet may be to somehow update that table from the Excel file, but I don't know how to do that.

Mark
0
Comment
Question by:msibley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 40521461
How about:

UPDATE InvoiceLine SET InvoiceLineRate = 63.36, Memo = 'Includes Adjustment of $66.64'

DO make a backup first, please.

/gustav
0
 
LVL 84
ID: 40521727
Note that gustav's suggestion would update EVERY InvoiceLine, not just specific invoices. From what I read, you want to update a sub-set of invoicelines ...

If so, you'd need a WHERE clause to tell QODBC which invoices to work with. I'm not sure if QODBC supports the IN keyword, but if it does:

UPDATE InvoiceLine SET InvoiceLineRate = 63.36, Memo = 'Includes Adjustment of $66.64'  WHERE Invoice IN ('123', '1234', '12345').

If not, you'd have to loop through each of your invoices, and send out that UPDATE statement for each Invoice, with the RefNumber value included in each WHERE clause.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40521863
As long as the spreadsheet contains the reference numberof the invoice, you would import the spreadsheet.  You have to do this because joining to the spreadsheet would make the query not updateable.  then create a query that joins the QuickBooks table to the imported spreadsheet and updates the matching rows.  You won't need a where clause since the join will control which records are updated.

Don't forget to back up your quickbooks first because doing bulk updates is dangerous when you don't know exactly what you are doing.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:msibley
ID: 40522729
The invoice updates generally have different amounts, so I either need to loop through them as Scott proposes, or import/join the table as Pat suggests. Pat, I assume that what you mean by "import" is to import the spreadsheet into MS Access and then do a join with the table in Access that is linked with QB. Either way, I need some directions. It's been too long since I've done much with Access and I've not had much experience with VB macros. Please give me the actual steps to take.

Thanks,

Mark
0
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40522744
The GetExternal Data ribbon will walk you through importing the spreadsheet.  If you want to do it in VBA, create a form to use to manage the process and add a button to trigger it.  In the click event of the button, use the TransferSpreadsheet method.

DoCmd.TransferSpreadsheet ...........   Intellisense will help you flesh out the details.
DoCmd.OpenQuery .............. intellisense will help

To create the query, start with the QBE.  
. Add the table you want to update and the imported spreadsheet.  
. Draw a join line to connect the two.
. Change the query type to Update
. Select the column(s) you want to update.
. In the Update To cell of each field you want to update, add the name of the column from the spreadsheet.  Prefix it with the table name as in -- tblA.InvoiceLineRate

don't forget to back up QuickBooks first!!!!!
0
 

Author Comment

by:msibley
ID: 40523112
Pat,

With some experimentation, I believe I got it to work using your instructions. The query is running now and will probably take a while. I'll report back with the results.

Mark
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40523845
Great.  Let us know what happens.

In the future, you might want to test with only a few records so you don't have to wait hours to see what happened.
0
 

Author Closing Comment

by:msibley
ID: 40526818
Yes, it worked! Thanks.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40527234
That makes it a happy new year:)
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question