Mass Update Query, Need Vb Help

Mike_In_Ga

Registered User.
Local time
Today, 05:33
Joined
May 27, 2008
Messages
32
A client has decided to retroactively change the unit of measure field in his ERP database. :eek: 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.
 
Try this:

Code:
Dim db As Database
Dim td As TableDef
Dim fld As Field
Dim strSQL As String
Dim strOld As String
Dim strNew As String
Dim strFldKey As String

Set db = CurrentDb

strOld = "ee"
strNew = "ea"
strFldKey = "uom"

For Each td In db.TableDefs
    If Left(td.Name, 4) <> "msys" Then
        For Each fld In db.TableDefs(td.Name).Fields
            If InStr(fld.Name, strFldKey) <> 0 Then
                '--------
                strSQL = "UPDATE " & td.Name & " SET " & td.Name & "." & fld.Name & " = '" & strNew & "' WHERE (((" & td.Name & "." & fld.Name & ")='" & strOld & "'));"
                DoCmd.SetWarnings False
                DoCmd.RunSQL strSQL
                DoCmd.SetWarnings True
                '--------
            End If
        Next fld
    End If
Next td

msgbox "Done!"
 
Ken,
I tried your code and got an error. By the way, in my sample I table Ihad the old and new values flipped so I changed that in your code. The error mesage I am getting is: Datatype mismatch in criteria expression. The debugger highlighted the line below (see RED line).

Option Compare Database
Private Sub Change_Click()
Dim db As Database
Dim td As TableDef
Dim fld As Field
Dim strSQL As String
Dim strOld As String
Dim strNew As String
Dim strFldKey As String
Set db = CurrentDb
strOld = "EA"
strNew = "EE"
strFldKey = "uom"
For Each td In db.TableDefs
If Left(td.Name, 4) <> "msys" Then
For Each fld In db.TableDefs(td.Name).Fields
If InStr(fld.Name, strFldKey) <> 0 Then
'--------
strSQL = "UPDATE " & td.Name & " SET " & td.Name & "." & fld.Name & " = '" & strNew & "' WHERE (((" & td.Name & "." & fld.Name & ")='" & strOld & "'));"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
'--------
End If
Next fld
End If
Next td
MsgBox "Done!"
End Sub
 
Put a break point in the code somewhere at the top and step through the code line by line and when you get to the first setwarnings put the following code in the immediate window and hit enter:

?strSQL

This should display the sql string as built. Copy and paste it to the forum so I can see what you're getting.
 
Put a break point in the code somewhere at the top and step through the code line by line and when you get to the first setwarnings put the following code in the immediate window and hit enter:

?strSQL

This should display the sql string as built. Copy and paste it to the forum so I can see what you're getting.
I understand what you are asking me to do, but how exactly do I do that? Sorry for the newbie hand holding and I appreciate the help.
 
Not sure how to put in breaks.....I put in the STOP command just after the 'DoCmd.SetWarnings True' line. In the locals window I saw for expression 'fld'

: strSQL : "UPDATE dbo_ArCstStkPrc SET dbo_ArCstStkPrc.PriceUom1 = 'EE' WHERE (((dbo_ArCstStkPrc.PriceUom1)='EA'));" : String

That looks right. It is picking up the first entry in the ChangeUoM table
 
Open a new query in design view and go into sql view and paste in the sql statement and then try to run it and see if it executes. Here's the sql statement:

UPDATE dbo_ArCstStkPrc SET dbo_ArCstStkPrc.PriceUom1 = 'EE' WHERE (((dbo_ArCstStkPrc.PriceUom1)='EA'));

Looks like you're hitting sql server and it may not like the '=' part and may prefer 'like' or something like that. I'm not very good with SQL so maybe somebody else can jump in...
 
And you may need a couple spaces toward the end:

strSQL = "UPDATE " & td.Name & " SET " & td.Name & "." & fld.Name & " = '" & strNew & "' WHERE (((" & td.Name & "." & fld.Name & ") = '" & strOld & "'));"

Try putting that line in and see if it works...
 
