I am getting and error in MSAccess ODBC--update on linked table Item failed [QODBC] Not supported (#10003)

QB enterprise 2007 with QODBC Server Edition.

I am trying to automate a pricing update from another table.

SQL:

UPDATE Item INNER JOIN [2007 Partner Price Update] ON Item.Name = [2007 Partner Price Update].[Part Number] SET Item.SalesPrice = [2007 Partner Price Update]![List Price], Item.PurchaseCost = [2007 Partner Price Update]!Cost
WHERE (((Item.Name)="506028401"));

Is this not supported? Is there another way to do it?

The Item table is a read only table view of the ItemInventory, ItemNonInventory, ItemOtherCharge, ItemInventoryAssembly, ItemService, ItemFixedAsset, and ItemGroup tables. Your update statement needs to run against the actual table with the item in it.

If these are stock parts, see: How do I do Inventory Adjustments? for COGS adjustments. As far as the sales price and purchase price are concerned, the update statement is:

UPDATE ItemInventory set SalesPrice = 1200.00, PurchaseCost = 700  where Name = 'Acer Notebook'

Thanks that works fine in the VB Demo 32 but I have run into a snag with msaccess. When I try and do a bulk update I find that msaccess only has read only access to QB. I tried to change it in QB but I don't see an option to do that in the preferences.

What am I missing?

The point is not the query, I used the example query you provided. It works fine in VB Demo but not in access, not due to the query but due to the fact that the Integrated App setup for msaccess sets up as read only so I cannot do an update query.

VB Demo access QB with read/write rights but msaccess access with read only rights. How do I change that? I am running the QB Enterprise 2007 with the lates v7 QODBC Server driver.

The problem is the integration rights. I see no way to change it in QB.

If you linked the QODBC tables in MS Access using the bundled QODBC Enterprise Edition that comes with QuickBooks Enterprise 2007 in USA, then the connection string will be hard coded as read only even though you are now running the QODBC Server Edition.

Try unlinking and relinking the tables or start another database in MS Access.

I just purchased the write verson of the odbc driver for quickbooks.  We are using qbe 7.0.  I have cost in a text file and need to update all of the item cost in our quickbooks file?  I need a steer in the right direction??

Thanks chuck

Ok great!  However I was told prior to purchase that what I was wanting to do was very common and there is a libray of scripts and processess to do the job.  I explained I was very capable using excel and foxpro and can muddle through some VB.

Thanks Chuck

I looked at all of the vbs programs.  First of all I do not see any documentation ????  Can you steer me to a link that will explain the code.  Furthermore, I do not see any examples of using excel to update the item file, namely the cost and or price fields???  I have used "update on ...." type of sql statemnts before???  How do i open the quickbooks item file, match on the text file item name field and update all of the records?  To suggest I need to write a program seems a little counter productive?  Do I need to pay you guys to construct this update statement - or can i supply some sample data?  But really, I described what I want to do at lenght before i bought the program and was assurred it would be easy and would have acces to SEVERAL examples to update the item file.

Chuck

Again, from above......the Item table is a read only table view of the ItemInventory, ItemNonInventory, ItemOtherCharge, ItemInventoryAssembly, ItemService, ItemFixedAsset, and ItemGroup tables. Your update statement needs to run against the actual table with the item in it.

Please contact whoever made the representations to you in regards to using Microsoft Excel. This is a QODBC support forum only.