I'm using QuickBooks Premier Edition 2007, multicurrency, Canada. With QODBC Desktop Read Write 2009, Ver 9.00.00.253.

When I issue the following from VBDEMO32.EXE, I get a message box with: "[QODBC] Not Supported"

UPDATE Item SET SalesAndPurchasePurchaseDesc='test' WHERE name='Skiing Lessons'

 What am I doing wrong, and more importantly, what documents should I be looking at that explain why I can't write this field (I can read it with no problem)?

Thanks for any reply,

-Reed Bement

The ITEM table is a combined (read only view) list of all Item tables in QuickBooks: ItemInventory, ItemNonInventory, ItemOtherCharge, ItemInventoryAssembly, ItemService, ItemFixedAsset, and ItemGroup; with minimum common fields.

Inserts and updates are actually done using the ItemInventory, ItemNonInventory, ItemOtherCharge, ItemInventoryAssembly, ItemService, ItemFixedAsset, or ItemGroup tables.

See: Where are the schema rules for using QODBC? How do I use sp_columns? 

Thanks, that answers my question completely. Unfortunately it also means I won't be able to use the SalesAndPurchasePurchaseDesc field for private data storage as I had planned for ItemNonInventory items.

It seems that the custom fields are too small for my use, I need 4096 chars. I'm beginning to think my approach is all wrong. What is best practice when you want to store a large amount of private data (data that QuickBooks won't use) with each item?

Thanks again,

-Reed Bement

I'm not to sure why the 4095 characters in either SalesOrPurchaseDesc or (SalesAndPurchaseSalesDesc and SalesAndPurchasePurchaseDesc) isn't enough here? My experience is that private data is more likely to be applied to the transactions themselves in the Memo fields as the data can be unique to the transaction, not just the item.

4095 characters would be fine, but we already use these fields and I would like not to mix the data in the QB UI. The data won't mean anything to the QB users  (it's file paths to documents on a server). Can the size and type of custom fields be modified? Could I add my own private column to the  ItemNonInventory items?

To summarize: I'm looking for the cleanest/safest way to add private read/write data (won't interact with quick books UI) to theItemNonInventory table.

Thanks,
-Reed Bement

I don't seem to be able to create a table in QuickBooks, so I assume you mean that I must have an external database. I was really hoping to have this data all in one place (a single database), but if I'm understanding you correctly, that is not going to be possible with QuickBooks. Correct? Also do you know what database engine is used by QuickBooks?

Thanks,

-Reed Bement