Open a new query in design view and go into sql view and paste in the sql statement and then try to run it and see if it executes. Here's the sql statement:

UPDATE dbo_ArCstStkPrc SET dbo_ArCstStkPrc.PriceUom1 = 'EE' WHERE (((dbo_ArCstStkPrc.PriceUom1)='EA'));

Looks like you're hitting sql server and it may not like the '=' part and may prefer 'like' or something like that. I'm not very good with SQL so maybe somebody else can jump in...
The update works SQL statement works. A couple of things:

1 - I went back to the ChangeUoM table and took out all records where table name <> dbo_ArCstStkPrc and reran your code. I still got the same error message but the records updated.

2 - I added all the records back into the ChangeUoM table, ran your code, got the error message, and some tables updated and others did not

3 - Right now I am testing on local tables in Access. Once the code works I will repull linked tables in the Db and then run the code to hit the SQL tables

4 - Looking at your code, and perhaps Im blinded by the Vb :D, I dont see where your code is looking at the ChangeUoM table to get the table and fields names in order to build the SQL Update statement
 
Sorry, it's not. It's a stand alone. You'd need to re-run it for ea change you need to make. One for EA to EE, etc.
 
Here is a different approach with a different problem. The code works but when run it asks over and over again for the old and new UoM values. These two message boxes pop up over and over:

error.jpg


Private Sub ChangeUoM_Click()
Dim db As Database
Dim rs As Recordset
Dim strTABLE As String
Dim strFIELD As String
Dim strOLD As String
Dim strNEW As String
Set db = CurrentDb
Set rs = db.OpenRecordset("UoMChanges", dbOpenDynaset)
DoCmd.SetWarnings False

With rs
.MoveFirst
While Not .EOF
strTABLE = !ERPTable
strFIELD = !ERPField
strOLD = !OldUoM
strNEW = !NewUoM
DoCmd.RunSQL "UPDATE " & strTABLE & " SET " & strTABLE & "." & _
strFIELD & " = " & strNEW & " WHERE " & strTABLE & "." & _
strFIELD & " = " & strOLD


.MoveNext
Wend
End With
rs.Close
DoCmd.SetWarnings True
Set rs = Nothing
Set db = Nothing
End Sub
 
Ken,
Going line by line, the code is hanging up on the line
'Next td'

I think I see what is going on. Instead of getting the table names and field names from the "ChangeUoM" table, you are going throught the entire Db looking for fields that contain "UoM". That wont work because there are a bunch of fields in the Db that contain the string "UoM" that are not text fields. For instance, I have conversion rates all over the system that convert one unit of measure to another and those fields are named "UoMConv". There are a lot of other fields with "UoM" that type binary, decimal, etc. This is why I put only the "UoM" fields I want to be updated in the "ChangeUoM" table.

I think the code just above is more in line with what I need but if possible Id like to get rid of the prompts that appear hundreds of times. Thanks again
 
Last edited:
Sorry - I went to lunch. You still tinkering with this?
 
Sorry - I went to lunch. You still tinkering with this?
Unfortunately yeah. Im just a poor accountant trying to learn greek in a day. I posted up above that your approach won't work because I have non text fields with "UoM" in the fieldname. I have to pull from the table I created to get the specific field and table names. I also posted different code in the thread that takes this approach and it works, but I keep getting prompted for parameters. If I can get rid of the prompts this code will work nicely
 
I think you simply need singles quotes in the sql. Give me a minute to work on it.
 
DoCmd.RunSQL "UPDATE " & strTABLE & " SET " & strTABLE & "." & _
strFIELD & " = '" & strNEW & "' WHERE " & strTABLE & "." & _
strFIELD & " = '" & strOLD & "'"

???
 
Here ya go. I took out a lot of the tables to lighten the load
 

Attachments

DoCmd.RunSQL "UPDATE " & strTABLE & " SET " & strTABLE & "." & _
strFIELD & " = '" & strNEW & "' WHERE " & strTABLE & "." & _
strFIELD & " = '" & strOLD & "'"

???
That did the trick! Thank you sir very much.
 

Users who are viewing this thread

Back
Top Bottom