Mike_In_Ga
Registered User.
- Local time
- Today, 02:09
- Joined
- May 27, 2008
- Messages
- 32
A client has decided to retroactively change the unit of measure field in his ERP database.
I need to do a mass update to the entire database to change the old unit of measure to the new one. The problem is that the field "UoM" is actually stored in a multitude of ways. In different tables the actual fieldname could be "UoM", "PriceUoM", "AltUoM" and so on.
I found all the places in the ERP where a unit of measure is stored and put that data into a table in Access I called "UoMChanges". The "UoMChanges" table resides in the same Db where I have pulled in all the ERP tables via ODBC.
The "UoMChanges" table looks like this:
ERPTable.......................ERPField........... ...... OldUoM............. NewUoM
SalesOrders.................. PriceUoM................ EE.................... EA
PurchaseOrders............. PriceUoM................ EE.................... EA
InventoryMaster............ StockUoM............... EE................... EA
InventoryReceipts.......... StockUoM............... EE................... EA
InventoryStocktake........ UoM...................... EE.................... EA
Etc...............................Etc............. ...........Etc....................Etc
What I would like to do is run one Update Query via VB that will change the old UoM to the new one by referencing the "UoMChanges" table I created. Instead of writing a seperate Update query for each table and running them 1 by 1 (there are over 100 tables where these fields reside), I would like the Vb to loop through the ERPTable and ERPField values I have stored in the "UoMChanges table" to determine what to update, use the value in the "OldUoM" as my criteria and use the "NewUoM" field as my update value.
Is this possible?
Thanks so much.

I found all the places in the ERP where a unit of measure is stored and put that data into a table in Access I called "UoMChanges". The "UoMChanges" table resides in the same Db where I have pulled in all the ERP tables via ODBC.
The "UoMChanges" table looks like this:
ERPTable.......................ERPField........... ...... OldUoM............. NewUoM
SalesOrders.................. PriceUoM................ EE.................... EA
PurchaseOrders............. PriceUoM................ EE.................... EA
InventoryMaster............ StockUoM............... EE................... EA
InventoryReceipts.......... StockUoM............... EE................... EA
InventoryStocktake........ UoM...................... EE.................... EA
Etc...............................Etc............. ...........Etc....................Etc
What I would like to do is run one Update Query via VB that will change the old UoM to the new one by referencing the "UoMChanges" table I created. Instead of writing a seperate Update query for each table and running them 1 by 1 (there are over 100 tables where these fields reside), I would like the Vb to loop through the ERPTable and ERPField values I have stored in the "UoMChanges table" to determine what to update, use the value in the "OldUoM" as my criteria and use the "NewUoM" field as my update value.
Is this possible?
Thanks so much.