Updating invoices in Quickbooks using QODBC

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

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.

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.

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.

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

Featured Post

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

I see at least one EE question a week that pertains to using temporary tables in MS Access. But surprisingly, I was unable to find a single article devoted solely to this topic.
I don’t intend to describe all of the uses of temporary tables in t…

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…

Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data.
Rather than update each graph to point to a different set within a static set of data, t…

Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…